July 7, 2014 at 12:16 am
Hi, I've been reading these pages for many years , but this is my first post here..
I would like to share a solution which I haven't seen before and wouldn't think was possible. Implemented in SQL Server 2012, it is working in production environment with no issues.
The Problem
A measure group in the cube is based on a database view. Why it is not a data mart table, populated by the ETL? Because the business requirements are changing frequently and it is much easier to simply change a view instead of changing the ETL and repopulating the table for all the dates in the past. The problem with this approach is that as the view evolves over time, it becomes more complex. In my case it contained complex joins, correlated subqueries, recursion and CTEs. The logic became hard to follow and the cube processing performance became unmanageable. The actual execution plan varied and was different from the plan observed in SQL Server Management Studio on "SELECT * FROM MyView".
The Solution
I tried to re-write the view as a table-valued function, splitting the view logic into three steps. The result of each step was stored in a table variable. The final SELECT statement combines data from a number of table variables. The code became neat and manageable and performance improved by an order of magnitude. All good - but how can I serve this function to the SSAS data source view? The solution is simple - create a view wrapper as shown below:
CREATE VIEW ViewForDSV AS SELECT * FROM dbo.MyFunction()
This is it. What does the community think?
July 7, 2014 at 12:23 am
This should've been posted under SQL Server 2012. Can anyone with the authority move the post there please?
Cheers.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply