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.

SSIS: Passing parameters from parent to child package

When using “Execute Package Task” in SSIS control flow, you can’t pass in any configuration options or variables to the child package. Here is a workaround to do that:

1. In the parent package, create a variable for each variable you want to set in child package. It must be defined as a global variable.

2. In the child package, create variables to correspond to the ones you created in the Parent package (e.g. “ChildVar” to correspond to “ParentVar”). Important: assign these variables valid default values, otherwise you will not be able to run the child package standalone.

3. In the child package, open the package Configurations. Add a new configuration, and select the “Parent package variable”. Enter the variable name you added to the parent package (in our example “ParentVar”). Note that variables are case sensitive. Assign this to the variable of the child package (”ChildVar”).

Now when you run the parent package, it will pass the value of the “ParentVar” to the “ChildVar”. When you run the child package directly (without a parent package), it will use the default value of the “ChildVar”.

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

Slowly Changing Dimension (SCD)

There are different types of Slowly Changing Dimension:

• Type 1 (overwrites the original information) (http://www.1keydata.com/datawarehousing/scd-type-1.html)
• Type 2 (a new record is added to the table to represent the new information, keeps all historical information) (http://www.1keydata.com/datawarehousing/scd-type-2.html)
• Type 3 (keeps original value, and current value in different columns of the same record) (http://www.1keydata.com/datawarehousing/scd-type-3.html)
• Type 4: uses "history tables", where one table keeps the current data, and an additional table is used to keep a record of some or all changes
• Type 6 (2+3+1) (hybrid approach that combines the three fundamental SCD techniques)

Note: SSIS SCD control only supports Type1 and Type2. But by identifying columns with the fixed attribute update type, you can capture the data values that are candidates for Type 3 changes.

• Slowly Changing Dimension Transformation (http://msdn2.microsoft.com/en-us/library/ms141715.aspx)
• Configuring Outputs Using the Slowly Changing Dimension Wizard (http://technet.microsoft.com/en-us/library/ms141662.aspx)
• http://en.wikipedia.org/wiki/Slowly_changing_dimension

Slowly Changing Dimension Transformation Outputs
Output	Description	Data flow requirements
Changing Attributes Updates Output	The record in the lookup table is updated. This output is used for changing attribute rows.	An OLE DB Command transformation updates the record using an UPDATE statement.
Fixed Attribute Output	The values in rows that must not change do not match values in the lookup table. This output is used for fixed attribute rows.	No default data flow is created. If the transformation is configured to continue after it encounters changes to fixed attribute columns, you should create a data flow that captures these rows.
Historical Attributes Inserts Output	The lookup table contains at least one matching row. The row marked as “current” must now be marked as "expired". This output is used for historical attribute rows.	Derived Column transformations create columns for the expired row and the current row indicators. An OLE DB Command transformation updates the record that must now be marked as "expired". The row with the new column values is directed to the New Output, where the row is inserted and marked as "current".
Inferred Member Updates Output	Rows for inferred dimension members are inserted. This output is used for inferred member rows.	An OLE DB Command transformation updates the record using an SQL UPDATE statement.
New Output	The lookup table contains no matching rows. The row is added to the dimension table. This output is used for new rows and changes to historical attributes rows.	A Derived Column transformation sets the current row indicator, and an OLE DB destination inserts the row.
Unchanged Output	The values in the lookup table match the row values. This output is used for unchanged rows.	No default data flow is created because the Slowly Changing Dimension transformation performs no work. If you want to capture these rows, you should create a data flow for this output.

SQL Server 2005 Books Online (September 2007)