DMR model

[akp]

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?

[akp]

No direction ... anyone???

thx.

[Francis aka khayman]

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