Analysis Server 2000 performance Benchmark on FoodMart 2000

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

WITH measures.[__Customers] as '"[__Customers]"',visible=0 Member measures.[__Customers_selection] as'"{ AddCalculatedMembers({[Customers].[Country].AllMembers}) }"' ,visible=0Set [__Customers_main] as '{ Hierarchize(Distinct(StrToSet(CStr(measures.[__Customers_selection])))) }'SET [__Customers_RootMembers_Fast] As'{[__Customers_main]}' SET [__Customers] As'[__Customers_RootMembers_Fast]' measures.[__Product] as '"[__Product]"',visible=0 Member measures.[__Product_selection] as'"{ AddCalculatedMembers({[Product].[Product Subcategory].AllMembers}) }"' ,visible=0Set [__Product_main] as '{ Hierarchize(Distinct(StrToSet(CStr(measures.[__Product_selection])))) }'SET [__Product_RootMembers_Fast] As'{[__Product_main]}' SET [__Product] As'[__Product_RootMembers_Fast]' measures.[__Store] as '"[__Store]"',visible=0 Member measures.[__Store_selection] as'"{ AddCalculatedMembers({[Store].[Store State].AllMembers}) }"' ,visible=0Set [__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]})' measures.[__Time] as '"[__Time]"',visible=0 SET [__Time_RootMembers_Fast] As'[Time].Levels(0).AllMembers' SET [__Time] As'[__Time_RootMembers_Fast]' 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=0Set [__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

Well, I haven't the time to tell you why, but the MDX above will not run in either of my MDX tools|||

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

WITH
Member 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.

an UPDATE inside a SELECT

I was wondering if it is possible to have an UPDATE satement inside a
SELECT statment. What I want to do is select certian rows from a table
then based on the result I want to update another table. Metaphorically
something like the following
SELECT A.QTY,(UPDATE TABLE B SET QTY=A.QTY WHERE B.[ID]=A[ID]) FROM
TABLE A
*** Sent via Developersdex http://www.examnotes.net ***Hi,
Looks like you are trying to Select and Update the contents of the table in
one go.
Try to use a Stored Procedure for this purpose.
Hope this answered your Question.
thanks,
Chandra
"Hussain Al-Dhaheri" wrote:

> I was wondering if it is possible to have an UPDATE satement inside a
> SELECT statment. What I want to do is select certian rows from a table
> then based on the result I want to update another table. Metaphorically
> something like the following
> SELECT A.QTY,(UPDATE TABLE B SET QTY=A.QTY WHERE B.[ID]=A[ID]) FROM
> TABLE A
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Hi
No, you vannot do it in that way
[Untested]
UPDATE TableA SET col=(SELECT b.col FROM TableB b WHERE b.col=col)
WHERE EXISTS (SELECT * FROM TableB b WHERE b.col=col)
SELECT <column lists> FROM
"Hussain Al-Dhaheri" <hdhaheri@.hotmail.com> wrote in message
news:e4s4e1tSFHA.3672@.TK2MSFTNGP10.phx.gbl...
> I was wondering if it is possible to have an UPDATE satement inside a
> SELECT statment. What I want to do is select certian rows from a table
> then based on the result I want to update another table. Metaphorically
> something like the following
> SELECT A.QTY,(UPDATE TABLE B SET QTY=A.QTY WHERE B.[ID]=A[ID]) FROM
> TABLE A
>
> *** Sent via Developersdex http://www.examnotes.net ***sql

AMO created cube and Calculated Measures

I'm using AMO (based on the Adventure Works sample) to create a simple cube. I create it with a Measure Group named BaseFact that has 2 measures, Dollar Sales (DOLSLS) and Package Sales (PKGSLS). The cube create works and I can see the correct values for each of the measures when I browse it in Management Studio connected to Analysis Services.

To continue with the cube (and my education) I want to dynamically add a calculated measure or two. The forums seem to suggest that this is not possible using AMO and suggest using the cubes MdxScript to create calculated measures.

