A little while ago I blogged about how to created discretization buckets in a multi-dimensional cube. The nice thing about it is that it was just a few properties that you needed to modify, and was pretty straightforward. To the flip side of that I see a lot of companies wanting to take advantage of the new tabular Analysis Services model and the performance you get from it. The good news is that you can do the same thing in tabular, but they’re not properties you set. To set it up, first you’ll need to identify what is going to create your buckets. Are you going to use a linked table? SQL view? Or maybe just a hard coded SQL Select statement. For this example, I’m going to assume that the business user wants the ability to change them easily, so I’m going to use a linked table. I’m also using the Adventure Works 2012 tabular demo project. One way to approach this dilemma is that you could just create an if statement in the table that groups your items for you, but what if a business user wants to change the values dynamically? Re-writing your calculation every time would be difficult, and frustrating to the end user. They want the ability to modify it as business needs change.
First, create your table in excel. When you’re done, save it and give a name. It should look something like:
You can see from the picture, nothing fancy. Just a quick table, took me longer to write this sentence than create it.
Then go into SSDT, and in your project select “Import from Data Source.” If you created a view in your source or are pulling your table from another source, this is where you would configure it. Assuming you’re using the Excel table, select Excel File from under the text files options:
Click Next, and navigate to the file path from your earlier. Make sure to give your imported table a friendly name, as it will just name “Excel” by default:
Click Next, and then click Finish. It should import your table in a few seconds. After you’ve imported it, and you view the model in diagram view, it should not be related to anything. This is OK, as there isn’t anything to join it to, as there are no columns in any of the tables that would join to it. The calculation we’re going to write here in a minute is going to give us what we’re looking for.
In order to solve this and give us our proper values, we’re going to take the values from the banding table and bring it directly into our Fact Internet Sale table. Going to the Internet Sales table, we’re going to create a calculated column (the filter context) with the following formula:
=CALCULATE(VALUES(‘Banding Matrix’[Band]),’Banding Matrix’[High Value] >= EARLIER(‘Internet Sales’[Unit Price]),’Banding Matrix’[Low Value] < EARLIER(‘Internet Sales’[Unit Price]))
What this calculation is doing is taking the value (the name) of the pricing band that we’ve specified where the value of the unit price column is between the low and the high amounts we’ve specified. But this formula has 1 problem. Since our key business requirement here was that users can input the band values themselves, what if one the values in our fact table is missed? What we really need to do is format this in a way where we can protect the formula against an error. Using the IFERROR() function can help us here. So a better formula is:
=CALCULATE(IFERROR(VALUES(‘Banding Matrix’[Band]), "No Band Found"),’Banding Matrix’[High Value] >= EARLIER(‘Internet Sales’[Unit Price]),’Banding Matrix’[Low Value] < EARLIER(‘Internet Sales’[Unit Price]))
Now we are well protected and business users are happy. Before you finish, click on the Banding configuration table tab and select “Hide from client tools”. Preview the model in Excel, pick any measure that you want out of the fact table, and select your new price banding matrix. The view below is showing me total sales amount by unit price group (band).
Now if a user wants to change the values for their groupings or just wants to know what they are, they have the control. There is no dependency on you or anyone else in IT to change it for them. Viva La Self Service BI Revolution!