Fact table design for "State Workflow Analysis": Analysis Services Dimensional modeling

Note: This is a work in progress article and may be updated regularly based on feedback.

There are many business analytical needs whose data is in the form of “State Workflow”. A state workflow consists of a set of states. There are a valid number of “State Transitions”, i.e. changing of one state to another. The state transitions happen by some event. There can be a valid number of events responsible for state transition.

The typical business problem that conforms to above situation is “Sales Pipeline” analysis. The typical queries that need to be resolved are:

  • How long it took to move from one state to another?
  • How many items moved from one state to another?
  • How much time spent in each state for a given period range?
  • What is the count of items in a given state for a period range vs another period range?
  • What is the aggregation of a related attribute with reference to state?

One typical analysis as presented by below graph is that for each day, what is the "% of Count of All opportunities" in each stages of sales pipeline.

Pipleline Evaluation by Stage

Or analyze the data using additional dimension such as "by Industry"

image

Or by "Booked Revenue"

image

The question is how we design our OLAP system to do such kind of analysis. Well, the trick lies in creating an intelligent fact table where we capture many of details at the time of ETL process itself rather than importing the transaction system "AS-IS" and hoping that some OLAP magic wand would answer all of our queries.

Let us understand it with some simple example which needs similar kind of analysis. In today’s rising gasoline cost, what better example is to understand how I drive or use my car?

If you think about operating a car, you will immediately think of several states that could be modeled in a workflow. Here are the states that I’ve chosen for this example:

  • Not Running: In this state, you are in the car but the engine is not running.
  • Idling: You’ve now started the engine, but you’re not moving.
  • Moving Forward: The car is moving forward.
  • Moving in Reverse: The car is moving in reverse.
  • Done with the Car: You are finished with the car.

There are many other states that you could model, but this list is enough to provide a substantial example. The next step is to identify the events that can occur while you are in each state.

Below table lists the events that are allowed for each state, along with the planned state transitions as each event is handled.

image

(Well, I still drive a manual gear car, for the sheer pleasure of it)

Lets look at our typical transactional system. Lets say I have an advanced tracking system installed in my car which records the states and events in a file. Typically, the transactional data recorded is very simple in the following structure:

image

Now, the typical analytical questions to understand my driving habits are:

On a given day, month or year:

  • How many miles I drive in “Moving Forward”+ “Moving in Reverse” or individual state?
  • How many times I “Apply Brake” while in “Moving Forward” state?
  • How many miles I drive in “Gear Four” event in “Moving Forward” state? (higher the gear, better is the mileage)
  • How much time is spent in “Idling” state for “Traffic Wait” event? (higher the time, higher is the fuel wastage)
  • How many times, I do “Start the engine” event from “Not Running” state?
  • How many times, I do “Beep Horn” even? (Am I compulsive honker?)
  • How many times I change gears while in “Moving Forward” state?
  • What is my average speed per trip?
  • What is my average speed per trip while in “Moving Forward” state?
  • Many more you can think of.

To answer the above analytical questions from our transactional data presented above is difficult for the end users. If a cube is created directly on the above transactional data, the MDX queries would be even more difficult. The point is, we want our end users to do more of analysis rather than spend effort on arranging the data prior to analysis.

The trick is to setup a ETL system which produces a synthesized fact table for data analysis. Lets take the below fact table which presents the same transactional data provided above:

image

From the above synthesized fact table, lets review if we can get the answers of our typical analytical questions to understand my driving habits. I think it should answers all of them.

We can create an OLAP cube on top of this fact table and using aggregation functions, answer the same analytical questions using MDX queries or expose the data to some OLAP client. Well, this is a topic for some other day.

14 comments:

Anonymous said...

Pingback. Link to this post was added in [SSAS Articles]/[Design] section.

Unknown said...

Ajit,You profile looks interesting basically the pipe line analysis story is quite interesting. I am just looking for the next part of the story , How you build the cube and what kind of best design would fit for this.

