Performance Improvement in Analysis Services 2005

Hi,

I am new to Analysis Services and was wondering if some one could
assist me with the issues I am having.

Basically we have one single table on which I need to build a cube so
that managemet can view the data in excel and have good perofrmance. I
started with AS 2000 but ran into the issue of having more than 64,000
members for a dimension level which were not unique.I tried various
suggestions given on the forum but none worked.

So I installed AS 2005 and migrated the database from AS 2000 and build the cube. However I am running into major performance issues, What I have are 2 dimensions with hierarchies Products and Time . The lowest dimesion in Products in something called Instrument Id and it is the 4th level in my hierarchy, the Time dimension has 3 levels, month being the lowest.

I read the articles which described the difference between hierarchies and attribute relationships in AS 2000 and AS 2005. So I defined atributes in my cube design for the above mentioned dimensions. However for the lowest level of detail ( Instument Id across Months ) the query takes for ever. I have the aggregation set at 70%

I know there is an issue with my design but with my limited knowledge of Analysis Serivces I am unble to focus in the right direction. Can someone help me with this

Thanks

For the clues on how to deal with performance problems and the for the better desing ideas take a look at the showcase study; project REAL

http://www.microsoft.com/technet/prodtechnol/sql/2005/projreal.mspx

Particularly in your case I can think of some reasons for slower performance.

For one, the query you are trying to run is going to scan your entire fact and is probably going to bring very big result. Try and make sure you provide a slice for your query. Select only few Instrument Id's not entire level.

Second, try and partition your measure group. Having several partitions would help to speed up your query. This is because Analysis Server can detect which partitions hold the data for the particular Instument Id's you query for and will only scan these partitions.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward,

Thanks for the input, just a couple of quick follow up questions

1. When you say "make sure you provide a slice for your query. Select only few Instrument Id's not entire level" I am not sure how i would go about doing it.

2. As far a partitions I was trying to create partitions for each month of data using the query binding feature. I am not supposed to create a poartition against a dimension. and how would I go about creating partitions across a measure group.

Thanks

|||Dear All,

I'm having a problem with performance in SSAS 2005. This is the server specification:
OLAP

Server and ORACLE Data Warehouse Server (64 bit) : IBM xSeries 460,

windows 2003 EE x64 SP1, SQL Server Analysis Services 2005 x64 SP1 +

Hotfix, , ORACLE 10g R2, 8x Xeon 3 GHz, memory 8 GB. Data Warehouse is

located on drive D 400GB and OLAP is in drive E 300GB and both of them

are HDD External using SAN (Storage Area Network) RAID 5.
Application Server (32 bit) : 52 IBM xSeries 366 Windows 2003 R2 SP1 BI Serv Dev ,sqlserv 2005 8x Xeon 3GH, mem 8 GB

For

the Data warehouse I'm using ORACLE 10g R2 64 bit and for the OLAP I'm

using SSAS 2005 64 bit. I'm already installed the oracle client. For

the front-end, I'm using Proclarity.

I have problems with the

performance when querying the OLAP by using ProClarity or SSMS

(Management Studio). The response time is very low, but the CPU

Utilization is below 10% and Memory Usage not more than 50%. When I

check in the performance monitor, the I/O access is very high

continuously 100% utilization when I are running query from ProClarity

Analytics Server. For improving the performance I have applied design

aggregation for each cube. However, design aggregation cannot be made

on several cubes that have a lot of data, and have a lot of measures

and dimension (the number of fields in the table more or less 150

fields). They return 0% optimization level when I run design

aggregation.

FYI, I have done several ways for optimizing the performance such as:
1. Using MOLAP storage mode with 100% design aggregation, except several cubes that can not be made.
2. Cube design follows the best practice, e.g. using key member.
3. Separation of physical storage for data warehouse and OLAP files in different disk.
4. I also used usage-based optimization but it not works.

My

key points are how to improve the performance and fix the aggregation

problem that cannot be made on several cubes. I am looking forward to

hear from you soon.

Thanks in advance.|||Dear All,

I'm having a problem with performance in SSAS 2005. This is the server specification:
OLAP

Server and ORACLE Data Warehouse Server (64 bit) : IBM xSeries 460,

windows 2003 EE x64 SP1, SQL Server Analysis Services 2005 x64 SP1 +

Hotfix, , ORACLE 10g R2, 8x Xeon 3 GHz, memory 8 GB. Data Warehouse is

located on drive D 400GB and OLAP is in drive E 300GB and both of them

are HDD External using SAN (Storage Area Network) RAID 5.
Application Server (32 bit) : 52 IBM xSeries 366 Windows 2003 R2 SP1 BI Serv Dev ,sqlserv 2005 8x Xeon 3GH, mem 8 GB

For

the Data warehouse I'm using ORACLE 10g R2 64 bit and for the OLAP I'm

using SSAS 2005 64 bit. I'm already installed the oracle client. For

the front-end, I'm using Proclarity.

I have problems with the

performance when querying the OLAP by using ProClarity or SSMS

(Management Studio). The response time is very low, but the CPU

Utilization is below 10% and Memory Usage not more than 50%. When I

check in the performance monitor, the I/O access is very high

continuously 100% utilization when I are running query from ProClarity

Analytics Server. For improving the performance I have applied design

aggregation for each cube. However, design aggregation cannot be made

on several cubes that have a lot of data, and have a lot of measures

and dimension (the number of fields in the table more or less 150

fields). They return 0% optimization level when I run design

aggregation.

FYI, I have done several ways for optimizing the performance such as:
1. Using MOLAP storage mode with 100% design aggregation, except several cubes that can not be made.
2. Cube design follows the best practice, e.g. using key member.
3. Separation of physical storage for data warehouse and OLAP files in different disk.
4. I also used usage-based optimization but it not works.

My

key points are how to improve the performance and fix the aggregation

problem that cannot be made on several cubes. I am looking forward to

hear from you soon.

Thanks in advance.|||Dear All,

I'm having a problem with performance in SSAS 2005. This is the server specification:
OLAP

Server and ORACLE Data Warehouse Server (64 bit) : IBM xSeries 460,

windows 2003 EE x64 SP1, SQL Server Analysis Services 2005 x64 SP1 +

Hotfix, , ORACLE 10g R2, 8x Xeon 3 GHz, memory 8 GB. Data Warehouse is

located on drive D 400GB and OLAP is in drive E 300GB and both of them

are HDD External using SAN (Storage Area Network) RAID 5.
Application Server (32 bit) : 52 IBM xSeries 366 Windows 2003 R2 SP1 BI Serv Dev ,sqlserv 2005 8x Xeon 3GH, mem 8 GB

For

the Data warehouse I'm using ORACLE 10g R2 64 bit and for the OLAP I'm

using SSAS 2005 64 bit. I'm already installed the oracle client. For

the front-end, I'm using Proclarity.

I have problems with the

performance when querying the OLAP by using ProClarity or SSMS

(Management Studio). The response time is very low, but the CPU

Utilization is below 10% and Memory Usage not more than 50%. When I

check in the performance monitor, the I/O access is very high

continuously 100% utilization when I are running query from ProClarity

Analytics Server. For improving the performance I have applied design

aggregation for each cube. However, design aggregation cannot be made

on several cubes that have a lot of data, and have a lot of measures

and dimension (the number of fields in the table more or less 150

fields). They return 0% optimization level when I run design

