MDX Debugging : OLAP PivotTable Extensions

In the spaceo of MDX debugging from the OLAP client side, one of the hidden gem (from my eyes) got discovered by me, when one of the reader of my blog posted a comment on my previous post. The this gem is knows as "OLAP Pivot Table Extensions".

Out of sone of the striking features of this add-inl, one is "If a PivotTable is performing poorly or returning incorrect numbers, it may be necessary for the Analysis Services administrator to troubleshoot the MDX query which the PivotTable is using. The MDX tab of the OLAP PivotTable Extensions dialog shows you this MDX". This add-in also has quite a number of limitations too and it has a great potential for improvement. But the value that it provides, that too for free is very similar to PTPower and interesting. It has some of the features which PTPower is missing.

I feel that PWPower + OLAP PivotTable Extensions used together, can make one's experience on debugging issues from the client end (i.e. excel as a client or consumer of Analysis Services) much warmer.

MDX Generator - PTPower Excel 2007 Add-In

One of the most fantastic add-in that I have seen in recent times, I am going to introduce it right now. I find it amazing due to the value it brings to MDX development. I have come across too many MDX aspirants who want to learn the same. I have found too less tools that can help in MDX debugging. This add-in has two great striking features, one is it comes for free and second is that it can extract MDX from aggregations. It's built upon ADOMD.Net that's a part of SQL Server 2008 feature pack.

PTPower is an Excel 2007 Add-In that lets you view the MDX and/or create Calculated Members for pivot tables based on Analysis Services (2005 or 2008) cubes. Thou I have not used/tested this add-in, but if it lives upto what it states, I give it a 5-star.

Reference: Andrew Fryer's Blog

SQL Server Tools : Qure Product Review

Today I received an email from Ami Levin. He is a SQL Server MVP and having an experience of working more than a decade on SQL Server. He introduced me to a product called "Qure".

I went through the product information page of Qure, and found that it focuses on 4 areas for SQL Server Performance Optimization. They are as below:

1) Smart Indexing - Create, Drop and Modify Indexes for Best Overall Performance

Details can be found on product page.

My View: The product page says that Qure has a patented algorithm which they call as Smart Indexing, which suggests code and places where indexes should be changed. I would like to review and compare, how better is this than Index Tuning Wizard or Database Tuning Wizard results. If it's better than that, may be Microsoft should have review theirs.

2) Query Syntax Optimization

Details can be found on product page.

My View: This is one of nice feature which I like, provided it results in better performance. Generally it's not feasible to change lots of code in production, as there are a lot of approval cycles that goes on. And if just for syntax if I am asked for an approval of modifying 100 objects, I won't approve it. But for example changing 35-50 odd objects can reap me at least 5% performance gain, I would be more than happy to see it happening.

By using a kind of work-around on DBPro this can be achieved. But it won't tell you the performance gain.

3) Schema Optimization

Details can be found on product page.

My View: This feature is like combining data profiling capabilities of SSIS with Database Tuning Advisor. This really makes me curious of exploring, how effective this feature is. If it works to my expectation, it can revolutionize database modeling.

4) Additional Optimizations

Details can be found on product page.

My View: I would like to compare performance of this feature with that of Best Practices Analyzer tool.

By and large, it seems like this is a big thing in a small package. Pricing of this product in alignment with the features offered seems to be a high, compared to Quest software products. Allow me some time to evaluate and compare it with other tools and I would be back with results shortly.

SSIS Installation Acceptance Testing

Installation of systems like ERP / SAP generally requires clients to sign off documents for acceptance testing. But this is generally not required in case of a typical installation for SSIS. Recently I came across a scenario where I was required to provide documentation and deliverables for acceptance testing of SSIS Installation at a client location.

Thanks to Microsoft, that it ships was a dozen of samples. Two of the samples which can be used as a base of such acceptance testing are the "AWDataWareHouseRefersh" and "Data Cleaning".

Execution of "AWDataWareHouseRefersh" sample tests components like Sequence Container, For Loop Container, For Each Loop Container, Execute SQL Task, Bulk Insert Task, Data Flow Task and connection adapters as well.

Execution of "Data Cleaning" sample tests components like Lookup, Fuzzy Lookup, Derived Column, Union All, Conditional Split, Data Flow Task and connection adapters as well.

I feel this much is enough for basic smoke testing of a proper SSIS installation.