Star Schema - Avoiding Cross Joins

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Star Schema - Avoiding Cross Joins

Author

Topic

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-11-17 : 20:12:33

Say you have a star schema and for simplicity, let's say you have the following dimensions and facts:

DimRoutes (RouteKey, RouteName, RouteGroup)
DimDate (DateKey, SDate, SMonth, SYear)
FactRides (RidesKey, RouteKey, DateKey, NumRides)

Now, let's say you have the following data:

Select *
from DimRoutes

Results:

RouteKey RouteName RouteGroup
1 Flex-1 FLEX
2 Flex-2 FLEX
3 Flex-3 FLEX

Select *
from DimDate
where SDate>='2012-01-01' and Sdate<'2012-04-01'

Results: (Abbreviated)

DateKey SDate SMonth SYear
1 2012-01-01 January 2012
2 2012-01-02 January 2012
...
...
91 2012-04-01 April 2012

Now, let's say that I have the following records in the FactRides table that occurred on one of these three routes between 2012-01-01 and 2012-03-31:

RidesKey RouteKey DateKey NumRides
1 1 1 50 --Flex-1 on 1-1-2012
2 2 1 100 --Flex-2 on 1-1-2012
3 2 31 50 --Flex-2 on 2-1-2012
4 2 60 100 --Flex-2 on 3-1-2012
5 3 60 110 --Flex-3 on 3-1-2012

So my issue is that if I run a query against these tables, I need for the Route data to persist across the date range regardless of whether or not there were rides for that route in the month.

Example of output I'd want to see from the data above:

RouteName SMonth SYear NumRides
Flex-1 Jan 2012 50
Flex-2 Jan 2012 100
Flex-3 Jan 2012 0
Flex-1 Feb 2012 0
Flex-2 Feb 2012 50
Flex-3 Feb 2012 0
Flex-1 Mar 2012 0
Flex-2 Mar 2012 100
Flex-3 Mar 2012 110

I realize that I can use a cross join so that Route persists, but I was wondering if there are any other techniques we could use than that. A cross join will be difficult to implement for users in Crystal Reports; so I'd like to set up the star schema so that users aren't having to do more sophisticated joins than an inner join wherever possible.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-02 : 02:31:12

you've to have a cross join if you want routes to be retrieved regardless of them being present for a date.
I didnt understand why user have to implement this.
You need to implement this as logic in backend query for crystal reports. Users just need to pass date range and it will provide them with result without letting them aware of cross joins that work on backend

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-12-03 : 15:23:47

Hi Vis,

Thanks...I was hoping there was some way to avoid a cross join, but I understand the need. In order to avoid having the user employ a cross join, I'm guessing I'd need to have a view created that performs the cross join. I'm creating a star schema and was trying to stay away from having to give the users views to use, but if there's no other way, I guess that's what needs to be done!

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-04 : 01:15:04

quote:
Originally posted by flamblaster

Hi Vis,

Thanks...I was hoping there was some way to avoid a cross join, but I understand the need. In order to avoid having the user employ a cross join, I'm guessing I'd need to have a view created that performs the cross join. I'm creating a star schema and was trying to stay away from having to give the users views to use, but if there's no other way, I guess that's what needs to be done!

Thanks

if the requirement is to retrieve matrix kind of result for all routes for all dates. so you may create a table if you dont want it to be generted each time otherwise you need cross join to get it on the fly.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources