sql server data warehouse:
Hi.
I guess.
Kimbal says this when the cube developer uses the same table for both dimensions (Order Date and Shipping Date).This solution is comfortable because you may load all dates in your DW to only one dimension date table.
The problem is that the cube joins with only one table for two date dimensions will force the following SQL when processing.
select * from FACTTABLE, DATEDIMENSIONTABLE
where FACTTABLE.OrderDate = DATEDIMENSIONTABLE.DateKey
and FACTTABLE.ShipDate = DATEDIMENSIONTABLE.DateKey
You may use VIEWS or You may use the same table for this solution, but you have to force an alias to the date dimension table including it on the schema while building the cube in the Editor.
I hope it may help.
Best Regards.
--
Fernando Ponte
[quoted text, click to view] "Ian Boyd" wrote:
> Quoting from The Data Warehouse Toolkit by Kimball:
>
> <quote ignoreSpellingMistakes="Yes">
> By now we all know that a date dimension is found in every fact table
> because we are always looknig at performance over time. In a
> transaction-grained fact table, the primary date column is the transaction
> date, such as the order date. Sometimes we also discover other dates
> associated with each transactin, such as the requested ship date for the
> order.
>
> Each of the dates should be a foreign key in the fact table. However, we
> cannot simiply join these two foreign keys to the same date dimension table.
> SQL would interpret such a two-way simultaneious join as requiring both the
> dates to be identical, which isn't very likely.
>
> Even though we cannot literally join to a single dimension table, we can
> build and administer a single date dimenison table behind the scenes. We
> create the illusion of two independent date tables by using views. WE are
> careful to uniquely label the columns in each of hte SQL views. For example,
> order month should be uniquely labeled to distinguish it from requiested
> ship month.
>
> CREATE VIEW ORDER_DATE (ORDER_DATE_KEY, ORDER_DAY_OF_WEEK, ORDER_MONTH...)
> AS SELECT DATE_KEY, DAY_OF_WEEK, MONTH, ... FROM DATE
>
> and
>
> CREATE VIEW REQ_SHIP_DATE (REQ_SHIP_DATE_KEY, REQ_SHIP_DAY_OF_WEEK,
> REQ_SHIP_MONTH...)
> AS SELECT DATE_KEY, DAY_OF_WEEK, MONTH, ... FROM DATE
>
> We now have two unique date dimensions that can be used as if they were
> independent with completely unrelated contraints.
> </quote>
>
>
> He says, "We cannot simiply join these two foreign keys to the same date
> dimension table. SQL would interpret such a two-way simultaneious join as
> requiring both the dates to be identical, which isn't very likely."
>
> Why is that?
>
> If i have a Order Line Items fact table:
> TABLE OrderItems (
> Customer (FK)
> OrderDate (FK)
> RequestedShipDate (FK)
> Quantity int)
>
> why can't i:
>
> SELECT *
> FROM OrderItems
> INNER JOIN Dates OrderDates
> ON OrderItems.OrderDate = OrderDates.DateKey
> INNER JOIN Dates ShipDates
> ON OrderItems.RequestedShipDate = ShipDates.DateKey
>
> SQL doesn't require both OrderDate and RequestedShipDate to be the same.
>
> So i don't get what he's saying.
>
>
Quoting from The Data Warehouse Toolkit by Kimball:
<quote ignoreSpellingMistakes="Yes">
By now we all know that a date dimension is found in every fact table
because we are always looknig at performance over time. In a
transaction-grained fact table, the primary date column is the transaction
date, such as the order date. Sometimes we also discover other dates
associated with each transactin, such as the requested ship date for the
order.
Each of the dates should be a foreign key in the fact table. However, we
cannot simiply join these two foreign keys to the same date dimension table.
SQL would interpret such a two-way simultaneious join as requiring both the
dates to be identical, which isn't very likely.
Even though we cannot literally join to a single dimension table, we can
build and administer a single date dimenison table behind the scenes. We
create the illusion of two independent date tables by using views. WE are
careful to uniquely label the columns in each of hte SQL views. For example,
order month should be uniquely labeled to distinguish it from requiested
ship month.
CREATE VIEW ORDER_DATE (ORDER_DATE_KEY, ORDER_DAY_OF_WEEK, ORDER_MONTH...)
AS SELECT DATE_KEY, DAY_OF_WEEK, MONTH, ... FROM DATE
and
CREATE VIEW REQ_SHIP_DATE (REQ_SHIP_DATE_KEY, REQ_SHIP_DAY_OF_WEEK,
REQ_SHIP_MONTH...)
AS SELECT DATE_KEY, DAY_OF_WEEK, MONTH, ... FROM DATE
We now have two unique date dimensions that can be used as if they were
independent with completely unrelated contraints.
</quote>
He says, "We cannot simiply join these two foreign keys to the same date
dimension table. SQL would interpret such a two-way simultaneious join as
requiring both the dates to be identical, which isn't very likely."
Why is that?
If i have a Order Line Items fact table:
TABLE OrderItems (
Customer (FK)
OrderDate (FK)
RequestedShipDate (FK)
Quantity int)
why can't i:
SELECT *
FROM OrderItems
INNER JOIN Dates OrderDates
ON OrderItems.OrderDate = OrderDates.DateKey
INNER JOIN Dates ShipDates
ON OrderItems.RequestedShipDate = ShipDates.DateKey
SQL doesn't require both OrderDate and RequestedShipDate to be the same.
So i don't get what he's saying.
[quoted text, click to view] > The problem is that the cube joins with only one table for two date
dimensions will force the following SQL when processing.
>
> select * from FACTTABLE, DATEDIMENSIONTABLE
> where FACTTABLE.OrderDate = DATEDIMENSIONTABLE.DateKey
> and FACTTABLE.ShipDate = DATEDIMENSIONTABLE.DateKey
It doesn't though, at least not in SQL Server, or ANSI-92.
SELECT
FactTable.Fact, OrderDates.Date AS OrderDate, ShipDates.Date AS ShipDate
FROM FactTable
INNER JOIN Dates OrderDates
ON FactTable.DateKey = OrderDates.DateKey
INNER JOIN Dates ShipDates
ON FactTable.DateKey = ShipDates.DateKey
Would give me:
Fact OrderDate ShipDate
==== ====== ======
A 20040612 20040630
B 20040323 20040723
So either i don't understand the fundamental nature of what it means to join
two tables together on a common column, or he's not explaining what quirk
he's referring to, or he's wrong.
