Two Tables with more dimensions

[pacoli2013]

Hello,

I have a Problem and I don’t know the right way to solve it. I think there is more then one solution, two in the script and one with expressions.

My Problem is that I have two tables: Sales Orders and Budget. In both tables I have the dimensions CountryID, CustomerID, ItemID, ItemGroup and SalesDistrictID. In both tables I have a TimeFilter YearMonth.

I cannot delete the dimensions in de Sales Orders because there is a relation between the ordernumbers and the different dimensions. The same for Budget, a budgetamount can have more then one relation with the (different) dimensions.

For example an order- or budget amount can have itemnumbers in combination with several customernumbers  and/or several countrID’s, etc.

To create a relation between the two tables I have the Options:

1) Create a composite key in the script, consisting of TimeFilter &’/’& CountryID  &’/’&  CustomerID &’/’& ItemID &’/’& ItemGroup &’/’& SalesDistrictID. Problem is that in a SalesOrderLine not all dimensions are always set, for example there is no SalesDistrictID when the order covers more than one SalesDistrictID, while at the same time in the budgettable there is a dataset for every possible dimension-combination for every month.

2) Create a budget table per dimension with a BudgetAmount per dimension. Then I get in Sales Orders a key per dimension. Problem/Question is when creating a report with more than one Dimension which BudgetAmount should be in the Report?

3)Using expressions with only TimeFilter YearMonth as key between the two tables, I got the full BudgetAmount per Dimension and I have the problem that my knowledge of Set Analysis is not sufficient enough to create the right expressions. But I think it is the best solution.

I have added an example with a BudgetTable with one BudgetAmount and a TimeFilter as key. And I hope that someone can help me with the right expressions. My example has more than one sheet but when I have the right expressions on the first and second sheet I think I can use them on the other sheets.

If someone thinks option 1 or 2 is the better solution, I would be glad to hear his/her opinion, motivation and solutions in script and expressions.

Thanks in regard

Court

[marcus_sommer]

I suggest a quite different approach instead of linking both tables and this is just to concatenate them. You might need some small adjustments to the fieldnames and to add/enrich/transform some fields for the links to the dimensions - for example are the budgets usually only on a period-level available but in a concatenated table you would need a date - such transforming is with the date#() or a makedate() quite easy.

In my experince is this approach compared with all other possible solutions the most simple ones and performed very well - in each case better than using any linking between two fact-tables. Therefore give it a shot and just try it out.

- Marcus

[pacoli2013]

Hello Marcus,

Thanks for your suggestion, 

Using MakeDate(Year, Month, 1)   as CreationDate (salesOrder has an CreationDate and then link this date as Date to my Calendar.

I think the Dimensions can be the same,

I will test it and will give you an answer of the results

Court

[pacoli2013]

Hallo Marcus,

Your suggestion works, should rebuild some tables and made some tests, but at the end I got the results wanted. Thanks a lot.