OLAP Services MDX (provider, datasource, database, cube, memb1, memb2, …)

Description:

OLAP Services MDX Optimization

PowerOLAP can access data from Microsoft Analysis Services using the OLAP Services feature. The metdata structure is replicated in PowerOLAP. The fact data can either be copied into PowerOLAP or can be accessed via a PowerOLAP formula. This new optimization improves the performance of the formula version by pre-querying the values from Analysis Services.

 

Introduction

PowerOLAP accesses data from Analysis Services dynamically by performing an MDX query via a PowerOLAP formula. The PowerOLAP calculation engines perform point-by-point calculation due to the flexibility of PowerOLAP formulas. This implies that for each point that is calculated from Analysis Services an MDX query must be formed and submitted. This process can be very slow performing if the amount of data requested is large.

This new optimization allows a PowerOLAP developer to create additional formulas that optimize the Analysis Services integration by performing large MDX queries to return large blocks of Analysis Services values needed by the PowerOLAP request.

 

Implementation

This optimization works with the existing OLAP Services formulas in PowerOLAP. A user is not required to implement this optimization to access Analysis Services data, however, they will notice significant performance improvements when making large requests. The basis for this optimization is defining the range of points that will be governed by a PowerOLAP formula and telling PowerOLAP how to query the corresponding data.

To take advantage of this new feature, the PowerOLAP developer will need to write new PowerOLAP formulas using the MDX function. A new tab has been added to the formula dialog. This OLAP Services tab includes a single MDX function. The syntax for OLAP Services is simply Left hand side LHS = MDX function.



For each LHS in a formula that accesses Analysis Services, you will need to write at least one MDX function to take advantage of this feature.

Syntax: MDX (provider, datasource, database, cube, member1, member2,…)

• Provider : Specify the name of the "provider" that is the OLEDB link to connect from PowerOLAP to the source database.
• Data-source : Specify the name of the server that has any OLEDB compliant "Analysis Services" database on it.
• Database : Specify the name of the database in "Analysis Services" which you want to connect to.
• Cube : Specify the name of a cube in the database mentioned above.
• Member1 : Specify the name of the member in the 1st dimension of this cube.
• Member2 : Specify the name of the member in the 2nd dimension of this cube.
• Member-n : Specify the name of the member in the nth dimension of this cube.

How it Works

This optimization applies to the following interfaces:

–    Calculate Slice

–    Export Slice

–    Constrain Slice

–    Export Fact Data

–    Transfer Cube

–    IDS

–    OLAPTable

–    OLAPPivot

 

When a request from one of these interfaces is made to PowerOLAP, PowerOLAP first determines the intersection of the values requested and the LHS of the OLAP Services MDX functions. PowerOLAP then uses the MDX function parameters to generate one or more MDX queries. Once returned, these values are inserted into temporary memory specific to the user making the request. PowerOLAP then continues on with its normal calculation process. When executing each OLEDBREAD formula, PowerOLAP first checks the temporary memory for the specific value. If it finds the result it is simply returned. If it is not found then PowerOLAP will use the normal process and request the value point-by-point from Analysis Services.

It is not a requirement that every Analysis Services point request be governed by an MDX formula. Those cells that are not governed by one of these formulas will continue to function as currently implemented.

 

Things to Know

A single PowerOLAP cube can only be optimized against a single Analysis Services cube. A debug option can be enabled to assist in the development of the MDX formulas. The debug flag causes information to be written to a file named POMDX.log written to the root of the c drive. This flag should only be used during development phase and should not be enabled on a production system.



To enable debugging, create a new DWORD value in the HKEY_CURRENT_USER/Software/Paris/PowerOLAP/Settings section. Set the value to 1 to see the debugging information. The debug file will contain the MDX statements that are executed along with any particular PowerOLAP/MDX failures. When performing the OLEDBREAD function additional information will be written indicating if any cache exists and will create a line for each point that is NOT found in the cache.

 

Example

Below is a very simple example based on the sample Adventureworks database:

All and {}=OLEDBREAD("MSOLAP.3","willson", "Adventure Works DW Standard Edition","Adventure Works", ALIAS("Customer", "AS ID", MemberIndex("Customer", Current("Customer"))), ALIAS("Date", "AS ID", MemberIndex("Date", Current("Date"))), ALIAS("Adventure Works_Measures", "AS ID", MemberIndex("Adventure Works_Measures", Current("Adventure Works_Measures"))), ALIAS("Product", "AS ID", MemberIndex("Product", Current("Product"))));

The above formula is a standard Analysis formula that is created using the OLAP Services wizard in PowerOLAP. The LHS governs the entire cube. The right hand side tells PowerOLAP to use the Alias values in the “AS ID” group to create an MDX query to access the point.

 

The corresponding OLAP Services optimization formula would be the following:

All and {} = MDX("MSOLAP.3","willson","Adventure Works DW Standard Edition", "Adventure Works", "Customer.AS ID", "Date.AS ID", "Adventure Works_Measures.AS ID", "Product.AS ID");

 

The first four parameters match the parameters for the OLEDBREAD function. Each additional parameter corresponds to the dimensions in the PowerOLAP cube. These parameters tell PowerOLAP where to access the AS Member names from PowerOLAP to build an MDX query. When using the OLAP Services wizard in PowerOLAP, the unique AS ID is added into the “AS ID” alias.

 

Users can also create specific PowerOLAP formulas that reference Analysis services points. The following example demonstrates a new, persistent PowerOLAP member that is a calculated value based on a value from Analysis Services.

All and {"Amount"} = ["Internet Sales Amount"];




