SQL Server 2012 BI: The Good, The Bad, and The Ugly

Recently I implemented a BI solution for a client using SQL Server 2012 and wanted to share some thoughts on that.

Reporting Layer

The Microsoft BI presentation layer includes several reporting and analysis tools including: Reporting Services, Report Builder, Power View, Excel, PowerPivot (for Excel and SharePoint), Excel Services, PerformancePoint Services and Visio Services.

Many of these tools have overlapping capabilities that can easily confuse developers and designers at first glance. For instance for ad-hoc functionalities you can use Report Builder, Excel, PowerPivot or Power View.

Number of this tools are only available on SharePoint Enterprise Edition. So if you don’t already have it and don’t want to spend on this costly licence you will miss many of them such as: Power View, PowerPivot for SharePoint, Excel Services, PerformancePoint Services and Visio Services.

By introducing the Power View, it seems like Microsoft was trying to add more self-service functionality to the stack, but to me this good looking and flashy tool won’t be able to compete with the beloved Excel to get adopted by business users, and it’s certainly not envisioned to be used by IT people either. The other big limitation is that only Tabular databases can be used as the source for Power View reports.

BI Layer

With the new Business Intelligence Semantic Model (BISM), the objective was to have one model for all user experiences: reporting, analytics, and custom applications, in other words “BI for all” based on the same model. Though a big difference is that in SQL Server 2012, Analysis Services is available in two types: Tabular and Multidimensional.

The Tabular model provides the advantage of the table-based approach, DAX queries, and the VertiPaq (xVelocity) which is an in-memory engine. In other words, a server version of PowerPivot without the need of SharePoint or Excel.

The Multidimensional model (cubes, dimensions & hierarchies) is what we had before with UDM, storing aggregated data on disk and using MDX language to query OLAP storage. It doesn’t have major changes since SQL 2008 except for a few fixes and optimizations and improvements.

The problem with that is unlike what Microsoft claimed, there is no such a thing as a unified BI model: BISM models cannot live together on the same SSAS instance and they speak in 2 different languages! Tabular database can be queried with old MDX too, but the point is developers spent so much time to understand and learn MDX (with not much luck!) and now they have to learn a new language and still keep learning the old one too!

Also there are not many tools that can use DAX to query database, currently Reporting Services and Excel 2010 can query the BISM tabular using MDX and PowerPivot and Power View can use DAX, so there is inconsistency in terms of the way you can talk to BISM, however Microsoft claims that this limitation would soon be removed and the developers could use either of the query languages to query data from both multidimensional or tabular types.

As Marco Russo stated in his blog "...there are opportunities in Tabular thanks to the flexibility, but these are very early days and we lack of new client tools able to take advantage of the new model. Power View is just one, but there is space for more."

Do I recommend using Tabular mode? Well, as it’s always the case: it depends. If you are implementing an enterprise BI solution, given the limitations of the Tabular mode I don’t recommend it at all, but if you don’t have a huge data size and you want to build a flexible system fairly quickly, then Tabular is the answer, otherwise use the traditional Mmltidimensional mode.

ETL and data Layer

SSIS had some improvements. Not only it looks nicer and tidier than previous versions, but also there are number of new controls and features in SSIS, such as Change Data Capture (CDC) tasks, scripting improvements and new expressions like LEFT, TOKEN and REPLACENULL, easier troubleshooting & logging, data taps, PowerShell support, etc. I was also very impressed with the data quality components in SSIS and DQS in general. 

Conclusion:

So in overall there are many new BI features and improvements in SQL Server 2012, but what seems to be lacked is a clear and well-defined vision on the BI roadmap. As mentioned, many of their tools are based on Enterprise Edition of SharePoint which is not affordable for small-mid size companies, and some of their tools are also not designed to satisfy enterprise clients. So to me it’s not very clear on where Microsoft is heading to and what type of market they are targeting with these products.



Update: Luke mentioned that Power View is now included as part of Excel 2013, I haven’t got chance to check it yet but am happy that Microsoft is coming to realize that not every company is interested in using SharePoint 2010 Enterprise, so perhaps they need to consider Office clients as the main delivery tool for BI and enrich the functionality of the good old friend Excel in order to have a better market share in BI space.

Handling Ragged/Unbalanced hierarchies in SSAS

What is it?

Balanced hierarchy: In balanced (standard) hierarchies, branches of the hierarchy all have the same level (depth), with each member's parent being at the level immediately above the member.

Example: time dimension, where the depth of each level (year, quarter, month, etc.) is consistent.

Unbalanced hierarchy: the hierarchy branches can have inconsistent depths.

Example of an unbalanced hierarchy is an organization chart. The levels within the organizational structure are unbalanced, with some branches in the hierarchy having more levels than others.

