Modelling cutomer dimension and sales orders fact

Hi

I am working currently on the design of the sales order fact and I have several questions. Please have a look on the current model below:



1. The business wants to be able to analyze the the sales orders both on Customer level and on Customer Address level (each customer could have more than one address). Currently I have only FK to the Address dimension where I have a FK to the customer dimension (snowflake model), so I could easily do both analisys in my BI tool (SSAS). Is this acceptable or do I have to stick to the start model?

2. We have several business units presented in the data warehouse. Each unit has it's local reporting currency. Each transaction (in this case Sales Order) could have different transaction currency. On top of that the whole group should do the reporting in euro. What makes sense - to have only values in the transaction currency and exchange rate to local and global reporting currency or to put the actual values for all amounts? We have several measures that contain amounts - Net Amount, Total Amount, Tax Amount etc.

3. Our customers could have a corporate customer associated to them. This corporate customer on it's turn could belong to some global customer as well. There are only those two levels. What is the best way to present this - the customer dimension to point to itself or to use a bridge table with parent-child relationships?

4. There are some calculated measures in the fact (Total Amount = Net Amount + Tax Amount, Total Cost = sum(cost elements) etc.). Should we have those measures pre-calculated during the ETL process or could we let BI tool calculate them? The fact should be holding less then a million records (not very big).

5. For some dimensions there are attributes that need to be remapped in order to be analysed on a company group level (Customer Numer/Code, Customer classification etc.). In the current model we have decided to have Local and Group values for each attribute in the same dimension. The other option would be to have separate dimension containing the "group" data linked either to the fact (don't like this option) or as a snowflake to the "local" or main dimension. What do you think?

Best regards,

Evgeni

1. customer should be a FK off the fact. Do not snowflake.

2. Common practice is to store both local and standard currency (in your case Euro). It helps to store the conversion rate as well. When the data makes its way through the system into accounting, then things get strange. Storing sales data like this helps reconcile questions at the end of the period.

3. Use an exploded hierarchy bridge table.

4. Put the calculations in the BI layer

5. I don't understand what you mean.

ngalemmo wrote:

5. I don't understand what you mean.

Sorry, I'm not native english speaker. Let me elaborate on this:

We have several ERP systems. It is possible, that a customer is registered as such in more than one ERP system. It would be having a different customer number and could have also his name spelled differently. On the other side there is a mapping between local customer numbers, so that they could be linked to each other. So on corporate level there is a "corporate customer number". For many reasons it is not possible to change the customer codes in the local ERP system, so this is the only way to do the analysis of the turnover on a corporate level for a particular customer. On the other hand the local business units are used to work with the local customer numbers/names, so we need to preserve and present those in the BI tool as well.

Thus we have decided to have customer dimension looking like this:

CustomerID
LocalCustomerCode
LocalCustomerName
CorporateCustomerCode
CorporateCustomerName
etc.

My question is if this makes sense or there is a better way to "picture" this mapping.

Regards,

Evgeni

No, that's fine.