AS 2005 modeling question

Hello everybody!

We have three fact-tables with a classic hierarchical order:
-customer table
-order table
-order positions

one customer has multiple orders and one order has multiple order positions

and we have one dimension articles related to the position table.

Further we defined a count measure for each measure group.

The question is how can we connect the articles dimension (related to order positions measure group) to the customer measure group to get a count over customers? We tried to use m:n relations. It was not very fast (10 million fact rows).

Any ideas to model this 1:n scenario fast and clever ;-)?

Thanks in advance
Ole J.

I am not sure about what you mean about the customers table being a fact table. That is a dimension table. Perhaps you can explain further?

Next. In the order postions table you will only have to add the order number and the customer number to that fact table as keys like this:

-CustomerKey

-OrderKey

-OrderItemKey

-Measure.

You will do this in the ETL-process with SSIS(Integration services) when you load your fact table.

Your mistake is perhaps that you have separated orders and order line items in two different fact tables?

HTH

Thomas Ivarsson

|||Thanks Thomas for your first ideas. I would like to tell you a little bit more background.

The customer table is indeed a fact table because there are for example measures like lifetime-values and others... and further the focus of investigation is not only the customer name but the count of customers is more important.

The vision is to have a customer measure group with at least one measure customer count and one or more dimensions like customer sex or others...

And with order facts we want to analyze the count of orders (facts), the amount group (dimension) and so on.

Line items are interesting because of the count over line items, a dimension that filters some properties of the line items and further more.

Is it really necessary to join the tables before we build the cube? We thought that in AS2005 we can simply bring together the measure groups?

What we have done so far is we denormelized the tables in the following way:

Customer table:
CustomerID, CustomerSexID

Order table:
OrderID, AmountGroupID, CustomerID, CustomerSexID

OrderItems table:
OrderItemsID, OrderitemsDimensionID, OrderID, AmountGroupID, CustomerID, CustomerSexID

In BI Studio it looks like this:

MeasureGroup MeasureGroup MeasureGroup
Customer Order OrderItems
Dimension
CustomerSex X X X
OrderAmountGroup X X
OrderItemsDimension X

So if you anaylize rigth now the dimension CustomerSex you get the correct results for:
count customer
count order
count orderitems

But if you want to analyze the OrderItemsDimension you get only the correct results for the count OrderItems because of the fact that until now the Orders and Customers measure group are not related to the dimension OrderItemsDimension. So the question is how can you go the way back from the count orderitems to get a result for the orders or customer counts?

I hope you can now better imagine what i mean..? Any ideas?

Thanks in advance,
Ole J.|||Any ideas?

Thanks in advance!

Ole J.|||

Hello Ole. This looks like a complicated project. I am unable to see the complete picture of what you would like to do.

I am not sure if your best choice is to do data mining, build a cube or combine these approaches.

My recommendation is to have a look att www.Kimball.group and see if there is any design tips that can help you.

You can also have a look here(http://www.sqlserverdatamining.com)

In SSAS2005 the customer sex is an attribute within the customer dimension so I do not understand why you will build this as a single dimension.

Regards

Thomas Ivarsson

|||

If I understand the situation, you already have CustomerSex as a reference dimension in the OrderItems measure group and you just want to control the behavior of the values in the Cutomer and Order measure groups when a order item is specified in the query. (The cube wizard should create this design for you if you specify each of the three tables as both fact and dimension tables.) Try using the IgnoreUnrelatedDimensions property on the measure groups (see http://msdn2.microsoft.com/en-us/library/microsoft.analysisservices.measuregroup.ignoreunrelateddimensions.aspx).

Hope this helps