All and {}=OLEDBREAD("MSOLAP.3","willson", "Adventure Works DW Standard Edition","Adventure Works", ALIAS("Customer", "AS ID", MemberIndex("Customer", Current("Customer"))), ALIAS("Date", "AS ID", MemberIndex("Date", Current("Date"))), ALIAS("Adventure Works_Measures", "AS ID", MemberIndex("Adventure Works_Measures", Current("Adventure Works_Measures"))), ALIAS("Product", "AS ID", MemberIndex("Product", Current("Product"))));

 

In this example, the first formula uses the result of the second formula to calculate the value for Amount. To optimize this, another MDX function must be written to create the MDX to get the values for Amount.

All and {"Amount"} = MDX("MSOLAP.3","willson", "Adventure Works DW Standard Edition","Adventure Works", "Customer.AS ID", "Date.AS ID", "Adventure Works_Measures.[Measures].[Internet Sales Amount]", "Product.AS ID");




All and {"Internet Sales Amount", "Internet Order Quantity"} = MDX("MSOLAP.3","willson","Adventure Works DW Standard Edition", "Adventure Works", "Customer.AS ID", "Date.AS ID", "Adventure Works_Measures.AS ID", "Product.AS ID");

The first MDX formula governs the value for Amount. In the MDX function the member for the Measure is hard-coded to [Internet Sales Amount]. This is telling PowerOLAP that the Amount member is always calculated by using the [Internet Sales Amount] value from Analysis Services. If a PowerOLAP dimension contains persistent members and that dimension is used in the LHS of a formula then the default MDX function must contain a list of all the members to calculate from Analysis Services. Otherwise, the default LHS (All and {}=) will include the PowerOLAP value. In this case, PowerOLAP would attempt to create an MDX statement including the value Amount.

 

The third situation is the most complex. A PowerOLAP formula may use multiple Analysis Services values to calculate a PowerOLAP value. Below are sample formulas that perform basic PowerOLAP calculations against Analysis Services data.

All and {"Amount"} = ["Internet Sales Amount"];




All and {"Amount2"} = ["Internet Sales Amount"] / ["Internet Total Product Cost", "Product Categories - Bikes"];




All and {}=OLEDBREAD("MSOLAP.3","willson", "Adventure Works DW Standard Edition","Adventure Works", ALIAS("Customer", "AS ID", MemberIndex("Customer", Current("Customer"))), ALIAS("Date", "AS ID", MemberIndex("Date", Current("Date"))), ALIAS("Adventure Works_Measures", "AS ID", MemberIndex("Adventure Works_Measures", Current("Adventure Works_Measures"))), ALIAS("Product", "AS ID", MemberIndex("Product", Current("Product"))));

 

The second formula uses two values from Analysis Services to compute a new persistent member in PowerOLAP. This introduces complexity because a single PowerOLAP formula requires multiple MDX queries. Also, the divisor in this case uses a specific intersection of a second dimension from Analysis Services. So to compute the value for Amount2, PowerOLAP must query MDX data from two different intersections. Two MDX formulas are needed to do this.

All and {"Amount"} = MDX("MSOLAP.3","willson", "Adventure Works DW Standard Edition","Adventure Works", "Customer.AS ID", "Date.AS ID", "Adventure Works_Measures.[Measures].[Internet Sales Amount]", "Product.AS ID");




All and {"Amount2"} = MDX("MSOLAP.3","willson", "Adventure Works DW Standard Edition","Adventure Works", "Customer.AS ID", "Date.AS ID", "Adventure Works_Measures.[Measures].[Internet Sales Amount]", "Product.AS ID");




All and {"Amount2"} = MDX("MSOLAP.3","willson", "Adventure Works DW Standard Edition","Adventure Works", "Customer.AS ID", "Date.AS ID", "Adventure Works_Measures.[Measures].[Internet Total Product Cost]", "Product.[Product].[Product Categories].[Category].&[1]");

 

All and {"Internet Sales Amount", "Internet Order Quantity"} = MDX("MSOLAP.3","willson","Adventure Works DW Standard Edition", "Adventure Works", "Customer.AS ID", "Date.AS ID", "Adventure Works_Measures.AS ID", "Product.AS ID");

 

Two new MDX functions have been added to calculate the value for Amount2. The first maps all matching dimensions using the “AS ID” alias value and specifies the [Internet Sales Amount] as the Analysis Services Measure member. The second maps all matching dimensions except for the Measure dimension and the Product dimension. These two are hard-coded to the specified Unique ID’s from Analysis Services.

In certain circumstances, A PowerOLAP cube may have additional local dimensions that do not exist in the associated Analysis Services cube. In these situations, the LHS of the MDX function MUST specify a single element from the additional PowerOLAP dimensions. Below is an example of a new MDX function in a cube that contains two additional PowerOLAP dimensions named “My Measures” and “My Measures two”. It logically makes sense to add these to the LHS of the formula as well.

All and {"My Measures.Amount", "My Measures two.Amount 2"}= MDX("MSOLAP.3","willson","Adventure Works DW Standard Edition", "Adventure Works", "Adventure Works_Measures.AS ID", "Customer.AS ID", "Date.AS ID", "Adventure Works_Measures.AS ID", "Product.AS ID");

 

A member reference in the MDX formula can specify a value using three techniques. All instances require the prefix to contain the name of the dimension followed by a period. To the right of the period valid references include a specific Analysis Services member ID, a valid PowerOLAP Alias group that contains the corresponding Analysis Services member names or the word “Member”. If the value is an Analysis Services ID then that single value is used for all results of the MDX query. If the value is an alias group then for each value requested along that dimension, the corresponding alias value for those members will be used in the MDX query. If the value is “Member” then the PowerOLAP Member name will be used to create the MDX query for this dimension.