aggregation.

FYI, I have done several ways for optimizing the performance such as:
1. Using MOLAP storage mode with 100% design aggregation, except several cubes that can not be made.
2. Cube design follows the best practice, e.g. using key member.
3. Separation of physical storage for data warehouse and OLAP files in different disk.
4. I also used usage-based optimization but it not works.

My

key points are how to improve the performance and fix the aggregation

problem that cannot be made on several cubes. I am looking forward to

hear from you soon.

Thanks in advance.|||Dear All,

I'm having a problem with performance in SSAS 2005. This is the server specification:
OLAP

Server and ORACLE Data Warehouse Server (64 bit) : IBM xSeries 460,

windows 2003 EE x64 SP1, SQL Server Analysis Services 2005 x64 SP1 +

Hotfix, , ORACLE 10g R2, 8x Xeon 3 GHz, memory 8 GB. Data Warehouse is

located on drive D 400GB and OLAP is in drive E 300GB and both of them

are HDD External using SAN (Storage Area Network) RAID 5.
Application Server (32 bit) : 52 IBM xSeries 366 Windows 2003 R2 SP1 BI Serv Dev ,sqlserv 2005 8x Xeon 3GH, mem 8 GB

For

the Data warehouse I'm using ORACLE 10g R2 64 bit and for the OLAP I'm

using SSAS 2005 64 bit. I'm already installed the oracle client. For

the front-end, I'm using Proclarity.

I have problems with the

performance when querying the OLAP by using ProClarity or SSMS

(Management Studio). The response time is very low, but the CPU

Utilization is below 10% and Memory Usage not more than 50%. When I

check in the performance monitor, the I/O access is very high

continuously 100% utilization when I are running query from ProClarity

Analytics Server. For improving the performance I have applied design

aggregation for each cube. However, design aggregation cannot be made

on several cubes that have a lot of data, and have a lot of measures

and dimension (the number of fields in the table more or less 150

fields). They return 0% optimization level when I run design

aggregation.

FYI, I have done several ways for optimizing the performance such as:
1. Using MOLAP storage mode with 100% design aggregation, except several cubes that can not be made.
2. Cube design follows the best practice, e.g. using key member.
3. Separation of physical storage for data warehouse and OLAP files in different disk.
4. I also used usage-based optimization but it not works.

My

key points are how to improve the performance and fix the aggregation

problem that cannot be made on several cubes. I am looking forward to

hear from you soon.

Thanks in advance.|||Dear All,

I'm having a problem with performance in SSAS 2005. This is the server specification:
OLAP

Server and ORACLE Data Warehouse Server (64 bit) : IBM xSeries 460,

windows 2003 EE x64 SP1, SQL Server Analysis Services 2005 x64 SP1 +

Hotfix, , ORACLE 10g R2, 8x Xeon 3 GHz, memory 8 GB. Data Warehouse is

located on drive D 400GB and OLAP is in drive E 300GB and both of them

are HDD External using SAN (Storage Area Network) RAID 5.
Application Server (32 bit) : 52 IBM xSeries 366 Windows 2003 R2 SP1 BI Serv Dev ,sqlserv 2005 8x Xeon 3GH, mem 8 GB

For

the Data warehouse I'm using ORACLE 10g R2 64 bit and for the OLAP I'm

using SSAS 2005 64 bit. I'm already installed the oracle client. For

the front-end, I'm using Proclarity.

I have problems with the

performance when querying the OLAP by using ProClarity or SSMS

(Management Studio). The response time is very low, but the CPU

Utilization is below 10% and Memory Usage not more than 50%. When I

check in the performance monitor, the I/O access is very high

continuously 100% utilization when I are running query from ProClarity

Analytics Server. For improving the performance I have applied design

aggregation for each cube. However, design aggregation cannot be made

on several cubes that have a lot of data, and have a lot of measures

and dimension (the number of fields in the table more or less 150

fields). They return 0% optimization level when I run design

aggregation.

FYI, I have done several ways for optimizing the performance such as:
1. Using MOLAP storage mode with 100% design aggregation, except several cubes that can not be made.
2. Cube design follows the best practice, e.g. using key member.
3. Separation of physical storage for data warehouse and OLAP files in different disk.
4. I also used usage-based optimization but it not works.

My

key points are how to improve the performance and fix the aggregation

problem that cannot be made on several cubes. I am looking forward to

hear from you soon.

Thanks in advance.

Performance Improvement in Analysis Services 2005

Hi,

I am new to Analysis Services and was wondering if some one could
assist me with the issues I am having.

Basically we have one single table on which I need to build a cube so
that managemet can view the data in excel and have good perofrmance. I
started with AS 2000 but ran into the issue of having more than 64,000
members for a dimension level which were not unique.I tried various
suggestions given on the forum but none worked.

So I installed AS 2005 and migrated the database from AS 2000 and build the cube. However I am running into major performance issues, What I have are 2 dimensions with hierarchies Products and Time . The lowest dimesion in Products in something called Instrument Id and it is the 4th level in my hierarchy, the Time dimension has 3 levels, month being the lowest.

I read the articles which described the difference between hierarchies and attribute relationships in AS 2000 and AS 2005. So I defined atributes in my cube design for the above mentioned dimensions. However for the lowest level of detail ( Instument Id across Months ) the query takes for ever. I have the aggregation set at 70%

I know there is an issue with my design but with my limited knowledge of Analysis Serivces I am unble to focus in the right direction. Can someone help me with this

Thanks

For the clues on how to deal with performance problems and the for the better desing ideas take a look at the showcase study; project REAL

http://www.microsoft.com/technet/prodtechnol/sql/2005/projreal.mspx

Particularly in your case I can think of some reasons for slower performance.

For one, the query you are trying to run is going to scan your entire fact and is probably going to bring very big result. Try and make sure you provide a slice for your query. Select only few Instrument Id's not entire level.

Second, try and partition your measure group. Having several partitions would help to speed up your query. This is because Analysis Server can detect which partitions hold the data for the particular Instument Id's you query for and will only scan these partitions.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward,

Thanks for the input, just a couple of quick follow up questions

1. When you say "make sure you provide a slice for your query. Select only few Instrument Id's not entire level" I am not sure how i would go about doing it.

2. As far a partitions I was trying to create partitions for each month of data using the query binding feature. I am not supposed to create a poartition against a dimension. and how would I go about creating partitions across a measure group.

Thanks

|||Dear All,

I'm having a problem with performance in SSAS 2005. This is the server specification:
OLAP

Server and ORACLE Data Warehouse Server (64 bit) : IBM xSeries 460,

windows 2003 EE x64 SP1, SQL Server Analysis Services 2005 x64 SP1 +

Hotfix, , ORACLE 10g R2, 8x Xeon 3 GHz, memory 8 GB. Data Warehouse is

located on drive D 400GB and OLAP is in drive E 300GB and both of them

are HDD External using SAN (Storage Area Network) RAID 5.
Application Server (32 bit) : 52 IBM xSeries 366 Windows 2003 R2 SP1 BI Serv Dev ,sqlserv 2005 8x Xeon 3GH, mem 8 GB

For

the Data warehouse I'm using ORACLE 10g R2 64 bit and for the OLAP I'm

using SSAS 2005 64 bit. I'm already installed the oracle client. For

the front-end, I'm using Proclarity.

