9/30/2015

Microsoft OLAP Cubes 101

OLAP Cubes are still around.  I work with them every day.  For Microsoft, there are two flavors.  There's Multi-Dimensional Cubes and Tabular Model cubes.

Tabular is the newer flavor, it has some advantages.  Like easier to get a cube up and running in shorter time, it uses a new language called DAX, you can import models from Excel Power Pivot and I imagine the newer Power BI.  You can assign permissions to specific users and groups contained in Active Directory.  Basically you create Roles with specific permissions, then assign users to the Roles.  Create partitions.  And schedule refreshes from SQL Server SQL Agent to process the cube, for example, if you receive new data throughout the day, the cube will only display new data when refreshed.


Multi Dimension Cubes have been around before Tabular.  They are a bit complex, if you're coming from the traditional T-SQL world.  MDX is a beast of a language, I've worked with it for a few years and every time is a challenge.  Although some apps like SSRS have Wizards to help out.
 

 Once you have an OLAP Cube, you can build reports off it.

What type of reports can you build based off an OLAP cube?  Microsoft offers Power BI, Excel, SQL Server Reporting Services (SSRS), Performance Point and Power View.


The OLAP Cubes run in a different database than OLTP, called Analysis Services.  It contains the Cube, Dimension tables and Roles.  The IDE used to build cubes is typically the SQL Server Data Tools, also known as BIDS depending on the version, and you can use Visual Studio 2013 provide you install the Data Tools add-in.

Many times, you build a Cube based off a Data Warehouse.  A Data Warehouse has Dimension tables and Fact tables.  The dimension are typically things like, Person, Region, Warehouse, Date, (things) to slice the data with.  The Fact tables contain measures consisting of Sums, Average, Mins, Max, Avergages, they are almost always numbers.  And you can build hierarchies within the Dim tables, for example:


Country --> State --> County --> City
 

 That's how OLAP Cubes work.  Abbreviated edition.

No comments:

Post a Comment

Bloom Consulting Since Year 2000