datawarehouse modeling

View previous topic View next topic Go down

datawarehouse modeling

Post  Tesla0306 on Sat Apr 16, 2011 7:13 am

I'm modeling data warehouse for some kind of helpdesk system also this si my graduate work and first project. Question i have is it possible to connect two fact tables. For example fact table Problem(FactProblem) stores informations about problems that customers have reported than it generates one or more work tasks. Work tasks are stored in FactRadniZadatak table. These facts have common dimensions like status, date, employee...

I want to be able to answer what tasks has foloving problem generated by drill down
Here is dw diagram

FactRadniZadatak -> worktask
DimDjelatnik -> employee
DimKlijent -> customer
DimTipGreske -> problem type
DimRazvojnoPodrucje -> department
DimPrioritet -> task priority





Thanks for replay

Tesla0306

Posts : 1
Join date : 2011-04-16

View user profile

Back to top Go down

Re: datawarehouse modeling

Post  ngalemmo on Sat Apr 16, 2011 12:34 pm

Facts are combined by joining two sub-queries. Each sub query aggregates one of the fact tables along dimensions common to both fact tables. You then join the two aggregates.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: datawarehouse modeling

Post  ObjectiveC on Mon Apr 18, 2011 3:17 am

ngalemmo wrote:Facts are combined by joining two sub-queries. Each sub query aggregates one of the fact tables along dimensions common to both fact tables. You then join the two aggregates.

Hello ngalemmo,

Will this not be terrible on performance ?! I mean isn't it better/faster to just put it all in one big fact table with some junk dimensions, since both tables already have common dimensions.

ObjectiveC

Posts : 25
Join date : 2011-03-18

View user profile

Back to top Go down

Re: datawarehouse modeling

Post  ngalemmo on Mon Apr 18, 2011 3:14 pm

Not really, and performance isn't the issue. The relationship between any two fact tables should always be assumed to be many-to-many. By aggregating each table along common dimensions reduces that relationship to one-to-one, which allows you to combine the two.

Alternately, you can union the two queries either before or after aggregation. This does the same thing, may perform a little better and avoids the need to do a full outer join.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: datawarehouse modeling

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum