SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Star Schema - Avoiding Cross Joins
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

flamblaster
Constraint Violating Yak Guru

380 Posts

Posted - 11/17/2012 :  20:12:33  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 12/02/2012 :  02:31:12  Show Profile  Reply with Quote
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/

Go to Top of Page

flamblaster
Constraint Violating Yak Guru

380 Posts

Posted - 12/03/2012 :  15:23:47  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/04/2012 :  01:15:04  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000