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.
| 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/
|
|
|
|
|
|
|
Star Schema - Avoiding Cross Joins