Tuesday, April 14, 2015

Tabular SSAS - Adding a "Rolling Twelve Month" Filter

It's common for report consumers to want to view all kinds of data in a rolling 12 month window.  However, it's cumbersome to have to drill down in a date/time dimension filter each time a report is run to check and uncheck values in order to display only the previous 12 months.

Do you think your end users would like to just be able to toggle a True/False option to view the previous year's worth of sales information?  Huh?  Do Ya?  You bet they would!!!

Here's how:

  • Open your tabular SSAS project
  • Add a new calculated column to your Calendar dimension and name it Rolling12Months
  • Add the following (or similiar depending on your column names) DAX calculation to the column

                                 ((NOW() - DimCalendarPeriod[FullDateAlternateKey]) * 1) < 365,

  • Make sure you make the new column visible through your perspectives or default view, then deploy and process your project.

Now when your end users view a pivot table report they'll have the following option available:

Analysis Services - Ordering Months in a Pivot Table

Say you've created an analysis services BISM, either tabular or multidimensional, and your end users connect and attempt to view data by month.  What they probably don't want to see is in their pivot tables is:

The order is all wrong, and toggling the sort options in Excel won't help.  The fix?  Concatenating the month number to the name.  You could either do this in your Calendar dimension when loading data, as such:

Or probably a better method is to do the concatenation in your cube.  With a tabular project, open your calendar dimension and add a new calculated column named OrderedMonth.  The DAX calculation for the column would be similar to:

     =DimCalendarPeriod[MonthNumberOfYear] & " - " & DimCalendarPeriod[EnglishMonthName]

Make sure the new column is visible through your perspectives or default view and then deploy and process your changes.  When your users pull this new column into a pivot table they will now see:

 Much better, don't you think?

Monday, April 13, 2015

Passing a Parameter to a View

"How do I pass a parameter to that view you created for me?" you asked.  Common question, but can it be done?  Technically no, you cannot pass a parameter to a standard SQL view, just as you can't pass a parameter to a table.

And now for everybody's favorite pastime - workarounds!!! Here are a few:

  • Of course you can add a where clause to your view select statement, just like with a table:
                    SELECT * FROM SomeView WHERE AwesomeStuff = 1

  • You may also consider creating a table valued function instead of a view:
                    CREATE FUNCTION dbo.SomeFunction (@AwesomeStuff bit)
                    RETURNS TABLE
                         RETURN (SELECT Col1, Col2, Col3 FROM SomeTable
                                           WHERE AwesomeStuff = @AwesomeStuff)

                    SELECT * FROM dbo.SomeFunction(1)

  • You could create a stored procedure to return a stand alone record set.  Less useful and I'm sure you know how to do that already.
Any other ideas from anyone??  Feel free to post below...

Thursday, April 17, 2014

Directly Updating Master Data Services Tables

Although Microsoft provides two mechanisms for modifying data withing your 2012 Master Data Services implementation - the web based MDS Manager and the Excel MDS plugin - I've found there are times when you may want to update data though general SQL commands:

  • You may want to update a large subset of data in batch mode
  • You may want to implement business rules, through a stored proc for instance, that runs during an ETL process
  • You may just like to do things your own way

Have you ever looked at the non-system related tables in the MasterDataServices database?  It's hard to tell if your data is in mdm.tbl_3_9_EN or maybe mdm.tbl_1_28_MS.  Or who knows where.  An how about the columns within those table?  Are my ProductNames in uda_10_349 or uda_11_8784?

Here's what you do:

  • Browse to the web based Master Data Services manager and click Integration Management
  • On the Integration tab, click Create Views
  • Create a View
  • Open the database in SQL Server Management Console, drill down to the view you created, and view it's DDL.

Viola!  You you can see where MDS is holding (or hiding) your data.  I've found its perfectly safe to update these MDS tables directly.  You'll loose the built in MDS change management and tracking functionality, and probably violate some basic MDM rules updating fields this way but sometimes you have to spill milk to make a frappe (or something like that).