I used script to new query window to look at the XMLA script for the cube. There is no MdxScripts section in the XML text. If I look at the cube in the debugger it shows the MdxScripts collection count to be 0. After running the code below to get an MdxScript object, add new commands and update the cube I can again look at the XMLA for the cube and now there is a MdxScript with only the new measures I created. The measures also show up when I browse the cube. However, I can no longer see any data for the original measures or the added measures. I'd appreciate any help in understanding what is happening.

Server srv = new Server();
srv.Connect("localhost");
Database db = srv.Databases["MyCube"];
Cube cb = db.Cubes["MyCube"];
MdxScript ms = cb.MdxScripts.Add();
string cmd = "CREATE MEMBER CurrentCube.Measures.Price as [Measures].[DOLSLS] / [Measures].[PKGSLS]";
ms.Commands.Add(new Command(cmd));
cmd = "CREATE MEMBER CurrentCube.Measures.MyMem2 as [Measures].[Price] * 2";
ms.Commands.Add(new Command(cmd));
cmd = "CREATE MEMBER CurrentCube.Measures.MyMem3 as [Measures].[Price] * 3";
ms.Commands.Add(new Command(cmd));

ms.Update();
cb.Update();
cb.Process();
srv.Dispose();

Thanks
John

Solved it.
1. Added semi-colon to end of strings.
2. I thought I had read somewhere in the forums that the CALCULATE as the first command was optional but it isn't. Added the CALUCULATE and it all works.

John

AMO created cube and Calculated Measures

I'm using AMO (based on the Adventure Works sample) to create a simple cube. I create it with a Measure Group named BaseFact that has 2 measures, Dollar Sales (DOLSLS) and Package Sales (PKGSLS). The cube create works and I can see the correct values for each of the measures when I browse it in Management Studio connected to Analysis Services.

To continue with the cube (and my education) I want to dynamically add a calculated measure or two. The forums seem to suggest that this is not possible using AMO and suggest using the cubes MdxScript to create calculated measures.

I used script to new query window to look at the XMLA script for the cube. There is no MdxScripts section in the XML text. If I look at the cube in the debugger it shows the MdxScripts collection count to be 0. After running the code below to get an MdxScript object, add new commands and update the cube I can again look at the XMLA for the cube and now there is a MdxScript with only the new measures I created. The measures also show up when I browse the cube. However, I can no longer see any data for the original measures or the added measures. I'd appreciate any help in understanding what is happening.

Server srv = new Server();
srv.Connect("localhost");
Database db = srv.Databases["MyCube"];
Cube cb = db.Cubes["MyCube"];
MdxScript ms = cb.MdxScripts.Add();
string cmd = "CREATE MEMBER CurrentCube.Measures.Price as [Measures].[DOLSLS] / [Measures].[PKGSLS]";
ms.Commands.Add(new Command(cmd));
cmd = "CREATE MEMBER CurrentCube.Measures.MyMem2 as [Measures].[Price] * 2";
ms.Commands.Add(new Command(cmd));
cmd = "CREATE MEMBER CurrentCube.Measures.MyMem3 as [Measures].[Price] * 3";
ms.Commands.Add(new Command(cmd));

ms.Update();
cb.Update();
cb.Process();
srv.Dispose();

Thanks
John

Solved it.
1. Added semi-colon to end of strings.
2. I thought I had read somewhere in the forums that the CALCULATE as the first command was optional but it isn't. Added the CALUCULATE and it all works.

John

AMO - Memory Error !!! PLEASE HELP

based on the Adventure Works example i decide to create the DSV, when i call the method ' AddTable ' as follows:

-

private void AddTable(Microsoft.AnalysisServices.DataSourceView dsv, OleDbConnection connection, String tableName)
{
OleDbDataAdapter adapter = new OleDbDataAdapter(
"SELECT * FROM [dbo].[" + tableName + "] WHERE 1=0",
connection);
DataTable[] dataTables = adapter.FillSchema(dataSet, SchemaType.Mapped, tableName);
DataTable dataTable = dataTables[0];

dataTable.ExtendedProperties.Add("TableType", "Table");
dataTable.ExtendedProperties.Add("DbSchemaName", "dbo");
dataTable.ExtendedProperties.Add("DbTableName", tableName);
}

