Hierarchy - Measure question

Hi,

In the cube, i'm using one fact table (I will call just A) as a fact (A) and dimension (A1).

From this table(A) , I created hierarchy ( division - department with primary key from this fact) in the dimension(A1).

And we have another fact table(B) which is not related to the fact (A) and dimension(A1) fisically.

Here is the fact table (B) as under:

Plan Fact : ID(pk), Item_key, Plan_Sales , Date_key

I can join this fact (B) with product dimension which has item_key (pk) and this product dimension has division and deparment key.

My question is that is there anyway to see plan sales measure with the hierarchy I created?

Please let me know.

Thanks in advance.

Hi there,

AFAIK, you could achieve this with Analysis Services 2005.

Your data source view would consist of your department dimension, linked to both of your fact tables. When creating your cube in Analysis Services 2005, the cube creation wizard should pick up that you have two fact tables and a single dimension (you may need to point it in the right direction though!).

This should generate you a cube schema, with a single dimension (department) and two measure groups. One measure group would contain measures from fact table A, and the other measure group would contain measures from fact table B. One use of a measure group is that it can group measures from a single fact table.

Using the cube browser you should be able to drag on your division-department hierarchy, along with the relevant measures from each measure group.

Hope that helps.

Cheers,

Jonathon

|||Actually you can create what is called a referenced relationship in AS2005 where one dimension is joined to a fact table through another dimension. So you could relate the A1 dimension to the Plan fact table through the Item dimension.|||

Hi Darren,

Sorry for the late response and thanks for your reply.

I've tried to make a referenced relationship but it seems it does not give a right value.

if I select the reference dimension attribute : divdep ( which includes all attributes in divdep dimension) and intermediate dimension attribute : item_key ( pk in product dimension) which I join the fact table with, then the total is fine but in each division level and department level , the numbers are not correct.

If I select reference dimension attribute : division name and intermediate dimension attribute: division name , then I can see the right number only in division level. ( My question is do i need to select lowest level in reference dimension attribute? in this case , department name is the lowest in hierarchy. But it gives an error since this dimension does not have some attributes which appears in fact table.)

I need to view the data with division- department hierarchy and I've tried to do every single possible thing but I don't have any clue yet.

I appreciate if you can give me any comments.

Thanks.

|||

I'm not sure I'm completely understanding how you have things setup.

If you have a product dimension which includes an attribute for the department, you should be able to join the department dimension at it's key to the department attribute in the product dimension.

There is an example of a reference relationship in the Adventure Works cube, have a look at the Geography dimension and you should be able to find an example.