Thursday, November 29, 2012

Fact Table Range Groups Created Easily in SSAS Tabular

This is one very cool feature that I wanted to share in my SQL Saturday #168 session on SSAS Tabular but ran out of time. Often in a data warehouse project you will receive a request to group certain values in a fact table into range groups.

For example: Sales Amounts into categories of "Small", "Medium", or "Large" and the corresponding range values for price will be 0-$50, $51-100, $101-$1,000,000.

If anyone has ever built a fact table in a data warehouse from the ground up and those are the concrete ranges, it is business as usual. The ETL can account for a dimension called Sales_Category and a foreign key to the dimension in the fact table is created etc.

With Analysis Services Tabular you can import a range table right into the SSAS model and create a DAX formula to accomplish this simply. I know you are probably asking yourself well I still need a table created in the data warehouse with the range values to import so it is still going to take some time to get accomplished right?  The answer is no you don't need to (see Disclaimer below).  You can create a range table in Excel or a flat file and import it right into the Analysis Services Tabular model.  Alberto Ferrari's post on banding with PowerPivot helped me do this and it works elegantly.  If you don't read Alberto's blog posts regularly, you should ;-)

Disclaimer: I am not saying this is a best practice or not; but can be accomplished quickly to accommodate a need and works elegantly. Personally I would do this to accommodate a quick need then incorporate the range table into the data warehouse for consistency. In my finest Forrest Gump voice "that is all I have to say bout that" and you can decide what is best for your situation.

Here is how you can do it...

Step 1: Click on Model>Import From Data Source>Excel

Step 2: Choose the Excel file you created for Sales Categories and enter credentials when prompted. Enter a name for the "Friendly Name" of your table.  I chose RangeSalesCategory. Click Finish.

Step 3: Finish the import into the model.

Step 4: Click on the Fact Table you would like to have a range incorporated on and create a new calculated column with a simple but very powerful DAX command called CALCULATE:

= CALCULATE(VALUES (RangeSalesCategory[Sales_Category]),
    FILTER (RangeSalesCategory,
                  FactInternetSales[SalesAmount] >= RangeSalesCategory[Min_Sales_Amount]
                  && FactInternetSales[SalesAmount] < RangeSalesCategory[Max_Sales_Amount]

As you can see the Sales Category range is populated with a very minimal effort.  All that is left to do is deploy and process the cube. Make sure you leave this column unhidden along with the Primary Key. Doing this will create a degenerate dimension you can slice your measures by without a week or so effort!

Wednesday, November 14, 2012

Speaking This Saturday in Tampa at SQL Saturday 168 BI Edition!

Just a quick blog post to let everyone know that I am speaking this saturday November 17, 2012 at SQL Saturday - Tampa BI Edition 2012.  You can find out more info on times of sessions and location of the event here.

I will be doing a session on SSAS Tabular and Self Service BI. I will walk everyone through how to create an SSAS Tabular model from scratch, get into some hierarchies, KPIs, then show the performance of the xVelocity engine on a large data set with calculations.

If you are in the Tampa / St. Pete area, you should take advantage of this free training event with great sessions from national and local experts to learn the latest with the Microsoft Business Intelligence Stack. Hope to see you all there!

Monday, November 5, 2012

SQL Saturday 174 - Follow Up Oracle Transactional Replication Answers

Thank you to everyone that attended my session on Conquering Reporting by Scaling Out SQL Server. It was a packed room and thank you especially for the high marks on the evaluations. It was my first time presenting in the first session slot and was at maximum caffeine slope on the curve during the session ;-)

There were two questions related to Oracle and Transactional Replication.


1. Are the tables locked in Oracle for Publishing as they are in SQL Server when using an Oracle Publisher?

2. Can you have an Oracle database as the subscriber?

Great questions. I tried to setup an Oracle publisher once many years ago and the Oracle DBA team did not want to do it because it required a bunch of extra objects and instead wanted to use a third party tool called Data Mirror. That doesn't answer the questions though so here is what I found out.

It is important to note that Oracle Publishers are being deprecated from SQL Server in a future release. You can create an Oracle Publisher from SQL 2005 to SQL 2012 using Oracle 9i and above. SQL Server wants folks to use SQL Server Integration Services (SSIS) and Change Data Capture (CDC) as the methods going forward from what I've read.


1. No they are not locked.  DDL changes can be done to Oracle Publisher tables at will, but they will not show up at the subscriber unless the publication is reinitialized. Further, Oracle Publishers are setup different with triggers created on Oracle tables then other tables that capture the changes that log the changes and get pushed to the distributor.  

References:  (Info on Oracle & Transaction Replication)

2. Yes you can have an Oracle database as a subscriber. You cannot have pull subscriptions you can only have push subscriptions.


Great questions, and sorry for the delay in posting the answers.