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!

No comments:

Post a Comment