The following charts illustrate the unbalanced hierarchies:

How to handle it on SSAS?

There are 2 methods to handle ragged hierarchies on SSAS:

Using Parent/child dimension

In this method we need to create parent-child dimension. Parent-child dimension can be created on top of a parent-child table/view where each record has a reference to its parent record (e.g. ParentID column). An example of this type of tables can be found on Adventure Works sample database.

Using ragged hierarchy with "Hide Member If" property
Another approach is to design a regular dimension on top of a ragged or unbalanced table/view. Ragged hierarchy: In a ragged hierarchy, the logical parent member of at least one member is not in the level immediately above the member.

Example: geography dimension which number of levels can be different on different countries. The levels provide a meaningful context to its members, thus, while Washington DC is a child of USA, it is included at the City level with Los Angeles. Therefore there will be nulls on some levels.

Note-1: We can also repeat the parent name instead of null as a placeholder.

Note-2: In the example above, 1st and 2nd record will be required when we need to assign facts to non-leaf members. In this example we may need to assign a fact to CA which is a non-leaf member.

Once we create the normal hierarchy, we can use HideMemberIf property to hide Nulls or repeated members.

HideMemberIf Setting	Description
Never	Level members are never hidden.
OnlyChildWithNoName	A level member is hidden when it is the only child of its parent and its name is null or an empty string.
OnlyChildWithParentName	A level member is hidden when it is the only child of its parent and its name is the same as the name of its parent.
NoName	A level member is hidden when its name is empty.
ParentName	A level member is hidden when its name is identical to that of its parent.

Example of an Account hierachy:

Important notes:

• For large parent-child dimensions, aggregations are created only for the key attribute and the top attribute, therefore queries returning cells at intermediate levels are calculated at query time and can be slow. If you have a large parent-child hierarchy (more than 250,000 members), you may want to consider using a hierarchy with a fixed number of levels.
• Microsoft recommends using P-C whenever you need to set unary operators.
• DataMember property only works in parent-child dimensions
• You can’t cross-join levels on a parent-child hierarchy. It might cause some complications in report development

References and more info:

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

http://gerardnico.com/wiki/dw/hierarchie

What's New in Microsoft SQL Server 2008 for Business Intelligence

Integration Services in SQL Server 2008

• SSIS 2008 provides the data integration features such as SSIS pipeline, SSIS persistent lookups, and SSIS data profiling that help you to integrate data more effectively.
• SSIS 2008 provides data warehousing features and tools that help you to effectively manage large volumes of data. These features and tools include partitioned table parallelism, query optimization, Resource Governor, and data compression.
• SQL Server 2008 provides the MERGE statement that helps you to simplify the code and enhance the system performance. You can also use the MERGE statement as a join between a target that is the recipient of the DML and a source that is the provider of the data.
• SQL Server 2008 provides the CDC (Change Data Capture) feature that helps you to insert records, update, and delete activities applied to SQL Server 2008 tables. You can use CDC to store details of the changes in an easy relational format. CDC also provides a structured, reliable stream of change data that can be applied by users to dissimilar target representations of data.

Reporting Services in SQL Server 2008

• SSRS 2008 includes the report server that is used to set a memory threshold for background operations and performance counters for monitoring service activity.
• SSRS 2008 supports two modes of deployment for report server, the native mode and SharePoint integrated mode.
• SSRS provides the report authoring features that help you to manage large reports. These features include Report Designer, data visualization, and Tablix.
• SSRS provides the report delivery features that help you to generate reports. The features include rich-text, Office Word rendering, Office Excel rendering, export to Office Word and Office Excel, and report delivery through MOSS.

Analysis Services in SQL Server 2008

• SSAS 2008 provides several Cube Designer enhancements for better detection and classification of attributes along with identification of member properties. These enhancements include Analysis Services personalization extensions, best practice alerts, enhanced dimension design, enhanced aggregate design, and dynamic named sets.
• SSAS 2008 provides you with the capabilities to can enhance the data mining models by appending a new algorithm to Microsoft Time Series algorithm. This new algorithim is based on the ARIMA algorithm. This enhancement improves the accuracy and stability of predictions in the data mining models.
• SSAS 2008 provides several performance enhancements to manage cube space. The enhancements include subspace computation, MOLAP-enabled write back capabilities, scale-out analysis, and scalable backup tool.

Source: MS Clinic 6189: What’s New in Microsoft® SQL Server™ 2008 for Business Intelligence

Usage-Based Optimization in Analysis Services 2005

You can optimize partitions of a measure group based on the usage. Here is the full instruction:

http://www.databasejournal.com/features/mssql/article.php/10894_3575751_1