I have problems with the

performance when querying the OLAP by using ProClarity or SSMS

(Management Studio). The response time is very low, but the CPU

Utilization is below 10% and Memory Usage not more than 50%. When I

check in the performance monitor, the I/O access is very high

continuously 100% utilization when I are running query from ProClarity

Analytics Server. For improving the performance I have applied design

aggregation for each cube. However, design aggregation cannot be made

on several cubes that have a lot of data, and have a lot of measures

and dimension (the number of fields in the table more or less 150

fields). They return 0% optimization level when I run design

aggregation.

FYI, I have done several ways for optimizing the performance such as:
1. Using MOLAP storage mode with 100% design aggregation, except several cubes that can not be made.
2. Cube design follows the best practice, e.g. using key member.
3. Separation of physical storage for data warehouse and OLAP files in different disk.
4. I also used usage-based optimization but it not works.

My

key points are how to improve the performance and fix the aggregation

problem that cannot be made on several cubes. I am looking forward to

hear from you soon.

Thanks in advance.|||Dear All,

I'm having a problem with performance in SSAS 2005. This is the server specification:
OLAP

Server and ORACLE Data Warehouse Server (64 bit) : IBM xSeries 460,

windows 2003 EE x64 SP1, SQL Server Analysis Services 2005 x64 SP1 +

Hotfix, , ORACLE 10g R2, 8x Xeon 3 GHz, memory 8 GB. Data Warehouse is

located on drive D 400GB and OLAP is in drive E 300GB and both of them

are HDD External using SAN (Storage Area Network) RAID 5.
Application Server (32 bit) : 52 IBM xSeries 366 Windows 2003 R2 SP1 BI Serv Dev ,sqlserv 2005 8x Xeon 3GH, mem 8 GB

For

the Data warehouse I'm using ORACLE 10g R2 64 bit and for the OLAP I'm

using SSAS 2005 64 bit. I'm already installed the oracle client. For

the front-end, I'm using Proclarity.

I have problems with the

performance when querying the OLAP by using ProClarity or SSMS

(Management Studio). The response time is very low, but the CPU

Utilization is below 10% and Memory Usage not more than 50%. When I

check in the performance monitor, the I/O access is very high

continuously 100% utilization when I are running query from ProClarity

Analytics Server. For improving the performance I have applied design

aggregation for each cube. However, design aggregation cannot be made

on several cubes that have a lot of data, and have a lot of measures

and dimension (the number of fields in the table more or less 150

fields). They return 0% optimization level when I run design

aggregation.

FYI, I have done several ways for optimizing the performance such as:
1. Using MOLAP storage mode with 100% design aggregation, except several cubes that can not be made.
2. Cube design follows the best practice, e.g. using key member.
3. Separation of physical storage for data warehouse and OLAP files in different disk.
4. I also used usage-based optimization but it not works.

My

key points are how to improve the performance and fix the aggregation

problem that cannot be made on several cubes. I am looking forward to

hear from you soon.

Thanks in advance.|||Dear All,

I'm having a problem with performance in SSAS 2005. This is the server specification:
OLAP

Server and ORACLE Data Warehouse Server (64 bit) : IBM xSeries 460,

windows 2003 EE x64 SP1, SQL Server Analysis Services 2005 x64 SP1 +

Hotfix, , ORACLE 10g R2, 8x Xeon 3 GHz, memory 8 GB. Data Warehouse is

located on drive D 400GB and OLAP is in drive E 300GB and both of them

are HDD External using SAN (Storage Area Network) RAID 5.
Application Server (32 bit) : 52 IBM xSeries 366 Windows 2003 R2 SP1 BI Serv Dev ,sqlserv 2005 8x Xeon 3GH, mem 8 GB

For

the Data warehouse I'm using ORACLE 10g R2 64 bit and for the OLAP I'm

using SSAS 2005 64 bit. I'm already installed the oracle client. For

the front-end, I'm using Proclarity.

I have problems with the

performance when querying the OLAP by using ProClarity or SSMS

(Management Studio). The response time is very low, but the CPU

Utilization is below 10% and Memory Usage not more than 50%. When I

check in the performance monitor, the I/O access is very high

continuously 100% utilization when I are running query from ProClarity

Analytics Server. For improving the performance I have applied design

aggregation for each cube. However, design aggregation cannot be made

on several cubes that have a lot of data, and have a lot of measures

and dimension (the number of fields in the table more or less 150

fields). They return 0% optimization level when I run design

aggregation.

FYI, I have done several ways for optimizing the performance such as:
1. Using MOLAP storage mode with 100% design aggregation, except several cubes that can not be made.
2. Cube design follows the best practice, e.g. using key member.
3. Separation of physical storage for data warehouse and OLAP files in different disk.
4. I also used usage-based optimization but it not works.

My

key points are how to improve the performance and fix the aggregation

problem that cannot be made on several cubes. I am looking forward to

hear from you soon.

Thanks in advance.|||Dear All,

I'm having a problem with performance in SSAS 2005. This is the server specification:
OLAP

Server and ORACLE Data Warehouse Server (64 bit) : IBM xSeries 460,

windows 2003 EE x64 SP1, SQL Server Analysis Services 2005 x64 SP1 +

Hotfix, , ORACLE 10g R2, 8x Xeon 3 GHz, memory 8 GB. Data Warehouse is

located on drive D 400GB and OLAP is in drive E 300GB and both of them

are HDD External using SAN (Storage Area Network) RAID 5.
Application Server (32 bit) : 52 IBM xSeries 366 Windows 2003 R2 SP1 BI Serv Dev ,sqlserv 2005 8x Xeon 3GH, mem 8 GB

For

the Data warehouse I'm using ORACLE 10g R2 64 bit and for the OLAP I'm

using SSAS 2005 64 bit. I'm already installed the oracle client. For

the front-end, I'm using Proclarity.

I have problems with the

performance when querying the OLAP by using ProClarity or SSMS

(Management Studio). The response time is very low, but the CPU

Utilization is below 10% and Memory Usage not more than 50%. When I

check in the performance monitor, the I/O access is very high

continuously 100% utilization when I are running query from ProClarity

Analytics Server. For improving the performance I have applied design

aggregation for each cube. However, design aggregation cannot be made

on several cubes that have a lot of data, and have a lot of measures

and dimension (the number of fields in the table more or less 150

fields). They return 0% optimization level when I run design

aggregation.

FYI, I have done several ways for optimizing the performance such as:
1. Using MOLAP storage mode with 100% design aggregation, except several cubes that can not be made.
2. Cube design follows the best practice, e.g. using key member.
3. Separation of physical storage for data warehouse and OLAP files in different disk.
4. I also used usage-based optimization but it not works.

My

key points are how to improve the performance and fix the aggregation

problem that cannot be made on several cubes. I am looking forward to

hear from you soon.

Thanks in advance.|||Dear All,

I'm having a problem with performance in SSAS 2005. This is the server specification:
OLAP

Server and ORACLE Data Warehouse Server (64 bit) : IBM xSeries 460,

windows 2003 EE x64 SP1, SQL Server Analysis Services 2005 x64 SP1 +

Hotfix, , ORACLE 10g R2, 8x Xeon 3 GHz, memory 8 GB. Data Warehouse is

located on drive D 400GB and OLAP is in drive E 300GB and both of them