this is shiva munigela, working in similar area MSBI.
Its really great to be in touch with you.
my personal mail
munigela99@yahoo.com

Regards,
Shiva

BI_Buff said...

The article is great, how does one model a cube based on the the transactional table you have presented.

Thank you

Anonymous said...

Good day !.
You re, I guess , perhaps curious to know how one can make real money .
There is no initial capital needed You may commense earning with as small sum of money as 20-100 dollars.

AimTrust is what you need
The company incorporates an offshore structure with advanced asset management technologies in production and delivery of pipes for oil and gas.

Its head office is in Panama with offices everywhere: In USA, Canada, Cyprus.
Do you want to become really rich in short time?
That`s your choice That`s what you desire!

I feel good, I began to take up income with the help of this company,
and I invite you to do the same. It`s all about how to choose a proper partner who uses your savings in a right way - that`s it!.
I take now up to 2G every day, and my first investment was 500 dollars only!
It`s easy to get involved , just click this link http://ypibotahug.mindnmagick.com/fexadak.html
and go! Let`s take our chance together to get rid of nastiness of the life

Anonymous said...

Hi !.
You may , perhaps curious to know how one can manage to receive high yields .
There is no need to invest much at first. You may start to get income with as small sum of money as 20-100 dollars.

AimTrust is what you need
The company represents an offshore structure with advanced asset management technologies in production and delivery of pipes for oil and gas.

Its head office is in Panama with affiliates everywhere: In USA, Canada, Cyprus.
Do you want to become a happy investor?
That`s your chance That`s what you wish in the long run!

I`m happy and lucky, I began to take up income with the help of this company,
and I invite you to do the same. If it gets down to select a correct partner utilizes your funds in a right way - that`s AimTrust!.
I take now up to 2G every day, and my first deposit was 1 grand only!
It`s easy to get involved , just click this link http://uvabyvyjo.kogaryu.com/dakunyg.html
and lucky you`re! Let`s take our chance together to become rich

Anonymous said...

Hello !.
You may , perhaps curious to know how one can manage to receive high yields .
There is no initial capital needed You may start earning with as small sum of money as 20-100 dollars.

AimTrust is what you haven`t ever dreamt of such a chance to become rich
The company represents an offshore structure with advanced asset management technologies in production and delivery of pipes for oil and gas.

Its head office is in Panama with structures everywhere: In USA, Canada, Cyprus.
Do you want to become a happy investor?
That`s your chance That`s what you really need!

I feel good, I started to get real money with the help of this company,
and I invite you to do the same. It`s all about how to choose a proper companion utilizes your funds in a right way - that`s the AimTrust!.
I earn US$2,000 per day, and my first deposit was 1 grand only!
It`s easy to get involved , just click this link http://lexenacad.1accesshost.com/kojyzo.html
and lucky you`re! Let`s take our chance together to get rid of nastiness of the life

Anonymous said...

Good day !.
You may , probably curious to know how one can reach 2000 per day of income .
There is no initial capital needed You may begin earning with as small sum of money as 20-100 dollars.

AimTrust is what you haven`t ever dreamt of such a chance to become rich
The firm incorporates an offshore structure with advanced asset management technologies in production and delivery of pipes for oil and gas.

Its head office is in Panama with structures everywhere: In USA, Canada, Cyprus.
Do you want to become really rich in short time?
That`s your chance That`s what you wish in the long run!