-

when i try to create the Dim product calling this function i got this error:

Memory error: While attempting to store a string, a string

was found that was larger than the page size selected. The operation

cannot be completed.
Errors in the OLAP storage engine: An error

occurred while the 'LargePhoto' attribute of the 'DimProduct' dimension

from the 'teste2005' database was being processed.
Errors in the

OLAP storage engine: The process operation ended because the number of

errors encountered during processing reached the defined limit of

allowable errors for the operation.

-

And i know that this functions works well because if i comment the creation of DimProduct, everything works fine, so the problem is with some atributes, like ' LargePhoto' for example,

How can i fix this memory error ?, i read some posts in the forum, but i didn't get a concrete answer,

PLEASE HELP !!!!!This may help:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=145355&SiteID=1

C

Alternative to IIF ? Case ?

Hi,
Need to display a value in a text box based upon perhaps one of four
values a parameter could have. This example is great for two values, = iif(UCASE(Parameters!SUPPID.Value) ="HHH", "Defense & Space", "Defense")
Is there an alternative , case... anything if we need to test for over
parameter values i.e. HHH1, or HHH2.
Many thanks, Steve.Try Switch:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctSwitch.asp
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:77DEB13A-6813-415E-A4A2-58190F3A7CF4@.microsoft.com...
> Hi,
> Need to display a value in a text box based upon perhaps one of four
> values a parameter could have. This example is great for two values, => iif(UCASE(Parameters!SUPPID.Value) ="HHH", "Defense & Space", "Defense")
> Is there an alternative , case... anything if we need to test for over
> parameter values i.e. HHH1, or HHH2.
> Many thanks, Steve.|||Report parameters have a Value and a Label property. You could use the Label
property for this purpose.
Regarding alternatives for IIF please check the following MSDN links:
* =Choose(...)
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctchoose.asp
* =Switch(...)
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctswitch.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:77DEB13A-6813-415E-A4A2-58190F3A7CF4@.microsoft.com...
> Hi,
> Need to display a value in a text box based upon perhaps one of four
> values a parameter could have. This example is great for two values, => iif(UCASE(Parameters!SUPPID.Value) ="HHH", "Defense & Space", "Defense")
> Is there an alternative , case... anything if we need to test for over
> parameter values i.e. HHH1, or HHH2.
> Many thanks, Steve.|||Thank you Robert !
"Robert Bruckner [MSFT]" wrote:
> Report parameters have a Value and a Label property. You could use the Label
> property for this purpose.
> Regarding alternatives for IIF please check the following MSDN links:
> * =Choose(...)
> http://msdn.microsoft.com/library/en-us/vblr7/html/vafctchoose.asp
> * =Switch(...)
> http://msdn.microsoft.com/library/en-us/vblr7/html/vafctswitch.asp
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:77DEB13A-6813-415E-A4A2-58190F3A7CF4@.microsoft.com...
> > Hi,
> > Need to display a value in a text box based upon perhaps one of four
> > values a parameter could have. This example is great for two values, => > iif(UCASE(Parameters!SUPPID.Value) ="HHH", "Defense & Space", "Defense")
> >
> > Is there an alternative , case... anything if we need to test for over
> > parameter values i.e. HHH1, or HHH2.
> >
> > Many thanks, Steve.
>
>|||Switch rocks !!! Thanks Jeff. Happy New year.
"Jeff A. Stucker" wrote:
> Try Switch:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctSwitch.asp
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:77DEB13A-6813-415E-A4A2-58190F3A7CF4@.microsoft.com...
> > Hi,
> > Need to display a value in a text box based upon perhaps one of four
> > values a parameter could have. This example is great for two values, => > iif(UCASE(Parameters!SUPPID.Value) ="HHH", "Defense & Space", "Defense")
> >
> > Is there an alternative , case... anything if we need to test for over
> > parameter values i.e. HHH1, or HHH2.
> >
> > Many thanks, Steve.
>
>