The same dimension twice in Analysis Services?

[jcoira]

Hi,

Suppose I have to design a datamart for a transportation company. I want to trace the quantity of passengers moving from place to place, using my buses.

My fact table will have one row for each trip of my buses. Will look like:
- Date_ID (from date dimension)
- Origen city (from Region dimension)
- Destination city (again from Region dimension)
- Quantity of pasengers (this is my measure)

But both Origen city and Destination city refer to the same REGION Dimension and I would like to know how to tell Analysis Services to deal with two dimensions which are really the same but twice.

I did two separate views pointing to the same REGION table and that seems to work, but isn't really awful ?

Any clue would be appreciated.

Thank you.

//jcoira

 

[blom0344]

Can you explain why this would be a bother? In relational terms, both Origen and Destination City are foreign keys, no matter whether they address the same dimension table. They serve , however a different purpose.
Imagine shipping passengers between 2 DIFFERENT regions. If you store the dimensional data for both regions in two different tables (because let's say of totally different fields needed) you would feel very comfortable by using both of them. There is no structural difference though between this set-up and using 2 views on one table.

Actually , I have a dimensiontable that gives the translation for all codes used in a warehouse sytem. I use loads of aliases of this one to translate codes (even on whole ranges of datatables) T. Blom
Information analyst
tbl@shimano-eu.com

 

[blom0344]

To add to this:

Circumventing this set-up would require a facttable with TWO entries for each trip: TRIP_FROM and TRIP_TO.
You would need two additional fields:
1. TRIP_TYPE
2. TRIP_KEY

and one Region field instead of two:

TRIP_KEY DATE_ID TRIP_TYPE CITY #PASSENGERS

TRIP_KEY to hold information between records together and trip TRIP_TYPE to indicate FROM or TO.

You could see this as a departures/arrivals type of structure, with arrivals being able to be a day later than departures


T. Blom
Information analyst
tbl@shimano-eu.com

 

[jcoira]

Hi Blom,

I agree with you, but I thought there were a loop hole in Analysis Services to avoid the tons of aliases you mentioned.

Actually I did it using a couple of views but it means you must have one view for each "new" dimension you need, actually refering to the same dimension table in the relational model. I would be better to do the same without having to write two (or more) different views pointing to the same table. Isn't it ?

// jcoira

 

[jp6212]

I see a flow in this DataMart design, particularly in the Region table; Origination & Destination should just be attributes in the Region table.

You would need two records in your dimension table (Region) and a computer generated key to fix the flow, and to track trips in both directions . Your Region table layout would looks like this:

Bus# Orig Dest
---- ---- ----
45 NY Chi
46 Chi NY

where Bus# would be computer generated key, and this key you would store in your fact table. When you need to get your facts just specify your Orig or Dest or Both ...

 

[jcoira]

Hi,

What about aggregations in the fact table? I believe this approach will decrease the queries response time. I dont even know if it is possible to deal with a fact table like that.

// jcoira

 

[blom0344]

Having aggregates on facttables can be useful when you want to answer questions like: How many passengers where carried during period x in region y. Drawback is that you lose detail-level,so you might end up creating more and more types of aggregates to suit user-demands if you cannot determine the type of questions that will be posed.

Apart from query performance in terms of SQL-speed, it makes a lot of difference for Business Intelligence tools to create reports on large or small datasets, which makes aggregates quite handy to get quicker formatting reports ....

The true challenge is knowing the right set of dimensions to aggregate on............ T. Blom
Information analyst
tbl@shimano-eu.com

 

[renu123]

If there are two foreign keys in a fact table pointing to the same dimension table, for eg., two dates like start date and end date to the time dimension table, we can have two different views based on a single underlying physical table. How can this be represented in ERWIN?
In ERWIN, when I create two views , I am not able to link the views to the fact table.
How is this to be represented in ERWIN?

Regards