DAT225x: Developing a SQL Server Analysis Services Tabular Data Model

SSAS Tabular gives at last the semantic layer that was missing to the Microsoft BI landscape. Users of IBM Cognos and Business Object have been enjoying that business abstraction layer for many years. As far as I can remember, it has always been part of the Cognos architecture, in the form of catalogues in Cognos 7.0 and before, and frameworks in Cognos 8.0 and after.

"Enjoying" may not be the appropriate word, since the use of catalogues and frameworks was nearly compulsory when creating reports.

May I remind here that the role of the semantic layer is to give a business-friendly meaning to the objects in the datawarehouse, and to ensure their consistency by embedding the joining strategy.

Two things that SSAS does happily, by the way.

People migrating from Cognos will miss the virtual cubes I think, also known as "Dimensionnally Modelled Relational" or DMR, because there is no such thing in SSAS Tabular. DMR enabled data queried directly from the database to be presented dimensionally and drilled-up and down along hierarchies in a pure dimensional manner. In SSAS, there is no notion of virtual cube. It's either an in-memory cube, ok it's fast but it's still a cube that needs to be refreshed and recalculated, or if you want a real-time access to the datawarehouse, then there is the Direct Access Mode, but then you lose the ability to navigate your data dimensionnally. Too bad.

I am not sure what Microsoft's roadmap is, maybe enabling drill-up down in the Direct Query Mode in a future version, but in terms of functionnality, there is currently no equivalent of Cognos' DMR in the SSAS offering. Is it because Microsoft assume that the underlying "stitched query" that Cognos are so proud of, doesn't scale and perform?

That being said, it was time for me to add textbook to practice, and to pass this SSAS Tabular certification :-)

Subjects covered:

• Importing data, creating relationships, creating hiarachies, defining a date table, creating perspectives, creating translations, DAX, calculated columns, calculated tables, measures, KPIs, configuring partitions, configuring permissions, deploying tabular databases,...
• The disappointing bit: designing for Direct Query Mode (because it's a semantic layer in its simplest form - not offering much more than friendly business names and joins)
• The tricky bit: architecture choices between SSAS Tabular and SSAS Dimensional... Tricky because the choices are not always obvious, as the products are so different.