I`m happy and lucky, I started to get real money with the help of this company,
and I invite you to do the same. If it gets down to choose a proper companion who uses your funds in a right way - that`s AimTrust!.
I make 2G daily, and my first deposit was 1 grand only!
It`s easy to get involved , just click this link http://ykyticumec.easyfreehosting.com/irupaty.html
and lucky you`re! Let`s take this option together to become rich

Anonymous said...

Can anyone recommend the robust Remote Management system for a small IT service company like mine? Does anyone use Kaseya.com or GFI.com? How do they compare to these guys I found recently: [url=http://www.n-able.com] N-able N-central event management
[/url] ? What is your best take in cost vs performance among those three? I need a good advice please... Thanks in advance!

Anonymous said...

Good day !.
You may , probably very interested to know how one can make real money .
There is no need to invest much at first. You may begin earning with as small sum of money as 20-100 dollars.

AimTrust is what you need
The company represents an offshore structure with advanced asset management technologies in production and delivery of pipes for oil and gas.

It is based in Panama with offices around the world.
Do you want to become an affluent person?
That`s your choice That`s what you wish in the long run!

I feel good, I began to get income with the help of this company,
and I invite you to do the same. It`s all about how to select a proper companion utilizes your savings in a right way - that`s the AimTrust!.
I earn US$2,000 per day, and my first deposit was 1 grand only!
It`s easy to join , just click this link http://exyribasy.lookseekpages.com/nahipy.html
and lucky you`re! Let`s take this option together to feel the smell of real money

Anonymous said...

ankur chatopadhyay timesbub legendary categorical huei resolutions refer asses grounds roadhouses
semelokertes marchimundui

Anonymous said...

An Leaked Lady GaGa Track was Unsealed this afternoon with no traces of where it originated from.
Some say that it was found in GaGa's Record Label's headquarters.

More info at http://ladygagaunreleased.blogspot.com

Free Download of the single at http://tinyurl.com/gagaunreleased

Unknown said...

Hi! We would like to introduce you to a new BI Web TV Channel, BizIntelligence.TV – A new and innovative way to let industry leaders express and gain access to thought-leadership on Business Intelligence. The goal of the BizIntelligence.TV program is to start an ongoing conversation by providing compelling content from peers and industry stars.

Here is just one of our featured interviews With Microsoft COO, Kevin Turner
http://www.youtube.com/watch?v=PB75HI8Q8nA

Thanks and we would love your feedback!

Sam Kane said...

Here are this and some other articles on SSAS Dimensional Modeling:

http://ssas-wiki.com/w/Articles#Dimensional_Modeling

Unknown said...

Great analysis for "State Workflow Analysis", thank you for sharing.

moving company

My Articles

Design

Cube structure optimization for MDX query performance in Analysis Services 2005 SP2: Tips for Parent Child Hierarchies usage

Fact table design for “State Workflow Analysis”: Analysis Services Dimensional modeling

Handling inter-dimensional members dependency and reducing cube sparsity using reference dimensions in Analysis Services 2005 SP2 : Cube design tip

Identifying intra-dimensional members relationship and reducing cube sparsity in Analysis Services 2005 SP2 : Cube design tip

Leaves() : An example to understand it for both regular hierarchies as well as parent child hierarchies

Aggregation design: useful tips

Level based attribute hierarchy: MDX query performance woes in SQL Server 2005 SP2: Is it fixed in post SP2 hotfix?

Parent child hierarchy to level base hierarchy conversion: hiding placeholder dimension members in client application

Trouble / Troubleshooting

Aggregate(), Sum() functions using calculated members does not work in Analysis Services 2005 SP2 (9.00.3042.00 version) but works in Analysis Services 2000 SP4

Analysis Services 2005 migration tool: Custom member formula issues in migrated database

Cube Partitions: Fact table not listing in Business Intelligence Development Studio in partition wizard

Analysis Services 2005: Many-to-Many relationship does not support unary operators with parent-child dimension

MDX

NextAnalytics and MDX : Part 1 - Swap Cells with Row Labels

Selecting dimension's default member based on a member property

Sorting members on member codes / member properties

Time Dimension: How to set Default Member to Current Month

Setting dynamic default member in dimension X based on the current member of dimensions Y

ADOMD.NET

Code : utility code for converting cellset to a data table

Others

Google specialized search for Analysis Services and MDX web resources integrated in my blog

Art of reading MDX articles

MDX Expression Builder : Need for a tool making it easier for functional users to write MDX expressions, queries.

Blogroll