SQL Server DENALI Short BI Guide
Posted by thomasivarssonmalmo on September 3, 2011
A lot on my plate for the moment so I had limited time to blog but since I am back in consulting expect to see more posts here after the end of September.
SQL Server Denali is a big release from the BI perspective and I will add my own reflections here but Denali has been commented by al lot of good bI thinkers that I have links to ón my site.
First I think you will here the abbreviation BISM(Business Intelligence Semantic Model) as many times as you have heard about UDM(Unified Dimensional Model) in the past. Our old multidimensional friend Analysis Services, alias UDM, will be called BISM Multidimensional when Denali ships. If you read the product team blog it will still be called Analysis Services and PowerPivot Team blog.
What about the news for BISM Multidimensional in Denali? Not much regarding new features except for that you can select to bypass the 4GB string store limit for a cube. This probably comes with a price as usual in the trade off between performance and features as usual in this business. There is no product that comes with good performance and a lot of features even if sales people claim that. The big news can be that this is still the best tool for building really large cubes on top of big data mart. It is not dead and you cannot exchange it for other MS BI servers when you need to construct complex business logic or need to browse large cubes.
BISM Tabular is the Vertipaq engine or column store mode for Analysis Services. When you install Analysis Services in Denali you will have to choose either modes, tabular or multidimensional, in the first installation. To add the second mode on the same server you will have to run the installation a second time. BISM tabular is really a server version of PowerPivot for SharePoint outside of SharePoint. You do not need SharePoint to install BISM Tabular or Analysis Services in tabular mode.
Some questions arise if you try to install BISM tabular and BISM multidimensional on the same box. BISM tabular is totally memory based so if you need both I would not run that on the same box as BISM Multidimensional or the SQL Server relational engine(RDBMS). In fact I think that you need several separate servers to support SQL Server RDBMS, BISM multidimensional, BISM tabular and SharePoint 2010(with Reporting Services). This will mean some additional decisions regarding in license cost and hardware budgets. It will also add additional complexities regarding infrastructure setups with Kerberos knowledge being mandatory.
Why would you need BISM tabular? First it is more flexible than BISM multidimensional. You can deploy Excel 2010 PowerPivot models to BISM tabular so users(or non consultants) with a good knowledge of DAX can build their models in Excel and send that to a an IT departement for further work or deployment. I must admit that it is far quicker for me, as a professional, to build models in Excel 2010 PowerPivot version 2 than doing the same in in BISM multidimensional. In the BIDS for tabular projects the UI experience is very near to building models directly in Excel 2010. I would recommend you to use BISM tabular for dimensions models that requires more changes within shorter time frames than what BISM Multidimensional can offer.
BISM tabular models can be queried in Reporting Services with MDX. That can be a way to bypass the SharePoint 2010 requirement and integrate Reporting Services in native mode to other portals. Excel 2010 is also supported as a client and Excel also use MDX to query BISM tabular models.
The second pro of using BISM tabular is that it is the only supported data source for Crescent, the new reporting tool in Denali, focused on non IT professionals to build quite complex reports. Crescent use DAX, the query and expression laguage of BISM tabular. This is a killer application for non IT professionals and the first serious attempt from Microsoft to buld a user friendly reporting tool. The backside is the SharePoint 2010 Server requirement for Crescent.
DAX is now a limited query language and not only an expression language but without the capacities of MDX. It is like a midget MDX that use functions for the query part.
Reporting Servives in Denali and only in SharePoint 2010 integrated mode also have a new event subscription model for KPI changes that the users, themselves, can configure.
In TSQL and the relational engine new Windows functions are added for building cumulative values and having the notion of previous record like we had in MDX since the start. Reporting in TSQL on the relational source will have more capable tools in the Denali release.
PowerPivot in Excel comes with some great features that is also part of BISM tabular. You can build natural hierarchies in the dimensions tables. There is an ER diagram view where you can relate tables by a drag and drop columns UI, build hierarchies and see DAX calculations. DAX measures can be formated. Another important addition is that some DAX functions have been simplified. As an example is the native DistinctCount() function. No more CountRows().
The relational engine in Denali also supports the Vertipaq column store model with special indexes. Vertipaq or the column store is managed by the relational engine query optimizer so adding these indexes to a relational data mart can boost performance of TSQL based reports. I guess that this will make the ROLAP mode of BISM multidimensional even more interesting in the near future.
SSIS will be a huge release in Denali but I will have to refer to other good blogs for comments about the improvement in this product. I use SSIS not only for ETL but also for building recordsets for import into Excel when you do not have the time to wait 30 minutes for SSRS to build a report with 50 thousand records.