Match [DimTime] table with fact table?

Question

I'm a BI beginner, so please bear with me.

I have a fact table [Fact15MinData] that looks like this:

Cell      Date      Hour  Minute   Counter1   Counter2
Cell1_1   20141001  2     15       2.4        4.5
Cell1_1   20141001  2     30       4.5        39.2
Cell1_1   20141001  3     45       1.5        3.2   
Cell1_1   20141001  5     00       3.5        9.2

My [DimTime] table looks like this:

ID      Time        Hour    Minute  Second  AmPm
1       00:00:00    0       0       0       AM
901     00:15:00    0       15      0       AM
1801    00:30:00    0       30      0       AM
2701    00:45:00    0       45      0       AM
3601    01:00:00    1       0       0       AM
4501    01:15:00    1       15      0       AM

How can I match my [DimTime] table to my [FactData] table?

score 0 · Answer 1 · edited May 23 '17 at 11:59

0

Create the compound key relationship (Hour & Minute) in the DataSourceView, and both columns should appear in the Dimension Usage tab in your cube.

ssas compound dimension key

edited May 23 '17 at 11:59

Community

1
1

answered Oct 14 '14 at 17:36

Tab Alleman

31,483
7
36
52

score 0 · Answer 2 · answered Oct 15 '14 at 07:15

You shouldn't have hours and minutes on your fact table, but just the key ID from DimTime.

On your ETL you need to lookup the row by matching hour and minute and return the ID, perhaps renaming it to time_id.

Any ETL tool should provide a way to lookup values in dimensions. In Pentaho Data Integration, for example, you can use the database lookup or the combination lookup/update steps for that purpose.

score 0 · Answer 3 · answered Mar 15 '16 at 04:01

As you want to have a linked between the Fact15MinData(Which is having counter data) and DimTime which is a combination of ID,Time,Hour,Minute,Second,AmPm. So you can have a relation ship between both of them by compairing hour and minute but as you have AmPm as a cloumn you will have 2 record for a particular combination of hours and min in DimTime.So to solve this issue you can add 12 to hours when the value of AmPm is Pm so that you have 1-1 relationship between the both the table.

But as suggested by nsousa and Tab Alleman you should not store the value from dimention table into fact table so you should have the below structure for your both table:

Fact15MinData: Cell-Date-DimTime_ID-Counter1-Counter2
DimTime:ID-Time-Hour-Minute-Second-AmPm.

Match [DimTime] table with fact table?

3 Answers3