New SQL Server 2012 BI tools

 “Three things cannot be long hidden: the sun, the moon, and the truth.”- Buddha

In Microsoft’s case, try as they might to be cute in naming their new products that no one understands, BI professionals have uncovered key features. I bring this up because many organizations are Microsoft-centric. Microsoft doesn’t make it easy to understand how to fully utilize their technologies.
Microsoft will launch their newest version of the venerable database system, SQL Server 2012 (Online launch event) on March 7.
New tools in SQL Server 2012 that I found:

• SQL Server Data Tools (SSDT) – the tool to develop database models, SSRS reports, SSIS packages, and more.

It will replace the Business Intelligence Development Studio (BIDS). Check out SQL Server’s Pro’s write-up.

• BI Semantic Tabular Model – Allows an analyst, as well as a developer, to create data models that can be published on SharePoint. In Microsoft’s vision (See in diagram below), developers create multi-dimensional models (cubes) and business analysts create tabular models.

Thanks to James Serra’s blog entry for clearing up Microsoft’s marketing mumbo-jumbo.

An analyst can design a data model by visually linking tables and writing DAX (Data Analysis Expressions language) formulas to encapsulate business logic.
Check out this slide deck
Check out demo

• PowerView -  PowerView is an interactive data exploration, visualization, and presentation experience. It provides intuitive ad-hoc reporting for business users such as data analysts, business decision makers, and information workers.

Features:

• Interactive, web-based report authoring & sharing
• Powerful BI data layout with new visualization
• Multi-view story board in tool, and Export to PowerPoint & present interactively with data
• Runs off PowerPivot BI data, or other SQL 2012 tabular models
• Smart and powerful querying, with zero configuration for filtering

Check out the Demo 1 or Demo 2

Trust me, data cubes are great

"Come with me if you want to live" 
 Arnold Schwarzenegger in Terminator 2: Judgment Day 


Two thoughts come to mind as I remember this quote:

1. Linda Hamilton's character must take a leap of faith and quickly
2. The Terminator has a plan that will weather the upcoming change, i.e. Terminator T-1000's relentless pursuit.

In the case of Healthcare analystics, perhaps the above quote should read something like  "Trust me, this new technology (EHR, data cubes, etc) will work if you intend to survive healthcare reform". 

Trust and new technology...Can that those words be used in the same sentence? I'm optimistic so yes. 

I'm faced with transitioning to data cubes. I'm told I'll have little need for my old skills. The new technology will be more efficient and robust. Over the years, I've become quite proficient with Microsoft Excel/Access and SQL. I feel comfortable. How can I trust the results of a data cube when time is of essence?

Two actions are needed to answer this question:

• Design a cube with a solid specification
• Test the cube with a robust Q/A checklist. This ensures the specification was followed and business logic developed appropriately. 

Given where my mind is today, I want to focus on the Q/A checklist. I googled "cube testing" and most links (this one or this one) are from a developer's perspective. If I relied on these links, it's as if my mechanic believes my car is fixed, but I need to be assured in terms I can understand. 


I developed the list below with the help of Google searches, colleagues, and mostly from frustrating experiences. 

1.    Ensure requested cube attributes are present 

2.    Ensure requested cube attributes have appropriate display names

3.    Challenge the logic behind each attribute. 
     If you are transitioning from a legacy system to a data cube, this is a vital step.

4.    Ensure cube attributes list properties either from the underlying database or custom list.

5.    Ensure relationships between fact table and dimension tables actually work as advertised

6.    Place extreme conditions in to see cube to see how it reacts 

7.    Look for known trends in the legacy system and see if they are present in the data cube

8.    Does the data cube answer specified business questions?

9.    Can data cube based reports replace your legacy reports?