I need some expert guidance on the DMR model that I need to develop:
Table A: has ids, names and levels of all org. entities (Corp > LE > Store)
Table B: has information (id, name, ...) about the assets (For ex. desktop systems).
Table A_B: has details how A & B are related (like there will be multiple records for a corp_id, multiple records for an LE .... and so on)
So, if I am looking at Store level info, its should give me count(desktops) assigned to that store. If I roll up to the parent LE, it should show me count(desktops for all stores under that LE) + count(desktops directly assigned to this LE) and so on ...
How should I go ahead with creating a dimensional model for this?
No direction ... anyone???
thx.
You should create a fact table for your measures (count) depending on the information on your A_B (your table A_B may be the fact table itself but from your description its unlikely)
Table A
Corp LE Store id
CC1 LE1 Store1 111
CC1 LE1 Store2 112
Table B
Item id
Laptop 1
Desktop 2
Fact Table
Aid Bid Count
111 1 20
111 2 30
112 1 40
112 2 50
From the table structure and data above, I can create a model which can give me this report information:
Count
Store1 Laptop 20
Desktop 30
Store2 Laptop 40
Desktop 50