Design Suggestion

Hi All,

I have a cube with a fact table containing sales data and multiple
dimensions. I have Sales Geography dimension, Sales District dimension and
Sales Group dimension (and some others, which is irrelevant for this
question).

Geography table is: Geography_Key, Geography, g_population
District table is : district_key, district, fk_geography_key, d_population
Group table is: group_key, sales group, fk_district_key, g_population

Each of the dimensions is connected to the fact table via the corresponding
key (geography_key, district_key, group_key).

In addition to the regular sales reports, I have to report the population of
each group; thus, I created the appropriate measures.

What would be a suggested way of creating a dimension combining all three
tables, so I can report proper population and sales/population correctly for
each level? Please note, there might be duplicate districts and groups names
in different geographiesl.

-
Thanks,

Pasha

"Please note, there might be duplicate districts and groups names in
different geographies .." - could you explain the relationships between
the 3 dimensions? From the foreign keys, it looks like the district can
be derived from the group, and the geography from the district. If so,
can multiple group_keys share the same name - could you give some
examples?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Deepak,

Consider this example:
1.
Geography: CA
District: SF
Group: Electronics
2.
Geography: CA
District: LA
Group: Electronics
3.
Geography: GA
District: LA
Group: Electronics
4.
Geography: FL
District: SF
Group: Electronics

As you can see, Electronics group exists in all four geographies and
districts. SF and LA districts exist in FL and CA geographies. Geography
table is connected to the district via the geography_key (it is a foreign key
in district table); district table is connected to the group table via the
district_key (it is a foreign key in a group table).

The fact table has all three keys. What I need is a dimension, with all
three (Snowflake?) tables in it. Sometimes, for example, I would need to
display all Electronics Groups in CA

Hope this clarifies....

--
Thanks,

Pasha


"Deepak Puri" wrote:

> "Please note, there might be duplicate districts and groups names in
> different geographies .." - could you explain the relationships between
> the 3 dimensions? From the foreign keys, it looks like the district can
> be derived from the group, and the geography from the district. If so,
> can multiple group_keys share the same name - could you give some
> examples?
>
>
> - Deepak
>
> Deepak Puri
> Microsoft MVP - SQL Server
>
> *** Sent via Developersdex http://www.developersdex.com ***
>

This is what I don't get: "district table is connected to the group
table via the district_key (it is a foreign key in a group table)".

Are there multiple entries for "Electronics" in the group table? If
there's just a single entry, how can it have a foreign key for both LA
and SF distircts? It would be clearer if you post the data columns in
the 3 tables for this example.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Deepak,

Here is the simplified DDL:

create table dim_Geography
(
geo_key int identity(-1,1) CONSTRAINT PK_geo_key PRIMARY KEY CLUSTERED,
geography varchar(50),
[population] float
)

create table dim_district
(
district_key int identity(-1,1) CONSTRAINT PK_district_key PRIMARY KEY
CLUSTERED,
district varchar(50),
fk_geo_key int constraint fk_geo_key FOREIGN KEY REFERENCES dim_Geography
(geo_key),
[population] float
)

create table dim_group
(
group_key int identity(-1,1) CONSTRAINT PK_group_key PRIMARY KEY CLUSTERED,
sales_group varchar(50),
fk_district_key int constraint fk_district_key FOREIGN KEY REFERENCES
dim_district (district_key),
[population] float
)

create table fact_sales
(
date_key int,
group_key int constraint f_fk_group_key FOREIGN KEY REFERENCES dim_Group
(group_key),
district_key int constraint f_fk_district_key FOREIGN KEY REFERENCES
dim_district (district_key),
geo_key int constraint f_fk_geo_key FOREIGN KEY REFERENCES dim_Geography
(geo_key),
sales float

)



--
Thanks,

Pasha


"Deepak Puri" wrote:

> This is what I don't get: "district table is connected to the group
> table via the district_key (it is a foreign key in a group table)".
>
> Are there multiple entries for "Electronics" in the group table? If
> there's just a single entry, how can it have a foreign key for both LA
> and SF distircts? It would be clearer if you post the data columns in
> the 3 tables for this example.
>
>
> - Deepak
>
> Deepak Puri
> Microsoft MVP - SQL Server
>
> *** Sent via Developersdex http://www.developersdex.com ***
>