I am new to this forum, so first of all I would like to say thank you to everyone beforehand.
I have a small problem when I try to create a star-scheme-datamodel. I have created a sample "data" file which illustrates the problem (please see attached Excel-file).
In this file, there are three sheets. The first sheet shows some actual sales data on a brick level. The second one holds the objectives (plan numbers) on a territory level. The last sheet shows the links between the bricks and territories.
I am now struggeling about how to build up the data model. As I was told, a star scheme is always the best way to go. This would mean, that I would concatenate the two data tables (Sales and Objectives). In this case I would get a table with four fields (Brick, Territory, Sales, Objectives) with five datasets where either Brick/Sales or Territory/Objective hold data.
The dimension table would basically be the same table as in the Excel file.
If I build up the model as explained, I would get a loop between the two tables (Brick and Territory) in both tables.
My question now is, how can I set up a data model in QV to represent this data correctly without any loops?
In my opinion, this would not help thougt. If I left join the Geography table to either one the number of datasets in the table would be increased. This would mean, that the total sum would increase as well and that the results are not valid anymore.
This is completely correct. Unfortunately the original tables have some other fields as well, such as a date (or month) and this would then immediately result into a loop.
Besides, I was told that a star scheme should be built up the way that you have one central data table (in my case Sales and Objectives) and next to it the dimensions (Brick, Territory, Date). This would mean that I have to concatenate the two data tables (Sales and Objectives) and from here on I get the problem as described above.