MS SQL Server OLAP - Time dimension

Author

Time dimension

Billy

2006-01-27, 9:23 am

I am working on a cube that has a time dimension. My fact table has one entry
for each day and I have generated a time dimension table containing dates for
many years ahead. This time dimension table was generated from SSAS.

My cube is the dataset for my reports that I use Reporting Services to
design and the time is a parameter for my reports. As I have a lot more dates
in my time dimension table than corresponding records in my fact table, the
date parameter drop down in Reporting Services contain a lot of useless
dates. The dates are also formated in a way I don't like (ISO-dates etc.).

If I make a view in the database like this (pseudo code):
create view as
select myDate, datepart(yy, myDate), datepart (ww, myDate), datepart(dd,
myDate)
from myFactTable

and use this view as the source from my time dimension in the cube, do I
then break good design rules or run into other problems?

Thanks in advace for your help.

Sreejith G

2006-01-30, 3:23 am

Yes that's one option... View. Again why are you populating data in Time
Dimesion before which actual data is not there....

In my design, i populated time data only from min and max date of fact date
field... I did like that as in my frontend lot of dates in future used to
come...

When i pull data from OLTP to OLAP, i check for the minimum date in FACT
table and popukated Time Dimension starting from that date...

Thanks,
Sree

"Billy" wrote:

> I am working on a cube that has a time dimension. My fact table has one entry
> for each day and I have generated a time dimension table containing dates for
> many years ahead. This time dimension table was generated from SSAS.
>
> My cube is the dataset for my reports that I use Reporting Services to
> design and the time is a parameter for my reports. As I have a lot more dates
> in my time dimension table than corresponding records in my fact table, the
> date parameter drop down in Reporting Services contain a lot of useless
> dates. The dates are also formated in a way I don't like (ISO-dates etc.).
>
> If I make a view in the database like this (pseudo code):
> create view as
> select myDate, datepart(yy, myDate), datepart (ww, myDate), datepart(dd,
> myDate)
> from myFactTable
>
> and use this view as the source from my time dimension in the cube, do I
> then break good design rules or run into other problems?
>
> Thanks in advace for your help.

Billy

2006-01-30, 8:24 pm

Good advice.

Thanks for your help.

"Sreejith G" wrote:
[color=darkred]
> Yes that's one option... View. Again why are you populating data in Time
> Dimesion before which actual data is not there....
>
> In my design, i populated time data only from min and max date of fact date
> field... I did like that as in my frontend lot of dates in future used to
> come...
>
> When i pull data from OLTP to OLAP, i check for the minimum date in FACT
> table and popukated Time Dimension starting from that date...
>
> Thanks,
> Sree
>
> "Billy" wrote:
>

Sponsored Links