Analyzing an Accumulating Snapshot

[Poster600]

I'm having some trouble figuring out how to do certain types of analysis on my Accumulating Snapshot fact table (using a BI tool, for example).

Let's say I have a classic "Order Pipeline" fact table and it includes, say, Order Submitted Date, Order Received Date, Items Prepared For Shipping Date, Items Shipped Date, etc. Each of these is a foreign key to a view of my date dimension - OrderSubmittedDateDimension etc.

Now the report I would like to pull would tell me, for each week of the year, how many orders were submitted in that week, how many orders were received in that week, how many orders were shipped in that week, etc. (for argument's sake all on one line of a report, with week number down the left-hand column, then a column for #submitted, a column for #received, etc.)

The problem is that that date (the week) does not refer to any of my date dimensions in particular - it's kind of a combination of all my date dimensions - "week" in the abstract, not OrderSubmittedWeek, OrderReceivedWeek, etc. So what dimension shall I tell my BI tool to put in that left column?

Am I thinking about this wrong? Is there a better way to approach it? Thanks for your help.

 

[johnherman]

You should have four joins to the date dimension, one for each of the date dimensions you mentioned.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[Poster600]

Thanks for your reply, johnherman.

Do you mean that I should join 4 times to the date dimension without using a separate view/synonym/alias for each one? Is that even possible? Currently I'm joining to 4 views of the date dimension.

The problem is that then the BI tool sees it as 4 different dimensions - so I can't combine them into a single "date" dimension. What I want, essentially, is to analyze all 4 by a single date dimension - like this: for week number 1, I had this many orders submitted, this many orders received, this many orders shipped, etc.; for week 2 I had this many orders submitted, this many received, this many shipped; for week 3 ... etc. But because I join through views/synonyms/aliases, which date dimension am I using to analyze the weeks?

See my problem?

Thanks again for your help.

 

[Bobje]

you can do basically two type of analyses on this
1/ which orders that are submitted in week 1 are prepared,shipped, ... (flow of the orders)
2/ how many orders are submitted in week 1, how many are prepared in week 1,... (nr per activity)

You seem to have built you're fact table to support nr1,
the only way (that i found as i struggled with it as well :s) is to make unions; having one query go for 1 activity with it's date. Performance is obviously slow.

There is another way to built you're fact table, but this is highly depending on what you're granularity is, which comparisons you want to make,...

lets says in query 1 you always want to select on order Submitted date as reference to the flow.
you can then built a table with following fields:
OrderSubmittedDate
NrOfOrderSubmitted
EventCode
EventDate
EventNr

So eventcode is Order Received Date, Items Prepared For Shipping Date,...
EventDate is the date of the event, eventnr is the amount of orders it's applicable to.

This table will allow you to make the 2 queries, but if you don't want to compare only with orders submitted you will have to change this into ReferenceEvent and then fill it with all the events you want which comes down to duplicating everything.

Obviously you might go from you're current fact table to the one I described and keep both