Building a SQL 2012 Tabular DataCube

From http://msdn.microsoft.com/en-us/library/hh994774.aspx

  • Multidimensional modeling, introduced with SQL Server 7.0 OLAP Services and continuing through SQL Server 2012 Analysis Services, enables BI professionals to create sophisticated multidimensional cubes using traditional online analytical processing (OLAP).
  • Tabular modeling, introduced with PowerPivot for Microsoft Excel 2010, provides self-service data modeling capabilities to business and data analysts. The tabular modeling experience is more accessible to these users, many who have spent years working with data in desktop productivity tools like Excel and Microsoft Access. In SQL Server 2012, tabular modeling has been extended to enable BI professionals to create tabular models in Analysis Services or to import a tabular model from PowerPivot into Analysis Services. Note that a PowerPivot model cannot be imported into an Analysis Services multidimensional model.

What does this mean? First thing to understand is VertiPaq  similar to zip compression it takes data and organizes it same as an order by SQL statement. It then uses tradition compression to find similarity in data.

What I have found is this. SQL 2012 Tabular will do more work if you use it to join all data and order it. If you put the work on SQL Server, Oracle, Sybase.. You can put the work on your database and reduce processing in Tabular. There is a trade off depending on which server has more power. What surprises me is how well Tabular compresses the data and how fast it is when users go into the row level.

Tabular is a hybrid between OLAP Multidimensional and OLTP. It really is just Multidimensional but its in memory. In regards to your server you need 2 things Memory and Processor.

Your memory should be 3 times the size of your largest cube. The reason being is that SQL 2012 Tabular stores your current snapshot and processes it in a second instance. This uses double the memory at times plus what ever else is processing on your server.

Your processor is used for the calculation and compression phase of processing. When processing is finishing (Full or Default) you will see processor rise is the compression or calculation is done. If you undersized either one you will run out of memory or crash the processing. Both can leave your cube in a corrupted state.

See Processing SSAS Tabular with Powershell