5/08/2014

#SSAS is a Great Programming Tool #MSBI

Microsoft Analysis Services is pretty cool.  The thing is, you can never really get over confident using it.  You must give it the proper respect.  Because it's a bit fragile.  And it's got many many features.

So you have Dimension tables.  And Fact tables.  Which you typically bring into the project using Views.  You can either let the foreign key relationships join the tables naturally and manually add the relationships.

Then you add your Dimensions to the project from the Data Source View.  Next you begin adding your Measures.  For example, you may have Revenue, Weight and Height.  You can perform aggregations on them by using Sum, Min, Max, Avg, etc.  Those get lumped into Measure Groups.

Then you add additional relationships and verify they got applied correctly in the Dimensional Usage tab.  From there you can add custom Measures in the Calculations tab.  For instance, if you wanted to Sum Revenue by Month you could apply and MDX formula, so that it dynamically calculates the Monthly Revenue based on the level in the Dimension.

The thing I've learned from using SSAS is "Naming Conventions".  Using incorrect names can get you into trouble really fast.  Another thing is the order in which the Calculations appear, they are in order and you must pay particular attention when moving up or down, as you may be calling that Calc from another Calc and if you place it after, it will generate an error.

Hierarchies are nice, for example most Cubes have a time dimension and sometimes its useful for Users to drill down from Fiscal Data to Fiscal Quarter to Fiscal Month to Day.  So that works great for Hierarchies.

Everything is customizable, the output format, visibility, just about anything, so it's indeed intricate and temperamental in the fact that everything is nested and there's no easy way to view everything from a global view.  It's a bit unforgiving, if for some reason you remove some MDX and want to get it back, good luck.

Overall, the end product is quite nice as you create Models for end user consumption in reports like Excel Pivot Tables or SQL Server Analysis Services.

I think it's a great product, just keep in mind, pay your respect to the product because sooner or later, one casual mistake and the project breaks and the error messages are not descriptive in the fact the actual errors are nested a few layers deep so it's not helpful in diagnosing the actual issue.

Happy coding~!

No comments:

Post a Comment

Babalon