Re: Customize the joins between my Fact table and Dimensions?
- From: dba <bryanmurtha@xxxxxxxxx>
- Date: Thu, 08 Nov 2007 11:41:21 -0800
Hey Dan,
I only know 2005, but I have created a cube off an OLTP DB which
it sounds like what you are trying to accomplish.
This may help, I cast the degenerate dimension, the time field in your
table as it's own dimension using a view. Like so:
CREATE view [dim].[Date]
with schemabinding
as
select
convert(datetime, ca.AttemptDt,101) as [ Attempt Date]
,day(ca.AttemptDt) as [Day of Month]
,datename(m,ca.AttemptDt) as [Month Name]
,month(ca.AttemptDt) as [Month Number]
,datename(quarter,ca.AttemptDt) as [Quarter]
,year(ca.AttemptDt) as [Year]
from
dbo.tbl ca with (nolock)
then the fact
CREATE view [fact].[table]
with schemabinding
as
select
,convert(datetime, ca.AttemptDt,101) as [Attempt Date]
from
dbo.table t with (nolock)
Then you can include the key in both views and join on that. Or you
can join the two time stamps togther.
Hope this helps
On Nov 7, 1:51 pm, Dan <dan_willi...@xxxxxxxxxxxxxxxxxxxx> wrote:
OK, i'm relatively new to this OLAP/data warehousing business, so
please excuse my ignorance.
I'm using Microsoft Analysis Service 2000.
I have a fact table that has a datetime column that goes down to the
hour and minute.
I have created a shared time dimension that goes down to the day
level.
In my Cube Editor, I want to join my fact table to my shared time
dimension via the fact tables datetime column.
Is there anyway i can adjust the "INNER JOIN" my doing something like
this SQL:-
SELECT * FROM factTable INNER JOIN Time ON CAST(factTable.datetime,
smalldatetime) = Time.SmallDateTime
At present my cube returns no values, as my fact table contains dd/mm/
yyyy hh:mm's where as my shared time dimension only contains dd/mm/
yyyy's.
I don't want to have to create hours and minutes in my time dimension
table as there would be millions of rows.
Also, i don't really want to have to perform a DTS package on all my
fact tables to do this initial convertion, as i'd have to do this for
all my other future cubes, of which there could be many.
I've seen a lot of mention in examples and books, that shows a Primary
Key in the Time dimension table, which links to a foreign key in the
fact table. How are people populating the foreign keys? Is this
something that is done to populate a separate datawarehouse source?
I'm currently using my live OLTP database as the source. Should i be
doing this?
I could create a new SQL view to convert the dates without the hours
and minutes? Anyone know the performance issues with this?
Ideally, i'd like to be able to customize the link between fact table
and my dimensions?
Is this possible?
Thanks in advance for any suggestions.
Dan
.
- Follow-Ups:
- References:
- Prev by Date: How can OWC/Excel group by distinct (non-unique) level members?
- Next by Date: Re: Customize the joins between my Fact table and Dimensions?
- Previous by thread: Customize the joins between my Fact table and Dimensions?
- Next by thread: Re: Customize the joins between my Fact table and Dimensions?
- Index(es):
Relevant Pages
|
