I'd like to have a performance benchmark on analysis services. I deployed a solution based on analysis services and I'd like to understand if performances are in line with other solutions. I 'm afraid that some hardware configuration is not properly set. So I ask to you if you can tell me how much time takes a report run on FoodMart 2000 , cube Warehouse and Sales.
I built the following report, and the MDX code has been automatically generated by our Client Tool. Even if is not the best MDX coding, I just need to unserstand if there are some differences in response time.
Thanks in advance
I think it is due to code formatting when I attached the code. I tried to copy and paste the code and it is true it doesn't work.
If you can try again......
Thanks in advance
Code Snippet
WITHMember measures.[__Customers] as '"[__Customers]"',visible=0
Member measures.[__Customers_selection] as '"{ AddCalculatedMembers({[Customers].[Country].AllMembers}) }"' ,visible=0 Set [__Customers_main] as
'{ Hierarchize(Distinct(StrToSet(CStr(measures.[__Customers_selection])))) }' SET [__Customers_RootMembers_Fast] As '{[__Customers_main]}'
SET [__Customers] As '[__Customers_RootMembers_Fast]'
Member measures.[__Product] as '"[__Product]"',visible=0
Member measures.[__Product_selection] as '"{ AddCalculatedMembers({[Product].[Product Subcategory].AllMembers}) }"' ,visible=0 Set [__Product_main] as
'{ Hierarchize(Distinct(StrToSet(CStr(measures.[__Product_selection])))) }' SET [__Product_RootMembers_Fast] As '{[__Product_main]}'
SET [__Product] As '[__Product_RootMembers_Fast]'
Member measures.[__Store] as '"[__Store]"',visible=0
Member measures.[__Store_selection] as '"{ AddCalculatedMembers({[Store].[Store State].AllMembers}) }"' ,visible=0 Set [__Store_main] as
'{ Hierarchize(Distinct(StrToSet(CStr(measures.[__Store_selection])))) }' SET [__Store_RootMembers_Fast] As '{[__Store_main]}'
SET [__Store] As '[__Store_RootMembers_Fast]'
SET [_Rows] as'Crossjoin({Crossjoin({[__Customers]}, {[__Product]})}, {[__Store]})'
Member measures.[__Time] as '"[__Time]"',visible=0
SET [__Time_RootMembers_Fast] As '[Time].Levels(0).AllMembers'
SET [__Time] As '[__Time_RootMembers_Fast]'
Member measures.[__Measures] as '"[__Measures]"',visible=0
Member measures.[__Measures_selection] as '"{ [Measures].[Warehouse Cost], [Measures].[Warehouse Sales], [Measures].[Units Shipped], [Measures].[Units Ordered], [Measures].[Warehouse Profit], [Measures].[Unit Sales] }"' ,visible=0 Set
[__Measures_main] as '{ Hierarchize(Distinct(StrToSet(CStr(measures.[__Measures_selection])))) }' Set [__Measures_RootMembers_Smart] as
'{ Filter([__Measures_main], Count(Intersect(Ascendants([Measures].CurrentMember),[__Measures_main])) = 1) }'
SET [__Measures] As '[__Measures_RootMembers_Smart]'
SET [_Columns] as'Crossjoin({[__Time]}, {[__Measures]})'
SELECT [_Columns] DIMENSION PROPERTIES Parent_Unique_Name on columns, [_Rows] DIMENSION PROPERTIES Parent_Unique_Name on rows
FROM [Warehouse and Sales]
CELL PROPERTIES back_color,fore_color,format_string,font_name,font_size,font_flags,cell_evaluation_list,value |||
Seems to take 2-4 seconds
2 on the server
4 remoting from my machine using Proclarity
|||Against AS2000 it takes more or less 10 seconds using MSX sample application on a client machine.
If I run it on the server machine it seems that nothing change (1 second difference)
It is more than the double.
Now I have to understand why?
We have installed the default configuration of AS2000 on the server.
|||On AS2005 it takes:
Cold cache: 4 seconds in SQL Server Management Studio, but in the server it takes only 0.812 seconds. Warm cache: 4 seconds in SQL Server Management Studio, but in the server it takes only 0.625 seconds.|||Thanks for your intervention.
I have a question, sorry but I'm not so smart in performance tuning. This is not my daily job.
How Do you trace the time that the query takes on the server when you run it from the client application? Do you have a tool or Do you use the standard performance counter. If so which is the counter that you trace?
|||Well, in AS2005 is very easy! Just turn on the Profiler. However, it does not work in AS2000.
In AS2000, we have a add-in to Excel that lets you time the duration of the Pivot Table refresh.
There was a mdx_applet.xls file that allow you to run queries in MDX, from Excel. There you should implement the timing feature.
No comments:
Post a Comment