Do you know about the bundled reporting tool that comes with SQL Server? SSRS (SQL Server Reporting Services).
Well, I’ve been using this lately more than Power Pivot. The reason? Quite simply, the data being queried is already present in an Analysis Services cube and there has been no need to mash-up the data with anything else. As much as I like working with DAX there is simply no point of moving the data from the cube when we simply query it in place via SSRS.
In my place of work I have a very demanding sales director who naturally wants to spend his time managing the sales team. He is not interested in clicking slicers or analysing data. He wants the report delivered on a plate.
In SQL Server 2008 R2, SSRS is a very rich report authoring tool and it allows me to send the report directly to the sales directors’ inbox just as he wanted.
There are some limitations when using Analysis Services as a source. The primary one being that measures must be placed in columns and attributes in rows. This means you lose the benefit of multi-dimensional cross tab reports that Analaysis Services provides.
It’s normally possible to work around this problem by re-structuring the data source of the report but it is here that you have to get your hands dirty with MDX. Personally, I don’t mind this as once the report is created you can kind of set it and forget it.
In the past I’ve been spoilt by building reports in Excel and querying Analysis Services using the ADO cube set object. The cube set object doesn’t have this limitation and with VBA it’s possible to manipulate data in any way you want.
This situation got me thinking. If I have a table of data, let’s say it’s a classification table of accounts (A) that summarise the detailed accounts in the accounting system. It’s relatively easy to move this data into SQL Server. Then, as long as we’re querying the account system via SQL we can simply join this data to table A.
Now of course this leaves the question about how table A is administered. Enter Lightswitch. Lightswitch is a development tool from Microsoft that is aimed at business users and enables users to rapidly create business applications. I bet it’s possible to create an admin tool very quickly that allows updates to be made to the SQL table.
In conclusion, the more I work with SQL Server and PowerPivot I’m beginning to think in some cases the traditional BI approach is dead.
Why spend 6 months working on a BI project when you can work in a more proactive way to support the business with facts when they need them? Now I know I’ve oversimplified ETL in this discussion, however, even with ETL I still think this type of approach is more agile.
What do you think?