are HDD External using SAN (Storage Area Network) RAID 5.
Application Server (32 bit) : 52 IBM xSeries 366 Windows 2003 R2 SP1 BI Serv Dev ,sqlserv 2005 8x Xeon 3GH, mem 8 GB

For

the Data warehouse I'm using ORACLE 10g R2 64 bit and for the OLAP I'm

using SSAS 2005 64 bit. I'm already installed the oracle client. For

the front-end, I'm using Proclarity.

I have problems with the

performance when querying the OLAP by using ProClarity or SSMS

(Management Studio). The response time is very low, but the CPU

Utilization is below 10% and Memory Usage not more than 50%. When I

check in the performance monitor, the I/O access is very high

continuously 100% utilization when I are running query from ProClarity

Analytics Server. For improving the performance I have applied design

aggregation for each cube. However, design aggregation cannot be made

on several cubes that have a lot of data, and have a lot of measures

and dimension (the number of fields in the table more or less 150

fields). They return 0% optimization level when I run design

aggregation.

FYI, I have done several ways for optimizing the performance such as:
1. Using MOLAP storage mode with 100% design aggregation, except several cubes that can not be made.
2. Cube design follows the best practice, e.g. using key member.
3. Separation of physical storage for data warehouse and OLAP files in different disk.
4. I also used usage-based optimization but it not works.

My

key points are how to improve the performance and fix the aggregation

problem that cannot be made on several cubes. I am looking forward to

hear from you soon.

Thanks in advance.

Performance degradation in December CTP of AS 2005 SP2

Has anyone had any similar experience with the December CTP of SQL Server 2005 and Analysis Services SP2 ?

We have a simple MDX query which returns 245 rows and 15 columns of data. When running this query through management studio under SP1 it takes 1 second, and when run through an XMLA query it takes 2 seconds.

We have now installed the december CTP of SP2 on an identical server and re-deployed / re-processed the identical cubes to this server. When running this first test query it takes consistently 6 seconds and 20 seconds when run through an XMLA query.

Does anyone have any hints on what could be different in the new CTP that could be behind this ? One thing that I have noticed is that running the MDX as an XMLA query produces tens of thousands of lines of messages under SP2 as opposed to the "Executing the query ... Execution complere" messages under SP1. Is there some server parameter that governs the trace / debug information created and could this be behind the performance degradation we are experiencing ?

Any ideas would be very gratefully recieved

It is very hard to tell anything sure about this.

A wild guess based on what Mosha has written in his Blog is that you have calculated members with a non-correct setting for non-empty behavior.

Edit: Have a look here: http://www.sqljunkies.com/WebLog/mosha/archive/2006/11/05/non_empty_behavior.aspx

Mosha have mentioned this several times but there are no clear guidelines for how to set this correctly.

If you do not have non_empty_behavior set for calculated members/measures or scripts, add it and see if it helps.

If you have non_empty_behavior set for a calculated members/measures or script, remove it and see if it helps.

Regards

Thomas Ivarsson

|||Can you share the query as well as related calculations?|||

Thanks for the replies and sorry for the change in username ... The problem was originally posted under a colleagues ID beforeI created my own.

The MDX Query pasted at the bottom of this message.

The query includes a calculation that I will try to move onto the cube so that I can benefit from the advice on non-empty behaviour and see what impact this has. However, the main concern is still the trying to understand the considerably worse performance under SP2.

Thanks again for any help.

WITH MEMBER [Time Intelligence].[Time Intelligence].[Used Methodology] AS

IIF ( NOT ISEMPTY(( [MethodologyCollection].[Dim Pre Post].&[2] ,[Time Intelligence].[Time Intelligence].&[1])) OR NOT ISEMPTY(( [MethodologyCollection].[Dim Pre Post].&[3] ,[Time Intelligence].[Time Intelligence].&[1])) , 2

, IIF ( ISEMPTY(( [MethodologyCollection].[Dim Pre Post].&[1] ,[Time Intelligence].[Time Intelligence].&[1])) , 3 , 1 ) )

SELECT

{ ([Time Intelligence].[Time Intelligence].&[1],[Display Instruments CC2])

, ([Time Intelligence].[Time Intelligence].[Used Methodology],[Display Instruments CC2])

} ON COLUMNS ,

[Display Countries] ON ROWS

FROM [IBLR Full IFS]

WHERE (

[Measures].[AmountsOutstanding cc2 display]

, [IBLR Currency].[Market].[Currency Total BK].&[1]

, [Quarter].[Year-Quarter].&[2006].&[20060930]

, [Rep Org].[Reporting Organisation].[DK]

, [MethodologyCollection].[Dim Pre Post].[Current Period Methodology]

, [IBLR Data Type].[Data Type BK].)

|||Can you try the query with connection string property "Cache Policy=9" to see if there is any difference in performance? Management Studio doesn't allow setting connection string property, you have to use MDX Sample from SQL Server 2000 or write your own C# program.|||

Thank you very much for the reply. I have implemented this as you suggested and tested the queries against SP1 and SP2 and the performance is now comparable (even slightly quicker under SP2, although drawing any conclusions from a single query taking 1.1 or 1.2 seconds is probably a little premature :-) ).

I have done a quick search on the online help and through google and have not been able to find any information on this setting, would you be able to provide a brief description of what this property ?

Thanks again for your help.

|||

In addition, I have found that inserting the following into the proprty list of the XMLA query has meant that the time running the XMLA version of the query through management studio has improved from 20 seconds to 1 second.

<DbpropMsmdCachePolicy>9</DbpropMsmdCachePolicy>

The online help (also the version of the help released with the December CTP) for this XMLA property indicates that the property is "Reserved for future use".

|||

This connection string property changes the default execution plan picked by AS in certain cases, for example when there is IIF in the calculation. AS picks the default execution plan as it is the best for most cases. Unfortunately in some cases, such as in your case, the execution plan picked by AS performs far worse than the alternative.

The default setting works best in most cases, therefore, users should only use "Cache Policy=9" when it has demonstrated significant performance boost for a given query. But setting "Cache Policy=9" is likely to cause performance degradation for many other queries. Since It is difficult to change server properties only for certain queries and it is risky to change the default setting, this server property is not widely publicized.

Ideally you should only turn this setting on for the problematic queries. If it is hard to do you have to verify that other queries are not adversely affected by the new setting. This is an area which is likely to see significant improvements in future versions of AS.

Performance counter on Report.

Our company used Reporting Services for a certain months.

One day, my administrator ask me if he can know each report's processing status.

For example, the average processing time of report name "Order Report".

Can the performance counters of reporting services do that?

Thanks alot!

As Benni said, using the DTS package we provide to export the contents of the ExecutionLog table is the best way to go (it is not a best practice to query this table directly, though)

There are no perfmon counters that you can use that will allow you to say "Specifically, give me information about report 'XZY'". However, there are some undocumented counters that will give you some of the same information as the execution log about the last run report. You can find info about them here:

http://blogs.msdn.com/bimusings/archive/2005/07/26/443511.aspx

|||

I haven't found anything about that in the api, but there is a nice table in ReportServer-Database.

Take a look at dbo.ExecutionLog (and for getting the reportnames dbo.Catalog). dbo.ExecutionLog has some TimeXYZ columns wich give you the time in millis..

Percentage/Proportion

I'm new to Analysis Services 2005 and MDX. I'm trying to create a calculated member that contains the proportion of cases (using [Measures].) for each dimension in the cube. The following code gives me the percentage of the total number of members in each dimension. This is what I need for attribute hiearchies (single-level), but for user-defined hiearchies (multi-level) I want the percentage of the above level. For example, in a time dimension, I want the proportion of cases in each year (this works) and the proportion of cases in each quarter of a given year (this doesn't work).

CALCULATE;

CREATEMEMBERCURRENTCUBE.[MEASURES].PercentCases

ASCASE

WHENIsEmpty([Measures].) THENNULL

ELSE ((

[DimLocation].[DimLocation].CurrentMember,

[DimLevel].[DimLevel].CurrentMember,

[DimDischargeYear].[DimDischargeYear].CurrentMember,

[DimDiagnosis].[DimDiagnosis].CurrentMember,

[DimICUAdmSource].[DimICUAdmSource].CurrentMember,

[DimSeverity].[DimSeverity].CurrentMember,

[DimGlucose].[DimGlucose].CurrentMember,

[DimMeanGlucose].[DimMeanGlucose].CurrentMember,

[DimAlbumin].[DimAlbumin].CurrentMember,

[DimAdmissions].[DimAdmissions].CurrentMember,

[DimDischargeHour].[DimDischargeHour].CurrentMember,

[DimUnitType].[DimUnitType].CurrentMember,[Measures].) /

(Root([DimLocation]),Root([DimLevel]),Root([DimDischargeYear]),Root([DimDiagnosis]),Root([DimICUAdmSource]),Root([DimSeverity]),Root([DimGlucose]),Root([DimMeanGlucose]),Root([DimAlbumin]),Root([DimAdmissions]),Root([DimDischargeHour]),Root([DimUnitType]),[Measures].))

END,

FORMAT_STRING = "#,0.0%",

VISIBLE = 1 ;

In addition I want the user to be able to get cross tabulations among the dimensions. For example, they may want the proportion of cases in each year at a given location or the proportion of cases in each year by product? In this case, I'd need a generalized formula because I'll never be sure which dimension(s) they may cross-tab.

Can anyone help? Any suggestions would be greatly appreciated.

SAP2006

Hi. To get percentage of the above level use the MDX Parent function in the denominator of your calculation. <dim>.<hier>.CurrentMember.Parent. However, there is a hazard using the .Parent function when CurrentMember is the highest level in the hierarchy. When the CurrentMember is the highest level in the hierarchy you reference a space outside the cube which is null, or 0, and you get a divide by zero error. We can avoid divide by 0 error, but it adds a lot of extra checking to our calculated member.

There are two sets of checks in the MDX below which avoid the divide by zero error.

The first set of checks execute before the calculation is done. Just as your ISEMPTY() check on the measure executes. First we check if the <dim>.<hier>.Currentmember.Level.Ordinal is <> 0. Checking the Level.Ordinal property identifies if we're at the highest level in the hierarchy. If we're not at the highest level in the hierarchy (.ordinal <> 0), then we check the tuple (<dim.<hier>.CurrentMember.Parent, Measures.<desired measure>) for the EMPTY condition.

IIF([Product].[Product Categories].CurrentMember.Level.Ordinal <> 0 AND

ISEMPTY(([Product].[Product Categories].CurrentMember.Parent, Measures.[Customer Count]))

If both conditions are met we return NULL and avoid the divide by zero error.

The second set of checks occurs in the tuple which defines the denominator of our division. Each <dim>.<hier>.CurrentMember is checked to see if we're at the highest level in the hierarchy:

IIF([Product].[Product Categories].CurrentMember.Level.Ordinal = 0

If we are at the highest level then we use the DefaultMember value in the hierarchy. If not at the highest level then we use <dim>.<hier>.CurrentMember.Parent in the denominator calculation.

A major drawback to this technique is the detailed checking of each <dim>.<hier> within the nested IIF(). When you have a lot of hierarchies to include the construction/maintenance if the nested IIF() is tedious. However, if you're diligent you can accomplish it.

The example below uses a subset of hierarchies form the Adventure Works DW sample database. I didn't reference EVERY hierarchy in adventure works because it would have been very long and tedious. So my percentage calculations are ONLY valid across the <dim>.<hier> I reference in the calculated member Measures.[Percent Customer Count]

WITH MEMBER Measures.[Percent Customer Count] AS

'

IIF(ISEMPTY([Measures].[Customer Count]), NULL,

IIF([Product].[Product Categories].CurrentMember.Level.Ordinal <> 0 AND

ISEMPTY(([Product].[Product Categories].CurrentMember.Parent, Measures.[Customer Count])), NULL,

IIF([Date].[Calendar].CurrentMember.Level.Ordinal <> 0 AND

ISEMPTY(([Date].[Calendar].CurrentMember.Parent, Measures.[Customer Count])), NULL,

IIF([Customer].[Customer Geography].CurrentMember.Level.Ordinal <> 0 AND

ISEMPTY(([Customer].[Customer Geography].CurrentMember.Parent, Measures.[Customer Count])), NULL,

// Do the calculation

([Product].[Product Categories].CurrentMember

,[Date].[Calendar].CurrentMember

,[Customer].[Customer Geography].CurrentMember

,[Measures].[Customer Count])

/

(IIF([Product].[Product Categories].CurrentMember.Level.Ordinal = 0, [Product].[Product Categories].DefaultMember, [Product].[Product Categories].CurrentMember.Parent)

,IIF([Date].[Calendar].CurrentMember.Level.Ordinal = 0, [Date].[Calendar].DefaultMember, [Date].[Calendar].CurrentMember.Parent)

,IIF([Customer].[Customer Geography].CurrentMember.Level.Ordinal = 0, [Customer].[Customer Geography].DefaultMember, [Customer].[Customer Geography].CurrentMember.Parent)

,[Measures].[Customer Count])

)

)

)

)

' ,SOLVE_ORDER=10, FORMAT_STRING="#,0.0%"

SELECT

{[Measures].[Customer Count], Measures.[Percent Customer Count]} ONCOLUMNS

,{[Product].[Product Categories].[All]

,DESCENDANTS([Product].[Product Categories].[All], [Product].[Product Categories].[Category])} ONROWS

FROM [Adventure Works]

WHERE(

[Date].[Calendar].[Calendar Year].&[2003]

,[Customer].[Customer Geography].[Country].&[United States]

)

My code copy into this window didn't carry over very well. If you'd like the .MDX file witht he query then drop me a note at paul_goldy@.hotmail.com

Good Luck.

PGoldy

|||

PGoldy,

Thank you...that worked!!! I do have a few questions though.

1) My understanding was that the solve order was no longer necessary in AS2005 because it relies on the order of the MDX script. Therefore, I did not include the solve order=10 because I wasn't sure if 10 was the correct number given the other calculations in the cube. Is it OK to eliminate it?

2) I'm not sure how to ask this question, so please bear with me if it doesn't make sense. Since the calculated percent is for the current dimension the end user is browsing by.... what happens if the first clause of the IIF in the first dimension is true? Does it return NULL and then not move on to the other dimensions? For example, if the following conditions are both met

[Product].[Product Categories].CurrentMember.Level.Ordinal <> 0 AND

ISEMPTY(([Product].[Product Categories].CurrentMember.Parent, Measures.[Customer Count]))

will it return a NULL value for the % when viewing the Product dimension and then STOP or will it continue to evaluate all the other conditions/dimensions as well.

Thanks again.

SAP2006

|||

Hi. Glad to be of help, and thanks for the questions.

(1) You are correct about the SOLVE_ORDER, it is not required and you can eliminate it. I'm afraid I used it out of habit.

(2) Your second question makes sense. Yes, if one of the initial IIF() conditions are met ( .Oridinal <> 0 AND ISEMPTY()) then the expression returns NULL as the result. I'm afraid this is required because if ANY of the denominator values are empty then you get a divide by 0 error - even though the user may only "see" a specific dimension they are drilling up/down on. Sory if that's bad news, but that's how the calculations work.

Good Luck.

PGoldy

|||

I think we will be OK with the nested IIFs. I just wanted to make sure that I understood what the code was doing. Thanks again for all your help.

SAP2006

|||

Hi PGoldy,

The code you provided previously works perfectly if we are only viewing one dimension at a time (column percentages). However, when I do a cross-tab of two or more dimensions, the code is giving me column percentages and what I really need are row percentages. I know I need to change the denominator, but I'm unsure how to write the MDX code given that the denominator could be any dimension in the cube and therefore will be different depending on which dimension the user is browsing. Is there a way to do this?

[DimLocation].[DimLocation].CurrentMember,[Measures].

/

?

where ? could be any of the following:

[DimDischargeYear].[DimDischargeYear].CurrentMember, [Measures].

[DimAdmissions].[DimAdmissions].CurrentMember, [Measures].

.........

[DimLevel].[DimLevel].CurrentMember, [Measures].

Any help would be appreciated.

Thanks, SAP2006

|||

Sorry, but I don't understand why the percentage calculation only shows corretly on columns. The calculation is not specific to rows or columns, but only adheres to the relative position of members in a hierarchy (children, parents). Perhaps you could provide a complete MDX query which shows the problem?

Also, I'm a little confused what you mean by a "...cross tab of two or more dimensions...". Do you mean you have nested dimensions on the rows and columns, or do you use the PAGE Axis and have multiple tabs of data?

PGoldy

|||

Sorry, I didn't explain myself better. You are correct. Currently, BIDS is calculating a percentage relative to the position of members of in the hierarchy (see table 1), for example, 3849/23343=16.5%. What I would like is a percentage of the row (see table 2), for example 3849/7440=51.7%.

By cross-tab, I simply meant a query using two or more dimensions. For example in the query below, I have Location on rows and Discharge Year on columns. There may be instances where I have nested dimensions as well, for example, Location and Admissions on rows and Discharge Year on columns. I hope this clarifies things.

TABLE 1 Discharge Year Location 2004 2005 Grand Total N PercentCases N PercentCases N PercentCases A 3849 16.5% 3591 15.4% 7440 31.9% B 2887 12.4% 2841 12.2% 5728 24.5% C 5103 21.9% 5072 21.7% 10175 43.6% Grand Total 11839 50.7% 11504 49.3% 23343 100.0% TABLE 2 Discharge Year Location 2004 2005 Grand Total N PercentCases N PercentCases N PercentCases A 3849 51.70% 3591 48.30% 7440 31.9% B 2887 50.40% 2841 49.60% 5728 24.5% C 5103 50.20% 5072 49.80% 10175 43.6% Grand Total 11839 50.7% 11504 49.3% 23343 100.0%

Thanks, SAP2006

|||

Hi, and thanks for the clarification. I understand what you're trying to do. If we define what you're looking for...you'd like to get the percentage contribution for unrelated members. In other words, you want to know how much Location A is contributing to Total Years (3849/7440 = 51.7%). This is a tough problem which is usually solved by a client side tool which takes apart the query result cell set and provides the % calcs as a client side activity without using MDX. Tools like Analyzer2005, and ProClarity demonstrate this capability well. I'm sure there are other tools that do as well, but I'm not as familiar with them.

It may be possible to do what you'd like as an MDX calculation, but it involves retrieving the current tuples defined on the axes (rows or columns). A function which allows retrieval of tuples from the axes is the MDX AXIS() function. I haven't taken a look at the AXIS() function since AS2005 came out because it only worked in limited cases in AS2000. You can find more information on the AXIS() function here:

http://msdn2.microsoft.com/en-us/library/ms145531.aspx

I'll also spend some time tinkering with it to see if we can retrieve current tuples on the axes and post any results here, but don't hold your breath. This is a tough problem usually solved by client side applications operating on the resulting cell-set.

PGoldy

|||

PGoldy,

We are using the ProClarity client tool and although it calculates and displays column, row and grid percentages, it graphs the values (n) rather than the percentages. Therefore, I was trying to create the percentages in SSAS. I reviewed the documentation you posted and included AXIS(1) in the denominator. The following messge was displayed when I put the cursor on the #VALUE result in the cell:

"The function expects a string or numeric expression for the argument. A tuple set expression was used."

The MDX code I processed was

IIF(IsEmpty([Measures].),NULL,

([DimLocation].[DimLocation].CurrentMember,[Measures].) /

(AXIS(1),[Measures].))

Any ideas if this can be changed? If you don't have time to look at it, I understand. I appreciate all the help you've given.

Thanks, SAP2006

|||

PGoldy,

We are using the ProClarity client tool and although it calculates and displays column, row and grid percentages, it graphs the values (n) rather than the percentages. Therefore, I was trying to create the percentages in SSAS. I reviewed the documentation you posted and included AXIS(1) in the denominator. The following messge was displayed when I put the cursor on the #VALUE result in the cell:

"The function expects a string or numeric expression for the argument. A tuple set expression was used."

The MDX code I processed was

IIF(IsEmpty([Measures].),NULL,

([DimLocation].[DimLocation].CurrentMember,[Measures].) /

(AXIS(1),[Measures].))

Any ideas if this can be changed? If you don't have time to look at it, I understand. I appreciate all the help you've given.

Thanks, SAP2006

|||

Hello,

I am new to MDX and I have a similar situation as discussed above i.e. finding % contribution to totals. I am using AS2005. Any idea of how this can be done?

Thanks,

Kulathu Sarma

|||

Hi,

Unfortunately, we haven't figured it out yet. However, in January, we have a consultant coming onsite to help with various issues. I'll post the solution if we find it.

SAP

|||

SAP,

It looks like you're working on a health-related application. We've also just started using SSAS2005 for cancer services data and are currently grappling with the same problem - creating row and column percentage measures that are not tied to specific dimensions. I've tried the Axis function without success; it returns a set that's the same as the current member or cell, so the percentage measure based on it is always 100%. This is probably not how it's supposed to work. Would be grateful for any advice in this regard...

QCCAT

|||

QCCAT,

The following MDX returns row percentages. Good Luck!!

//Begin - Percent Cases

CREATEMEMBERCURRENTCUBE.MEASURES.[Percent Cases]

AS

IIF(Axis(0)(0).Count < 2,

(Axis(0)(0).Dimension.CurrentMember,Measures.N)

/

Sum

(Filter(Axis(0),

InStr(Axis(0)(0).Dimension.CurrentMember.Name,"Total") = 0),

Measures.N),

NULL),

FORMAT_STRING = "0.0%",

VISIBLE = 1;

//End - Percent Cases

Percentage/Proportion

I'm new to Analysis Services 2005 and MDX. I'm trying to create a calculated member that contains the proportion of cases (using [Measures].) for each dimension in the cube. The following code gives me the percentage of the total number of members in each dimension. This is what I need for attribute hiearchies (single-level), but for user-defined hiearchies (multi-level) I want the percentage of the above level. For example, in a time dimension, I want the proportion of cases in each year (this works) and the proportion of cases in each quarter of a given year (this doesn't work).

CALCULATE;

CREATE MEMBER CURRENTCUBE.[MEASURES].PercentCases

AS CASE

WHEN IsEmpty([Measures].) THEN NULL

ELSE ((

[DimLocation].[DimLocation].CurrentMember,

[DimLevel].[DimLevel].CurrentMember,

[DimDischargeYear].[DimDischargeYear].CurrentMember,

[DimDiagnosis].[DimDiagnosis].CurrentMember,

[DimICUAdmSource].[DimICUAdmSource].CurrentMember,

[DimSeverity].[DimSeverity].CurrentMember,

[DimGlucose].[DimGlucose].CurrentMember,

[DimMeanGlucose].[DimMeanGlucose].CurrentMember,

[DimAlbumin].[DimAlbumin].CurrentMember,

[DimAdmissions].[DimAdmissions].CurrentMember,

[DimDischargeHour].[DimDischargeHour].CurrentMember,

[DimUnitType].[DimUnitType].CurrentMember,[Measures].) /

(Root([DimLocation]),Root([DimLevel]),Root([DimDischargeYear]),Root([DimDiagnosis]),Root([DimICUAdmSource]),Root([DimSeverity]),Root([DimGlucose]),Root([DimMeanGlucose]),Root([DimAlbumin]),Root([DimAdmissions]),Root([DimDischargeHour]),Root([DimUnitType]),[Measures].))

END,

FORMAT_STRING = "#,0.0%",

VISIBLE = 1 ;

In addition I want the user to be able to get cross tabulations among the dimensions. For example, they may want the proportion of cases in each year at a given location or the proportion of cases in each year by product? In this case, I'd need a generalized formula because I'll never be sure which dimension(s) they may cross-tab.

Can anyone help? Any suggestions would be greatly appreciated.

SAP2006

Hi. To get percentage of the above level use the MDX Parent function in the denominator of your calculation. <dim>.<hier>.CurrentMember.Parent. However, there is a hazard using the .Parent function when CurrentMember is the highest level in the hierarchy. When the CurrentMember is the highest level in the hierarchy you reference a space outside the cube which is null, or 0, and you get a divide by zero error. We can avoid divide by 0 error, but it adds a lot of extra checking to our calculated member.

There are two sets of checks in the MDX below which avoid the divide by zero error.

The first set of checks execute before the calculation is done. Just as your ISEMPTY() check on the measure executes. First we check if the <dim>.<hier>.Currentmember.Level.Ordinal is <> 0. Checking the Level.Ordinal property identifies if we're at the highest level in the hierarchy. If we're not at the highest level in the hierarchy (.ordinal <> 0), then we check the tuple (<dim.<hier>.CurrentMember.Parent, Measures.<desired measure>) for the EMPTY condition.

IIF([Product].[Product Categories].CurrentMember.Level.Ordinal <> 0 AND

ISEMPTY(([Product].[Product Categories].CurrentMember.Parent, Measures.[Customer Count]))

If both conditions are met we return NULL and avoid the divide by zero error.

The second set of checks occurs in the tuple which defines the denominator of our division. Each <dim>.<hier>.CurrentMember is checked to see if we're at the highest level in the hierarchy:

IIF([Product].[Product Categories].CurrentMember.Level.Ordinal = 0

If we are at the highest level then we use the DefaultMember value in the hierarchy. If not at the highest level then we use <dim>.<hier>.CurrentMember.Parent in the denominator calculation.

A major drawback to this technique is the detailed checking of each <dim>.<hier> within the nested IIF(). When you have a lot of hierarchies to include the construction/maintenance if the nested IIF() is tedious. However, if you're diligent you can accomplish it.

The example below uses a subset of hierarchies form the Adventure Works DW sample database. I didn't reference EVERY hierarchy in adventure works because it would have been very long and tedious. So my percentage calculations are ONLY valid across the <dim>.<hier> I reference in the calculated member Measures.[Percent Customer Count]

WITH MEMBER Measures.[Percent Customer Count] AS

'

IIF(ISEMPTY([Measures].[Customer Count]), NULL,

IIF([Product].[Product Categories].CurrentMember.Level.Ordinal <> 0 AND

ISEMPTY(([Product].[Product Categories].CurrentMember.Parent, Measures.[Customer Count])), NULL,

IIF([Date].[Calendar].CurrentMember.Level.Ordinal <> 0 AND

ISEMPTY(([Date].[Calendar].CurrentMember.Parent, Measures.[Customer Count])), NULL,

IIF([Customer].[Customer Geography].CurrentMember.Level.Ordinal <> 0 AND

ISEMPTY(([Customer].[Customer Geography].CurrentMember.Parent, Measures.[Customer Count])), NULL,

// Do the calculation

([Product].[Product Categories].CurrentMember

,[Date].[Calendar].CurrentMember

,[Customer].[Customer Geography].CurrentMember

,[Measures].[Customer Count])

/

(IIF([Product].[Product Categories].CurrentMember.Level.Ordinal = 0, [Product].[Product Categories].DefaultMember, [Product].[Product Categories].CurrentMember.Parent)

,IIF([Date].[Calendar].CurrentMember.Level.Ordinal = 0, [Date].[Calendar].DefaultMember, [Date].[Calendar].CurrentMember.Parent)

,IIF([Customer].[Customer Geography].CurrentMember.Level.Ordinal = 0, [Customer].[Customer Geography].DefaultMember, [Customer].[Customer Geography].CurrentMember.Parent)

,[Measures].[Customer Count])

)

)

)

)

' ,SOLVE_ORDER=10, FORMAT_STRING="#,0.0%"

SELECT

{[Measures].[Customer Count], Measures.[Percent Customer Count]} ON COLUMNS

,{[Product].[Product Categories].[All]

,DESCENDANTS([Product].[Product Categories].[All], [Product].[Product Categories].[Category])} ON ROWS

FROM [Adventure Works]

WHERE(

[Date].[Calendar].[Calendar Year].&[2003]

,[Customer].[Customer Geography].[Country].&[United States]

)

My code copy into this window didn't carry over very well. If you'd like the .MDX file witht he query then drop me a note at paul_goldy@.hotmail.com

Good Luck.

PGoldy

|||

PGoldy,

Thank you...that worked!!! I do have a few questions though.

1) My understanding was that the solve order was no longer necessary in AS2005 because it relies on the order of the MDX script. Therefore, I did not include the solve order=10 because I wasn't sure if 10 was the correct number given the other calculations in the cube. Is it OK to eliminate it?

2) I'm not sure how to ask this question, so please bear with me if it doesn't make sense. Since the calculated percent is for the current dimension the end user is browsing by.... what happens if the first clause of the IIF in the first dimension is true? Does it return NULL and then not move on to the other dimensions? For example, if the following conditions are both met

[Product].[Product Categories].CurrentMember.Level.Ordinal <> 0 AND

ISEMPTY(([Product].[Product Categories].CurrentMember.Parent, Measures.[Customer Count]))

will it return a NULL value for the % when viewing the Product dimension and then STOP or will it continue to evaluate all the other conditions/dimensions as well.

Thanks again.

SAP2006

|||

Hi. Glad to be of help, and thanks for the questions.

(1) You are correct about the SOLVE_ORDER, it is not required and you can eliminate it. I'm afraid I used it out of habit.

(2) Your second question makes sense. Yes, if one of the initial IIF() conditions are met ( .Oridinal <> 0 AND ISEMPTY()) then the expression returns NULL as the result. I'm afraid this is required because if ANY of the denominator values are empty then you get a divide by 0 error - even though the user may only "see" a specific dimension they are drilling up/down on. Sory if that's bad news, but that's how the calculations work.

Good Luck.

PGoldy

|||

I think we will be OK with the nested IIFs. I just wanted to make sure that I understood what the code was doing. Thanks again for all your help.

SAP2006

|||

Hi PGoldy,

The code you provided previously works perfectly if we are only viewing one dimension at a time (column percentages). However, when I do a cross-tab of two or more dimensions, the code is giving me column percentages and what I really need are row percentages. I know I need to change the denominator, but I'm unsure how to write the MDX code given that the denominator could be any dimension in the cube and therefore will be different depending on which dimension the user is browsing. Is there a way to do this?

[DimLocation].[DimLocation].CurrentMember,[Measures].

/

?

where ? could be any of the following:

[DimDischargeYear].[DimDischargeYear].CurrentMember, [Measures].

[DimAdmissions].[DimAdmissions].CurrentMember, [Measures].

.........

[DimLevel].[DimLevel].CurrentMember, [Measures].

Any help would be appreciated.

Thanks, SAP2006

|||

Sorry, but I don't understand why the percentage calculation only shows corretly on columns. The calculation is not specific to rows or columns, but only adheres to the relative position of members in a hierarchy (children, parents). Perhaps you could provide a complete MDX query which shows the problem?

Also, I'm a little confused what you mean by a "...cross tab of two or more dimensions...". Do you mean you have nested dimensions on the rows and columns, or do you use the PAGE Axis and have multiple tabs of data?

PGoldy

|||

Sorry, I didn't explain myself better. You are correct. Currently, BIDS is calculating a percentage relative to the position of members of in the hierarchy (see table 1), for example, 3849/23343=16.5%. What I would like is a percentage of the row (see table 2), for example 3849/7440=51.7%.

By cross-tab, I simply meant a query using two or more dimensions. For example in the query below, I have Location on rows and Discharge Year on columns. There may be instances where I have nested dimensions as well, for example, Location and Admissions on rows and Discharge Year on columns. I hope this clarifies things.

TABLE 1

Discharge Year

Location

2004

2005

Grand Total

N

PercentCases

N

PercentCases

N

PercentCases

A

3849

16.5%

3591

15.4%

7440

31.9%

B

2887

12.4%

2841

12.2%

5728

24.5%

C

5103

21.9%

5072

21.7%

10175

43.6%

Grand Total

11839

50.7%

11504

49.3%

23343

100.0%

TABLE 2

Discharge Year

Location

2004

2005

Grand Total

N

PercentCases

N

PercentCases

N

PercentCases

A

3849

51.70%

3591

48.30%

7440

31.9%

B

2887

50.40%

2841

49.60%

5728

24.5%

C

5103

50.20%

5072

49.80%

10175

43.6%

Grand Total

11839

50.7%

11504

49.3%

23343

100.0%

Thanks, SAP2006

|||

Hi, and thanks for the clarification. I understand what you're trying to do. If we define what you're looking for...you'd like to get the percentage contribution for unrelated members. In other words, you want to know how much Location A is contributing to Total Years (3849/7440 = 51.7%). This is a tough problem which is usually solved by a client side tool which takes apart the query result cell set and provides the % calcs as a client side activity without using MDX. Tools like Analyzer2005, and ProClarity demonstrate this capability well. I'm sure there are other tools that do as well, but I'm not as familiar with them.

It may be possible to do what you'd like as an MDX calculation, but it involves retrieving the current tuples defined on the axes (rows or columns). A function which allows retrieval of tuples from the axes is the MDX AXIS() function. I haven't taken a look at the AXIS() function since AS2005 came out because it only worked in limited cases in AS2000. You can find more information on the AXIS() function here:

http://msdn2.microsoft.com/en-us/library/ms145531.aspx

I'll also spend some time tinkering with it to see if we can retrieve current tuples on the axes and post any results here, but don't hold your breath. This is a tough problem usually solved by client side applications operating on the resulting cell-set.

PGoldy

|||

PGoldy,

We are using the ProClarity client tool and although it calculates and displays column, row and grid percentages, it graphs the values (n) rather than the percentages. Therefore, I was trying to create the percentages in SSAS. I reviewed the documentation you posted and included AXIS(1) in the denominator. The following messge was displayed when I put the cursor on the #VALUE result in the cell:

"The function expects a string or numeric expression for the argument. A tuple set expression was used."

The MDX code I processed was

IIF(IsEmpty([Measures].),NULL,

([DimLocation].[DimLocation].CurrentMember,[Measures].) /

(AXIS(1),[Measures].))

Any ideas if this can be changed? If you don't have time to look at it, I understand. I appreciate all the help you've given.

Thanks, SAP2006

|||

PGoldy,

We are using the ProClarity client tool and although it calculates and displays column, row and grid percentages, it graphs the values (n) rather than the percentages. Therefore, I was trying to create the percentages in SSAS. I reviewed the documentation you posted and included AXIS(1) in the denominator. The following messge was displayed when I put the cursor on the #VALUE result in the cell:

"The function expects a string or numeric expression for the argument. A tuple set expression was used."

The MDX code I processed was

IIF(IsEmpty([Measures].),NULL,

([DimLocation].[DimLocation].CurrentMember,[Measures].) /

(AXIS(1),[Measures].))

Any ideas if this can be changed? If you don't have time to look at it, I understand. I appreciate all the help you've given.

Thanks, SAP2006

|||

Hello,

I am new to MDX and I have a similar situation as discussed above i.e. finding % contribution to totals. I am using AS2005. Any idea of how this can be done?

Thanks,

Kulathu Sarma

|||

Hi,

Unfortunately, we haven't figured it out yet. However, in January, we have a consultant coming onsite to help with various issues. I'll post the solution if we find it.

SAP

|||

SAP,

It looks like you're working on a health-related application. We've also just started using SSAS2005 for cancer services data and are currently grappling with the same problem - creating row and column percentage measures that are not tied to specific dimensions. I've tried the Axis function without success; it returns a set that's the same as the current member or cell, so the percentage measure based on it is always 100%. This is probably not how it's supposed to work. Would be grateful for any advice in this regard...

QCCAT

|||

QCCAT,

The following MDX returns row percentages. Good Luck!!

//Begin - Percent Cases

CREATE MEMBER CURRENTCUBE.MEASURES.[Percent Cases]

AS

IIF(Axis(0)(0).Count < 2,

(Axis(0)(0).Dimension.CurrentMember,Measures.N)

/

Sum

(Filter(Axis(0),

InStr(Axis(0)(0).Dimension.CurrentMember.Name,"Total") = 0),

Measures.N),

NULL),

FORMAT_STRING = "0.0%",

VISIBLE = 1;

//End - Percent Cases