Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Analyzing an Accumulating Snapshot

Analyzing an Accumulating Snapshot

Analyzing an Accumulating Snapshot

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.

RE: Analyzing an Accumulating Snapshot

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

RE: Analyzing an Accumulating Snapshot

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.

RE: Analyzing an Accumulating Snapshot

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:

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close