Saturday, November 02, 2013

Cant find Microsoft.AnalysisServices.AdomdClient in Add Reference window


I was working on SSAS Cube programmatically ( C#), the other night and I realized in Visual Studio required reference for SSAS Classes doesn’t come by default in Add Reference window.



They have to be located by browsing to their location
  
1)      To add Microsoft.AnalysisServices.dll
Browse to  C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.AnalysisServices.dll

2)    To add Microsoft.AnalysisServices.AdomdClient
Browse to  C:\Program Files\Microsoft.NET\ADOMD.NET\100\Microsoft.AnalysisServices.AdomdClient.dll

Wednesday, February 20, 2013

How does MDX Query run


 
When user fires a MDX query it goes through sequence of steps


1.      Formula Engine: Formula engine receives the MDX first hand and then parses it. Once MDX is correctly parsed it looks for data in its Cache, if find all the data it needs, it will skip Storage engine(step 2) and goes to Step 3



2.      Storage Engine: Formula engine sends request to Storage engine for data. Storage engine looks into its Cache and if doesn’t fine requested data if fetches data from physical storage and returns back to Formula engine



3.      Formual Engine: Once it get all the Data it needs, performs calculations specified in MDX and return result set to MDX editor.



Form above Steps we can see the there could be two bottleneck for a MDX query

a)    Data Fetch from Storage engine

b)    Performing Calculation



So How do we know where it is stuck.


User profile and run MDX in background. Now check for Query Subcube and Query Subcube Verbose Event in profiler, when u sum the time from these events corresponding to the Query you will get the Time taken by Storage Engine to return the Data. Now if you subtract this time from Total time taken by Query you will get time taken in performing Calculation. 



There we are we know the time taken at each step and choose the direction to look for.

Cheers!!