Data Access Layer for Analysis Services w/Dynamic MDX

Question

We have project that uses Analysis Services as it's datasource. To try to avoid having to create 100's of queries because of all the selection options we allow, we create our mdx queries with alot of switches and string concatenation. This is our "Data Access Layer". It is a beast to manage and the smallest mistake: missing spaces, mispellings are easy to miss and even easier to accidently include. Does anyone know of a good resource that can help make this more manageable, like a tutorial, white paper or sample project.

To give you an idea of the case logic I'm talking about and it goes on and on... if (Time == Day) { if (Years == One) { return (" MEMBER " + CurrentSalesPercent + " AS ([Sales % " + YearString + " " + StatusType + "]) "); } else //2Y { return (" MEMBER " + CurrentSalesPercent + " AS ([Sales % 2Y " + StatusType + "]) "); } } else if (Time == Week) { if (Years == One) { return (" MEMBER " + CurrentSalesPercent + " AS ([Sales WTD % " + YearString + " " + StatusType + "]) "); } else //2Y { return (" MEMBER " + CurrentSalesPercent + " AS ([Sales WTD % 2Y " + StatusType + "]) "); } ...

To be honest, I'm not sure if all the different measures and calculations are correct either. But, that's controlled by another team, so we have a little less influence here.

Thanks! mkt

Answer 1

Have you looked at the way MS generates MDX? If you have SSRS installed, get "Red gate Reflector" and disassemble C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin\MDXQueryGenerator.dll

Apart from that, pre-canned queries that take parameters seems pretty standard :(