In my last post, I discussed the new AlwaysOn and Security features in SQL Server “Denali” CTP3. This post is about some of the Business Intelligence (BI) improvements, and changes in SSIS and Data Tools.
I am going to focus on three different areas of BI changes that I think are notable:
- Columnstore – if you are a heavy OLAP user, you will probably be pretty excited about Columnstore, and this may be THE selling feature of the product for you. Without going into b-trees, vectors, segments and dictionaries, here is the bottom line: you can probably see speed improvements of hundreds to thousands of times by using columnstore. Columnstore derives its name from the fact that the columnstore index stores the data in columns instead of rows.
- Project “Crescent” – Crescent is such a huge addition that it is hard to describe without a full week’s worth of posts. Imagine having a BI tool with an Office-like interface, data that is easy to find and use with just a few clicks, the ability to navigate your model, see table subtotals, totals and blocking, matrix subtotals, grand totals and blocking, Excel-like charts and graphs, motion charts, interactive models and the ability to filter with sliders, calendars, lists or custom parameters. All of that and much more is included in Crescent. Crescent seems to be a project where the SQL Server team took a step back and said, “What should BI really look like?”. I think you will be pretty excited about Crescent.
- Self-Service BI – Microsoft has continued down the road of self-service BI, and has enhanced tools like PowerPivot to support KIP’s, Top N, new financial and statistical calculations, and perspectives. While the full functionality continues to rely on Sharepoint, the fact that your end-users can work on the data without IT’s involvement continues to be the selling point.
SSIS / Data Tools
There is really a lot of work that has taken place in Denali around data quality, moving data between systems and data modeling. For lack of a better way to organize this data, I have lumped it all together under “SSIS / Data Tools” because to me, most of these features are related and will be used by the same groups of users. All of this falls under what Microsoft is calling “Enterprise Information Management (EIM)”.
- Master Data Services (MDS) – gone are the days when creating lookup or fact tables from multiple systems involved iterations of importing from Excel, showing the user, exporting back to Excel, letting the user clean up the data and then starting over. MDS allows multiple to collaborate on the data, even using tools like Excel, while not losing data consistency.
- Data Quality Services (DQS) – Along with MDS, DQS allows you to cleanse and scrub your data using a Data Quality Knowledge Base (DQKB). While that sounds daunting, getting up and running with a DQKB can be done in just a few minutes. If your business could benefit from more consistent data, DQS can help you achieve those goals both now and into the future as you work with new data.
- SSIS – SSIS has been enhanced in many ways, including an improved UI, toolbox and undo/redo functionality. In addition, there are built-in troubleshooting reports, simplified deployment, and a better packaging, which helps with version control. The changes in SSIS are substantial and are another area that is worth a deeper dive here in the coming months.
If you are a BI user or work with SSIS or Data stores, I think you will find quite a few features in Denali that can help you with your job.
Check out Part 3 for Programmability and Other enhancements in CTP3.