Best practice relationships when financial postings and budgets are on different levels

[Anonymous]

Hi, 

I would like some advice on how to set up the relationships when I have 

- Projects with 3 levels

- Budgets on the top levels

- Financial transactions on the lowest level

- Product grouping on Level 2

I would like tohave the ability to see the budget and transactions on top level, and also see budgets and transactions on products. 

Data looking like this:

Project number	Level 2	Level 3
A-531	A-531.10	A-531.10.10
A-531	A-531.10	A-531.10.10
A-531	A-531.20	A-531.20.10
A-531	A-531.21	A-531.21.10
A-531	A-531.22	A-531.22.10
A-531	A-531.23	A-531.23.10
A-531	A-531.24	A-531.24.10
A-532	A-532.10	A-532.10.10
A-532	A-532.10	A-532.10.10
A-532	A-532.20	A-532.20.10
A-532	A-532.21	A-532.21.10
A-532	A-532.22	A-532.22.10
A-532	A-532.23	A-532.23.10
A-532	A-532.24	A-532.24.10

Project number	Budget
A-531	3000000
A-532	6000000

UnderWBS	Dato
A-531.10.10	01.05.2020
A-531.10.10	01.02.2020
A-531.20.10	01.02.2020
A-531.21.10	01.02.2020
A-531.22.10	30.03.2020
A-531.23.10	30.03.2020
A-531.24.10	30.03.2020
A-532.10.10	01.02.2020
A-532.10.10	01.02.2020
A-532.20.10	01.05.2020
A-532.21.10	01.02.2020
A-532.22.10	01.03.2020
A-532.23.10	30.03.2020
A-532.24.10	01.02.2020
A-531.22.10	01.04.2020
A-531.23.10	01.02.2020
A-531.24.10	01.02.2020
A-532.10.10	01.05.2020
A-532.10.10	01.02.2020
A-532.20.10	28.04.2020
A-532.21.10	01.04.2020
A-532.22.10	01.02.2020

Level 2	Product
A-531.10	Vegetables
A-531.20	Other
A-531.21	Fruit
A-531.22	Fruit
A-531.23	Fruit
A-531.24	Fruit
A-532.10	Vegetables
A-532.20	Other
A-532.21	Fruit
A-532.22	Fruit
A-532.23	Fruit
A-532.24	Fruit

[DebbieE · Accepted solution]

Hi,

Ill have a go at this one too

there should be a date table

I would add a Surrogate ket to all the description tables (Your dimensions)

So 1,2,3 etc

So lets take one example

Key			Project number	Level 2	Level 3
1			A-531	A-531.10	A-531.10.10
2			A-531	A-531.10	A-531.10.10
3			A-531	A-531.20	A-531.20.10
4			A-531	A-531.21	A-531.21.10
5			A-531	A-531.22	A-531.22.10
6			A-531	A-531.23	A-531.23.10
7			A-531	A-531.24	A-531.24.10

Project A-531 has 7 rows of data. However 1 and two are confusing because the IDs appear to be identical so you would have issues with this. Every 3 levels should be unique

Key		Project number	Budget
1		A-531	3000000

And the top level project  connects  to a budget

Key		Level 2	Product
1		A-531.10	Vegetables
2		A-531.20	Other
3		A-531.21	Fruit
4		A-531.22	Fruit
5		A-531.23	Fruit
6		A-531.24	Fruit

And your 2^nd level project seems to connect to the 6 products

And finally a date table connecting to a date ID rather than a date

DateKey         Date                     Month     Year

20200501      01.05.2020    May        2020

And your Fact table connects up to your lowest level level project so Im just going to choose 1 level

You can merge your Surrogate keys into the fact table to use them as a join in the model

Project Key	Budget Key	Product Key	UnderWBS	Date Key	Date	Metric
3	1	2	A-531.20.10	20200501	01.05.2020	300

And what you are looking for is single joins to your fact table so your descritive info can fiter your metrics. Like Give me sales by product.

I think its 1 on your dimension to the many to your fact table

So I always just write myself a little sentence to say for example

Many projects can be on the same date

Im not too sure on your business logic but it always helps me

[MFelix]

Hi @Anonymous ,

I would create a dimension table for:

• Level 2
• Level 3
• Dates

Then relate the tables with the Projects tables and the products and financials, that should give you the needed relationship to have everything working, but you can have to make some specific measures depending on the way you are picking up the values.