Analysis Services cubed in SQL Server Denali ctp3

  • Article

I have now managed to install analysis services (SSAS) in SQL Server Denali ctp3 three times to reflect the three installation options that now exist ..

ssas in denali ctp3

  • The top instance is the table based installation that’s completely new for SQL Server Denali
  • Then I have olap based SSAS instance that I have deliberately called OLAP.  This is essentially the same as was introduced in SQL Server 2005 and performance enhanced in SQL Server 2008. It has had some minor improvements in SQL Server Denali, specifically scalable string storage to allow more than 4G for storing strings in dimension tables, but will support existing tools such as the data mining add-ins for Excel (Note: you’ll need the SQL Server 2008 add-in and this must be installed to 32-bit excel).
  • Finally I have a PowerPivot for SharePoint integration instance (the instance name is hard coded as PowerPivot during the installation), and as you can see I have one PowerPivot in SharePoint

Anyway back to table based or VertiPaq analysis services.  Essentially this is server side PowerPivot:

  • Is uses the same query language, DAX
  • It uses the same VertiPaq column based engine to cache data, and this can be likened to MOLAP storage in SSAS.
  • you can import a PowerPivot into BI Development studio (BIDS) and then deploy an instance of tables based SSAS.
  • You’ll see that the design experience in BIDS is very similar to what there is in PowerPivot for Excel today with SQL Server 2008 R2. 

However like PowerPivot itself in SQL Server Denali there are also improvements in BIDS such as a visual view of the relationship between the tables ..

table based SSAS model

 

What you are actually looking at here is the new BI Semantic Model (BISM).  This is replacing the report model in previous versions of SQL Server and will also replace the unified dimension model (UDM) that underpins analysis services, so that there is a simple three layer structure to do BI:

  • Data sources, be that structured data that exists in house or Odata feeds from the cloud like the Azure data market
  • BISM to add business logic, and store how the data sets are joined and where they came from.
  • Analysis & Reporting tools both from Microsoft and from third parties to suite different users and differing needs.  For example I can point straight Excel (with no add-ins)  directly at Tabular based SSAS model like this or the Dashboard Designer in SharePoint and both will treat this as a conventional olap cube. 

At this point I probably need to do a series of posts on why BISM is important as well as contrast and comparison posts on Vertipaq v OLAP analysis services, so I’ll stop here. In the meantime if you want to try any of this, then SQL Server Denali ctp 3 is available for download here and there’s matching sample databases, reports and tutorials for it on codeplex.