How to insert values at higher levels of the Hierachy

[imosri]

Hi,

This is the scenario:

We have some cubes with financial information.

We have some variables that are financial ratios.

There are very small differences between the ratios calculated by the cube and the ratios shown by the Financial System (0.001 and even 0.0001).

Fiancial department wants the ratios exactly as they appear on their relational system, so they gave us a full denormilized table with the "correct" ratios, with all the possible data combinations for the dimensions at all levels of the hierarchies.

So we need to acomplish the following:

Insert the data at all levels of the Hierarchies and suppres consolidation for that specific variable.

Your help is appreciated

Regards,

imosri

 

[MDXer]

What version of AS are you using. Also could you give an example?

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[imosri]

yikes!!!

Analysis Services for SQL Server 2000

Here is the example.

Suppose one measure, let say "Ratio"; and two dimensions "Organization".

So you have the following Hierarchy for the Organization Dimension:

Total Organization (0)
|
|_Division 1 (1)
| |
| |_Factory 11 (11)
| |_Factory 12 (12)
|
|_Division 2 (2)
| |
| |_Factory 21 (21)
| |_Factory 22 (22)
|
|_Division 3 (3)
|
|_Factory 31 (31)
|_Factory 32 (32)

The number in parenthesis is the ID code

The fact table we are getting is as follows:

Org_ID Data Date Measure
0 12.1 01/25/07 Ratio
1 14.6 01/25/07 Ratio
2 10.9 01/25/07 Ratio
3 12.8 01/25/07 Ratio
11 12.7 01/25/07 Ratio
12 12.1 01/25/07 Ratio
21 11.1 01/25/07 Ratio
22 10.1 01/25/07 Ratio
31 16.6 01/25/07 Ratio
32 13.2 01/25/07 Ratio

As you can see all the results for the complete dimension are provided, so what we need is to load the data at all levels of the dimension, and block any summarization (or consolidation) to be performed on that specific measure.

Thank you

imosri

 

[MDXer]

you need to look at non or semi-additive measures. along with calculated members or measures you could probably achive what you would like. If the the Ratio is always the same for a set membet then you may want to add these as member properties and then reference them in a calculated measure. You would use the calc member to test at what level you were at and either perform the calculation or supress the data.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck