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.

22 comments:

SSAS-Info.com said...

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

shivamun 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

demetriapauley0116 said...

女人性感帶.隋棠性感照.免費a片觀看,jp成人.嘿咻,限制級,性愛電影,性感睡衣.視訊辣妹,網路援交.台北援交,情色,hilive.ggyy線上漫畫,成人圖片區.一葉晴貼圖,黑澀會美眉琳恩.性愛教學.性感沙灘3,性感照片.主播情人視訊,豆豆出租情人視訊.兼職援交,性感辣妹,新浪視訊.免費做愛影片.性愛小遊戲,鹹濕視訊.微風成人,色情小說.黑澀會美眉的即時通.日本美女寫真集,免費 a.暗戀視訊,免費視訊聊天.gogo2sex,sex520貼影片.qq美美色網影片,彩虹頻道免費影片.情色性愛貼圖,人妻.sexy,援交留言版,自慰,成人短片.小魔女免費影片,自拍密錄館,免費成人影片.色貼工廠,免費成人影片,aa片,h片.免費試看av,論壇區,色情 網站.一對一視訊.av女優影片,aa片免費看影片.18成人avooo,情色 電影.色咪咪貼影片.av女優報報,免費試看.ez日本視訊女郎帳號.成人漫畫區,熊貓成人貼,嘟嘟成人網.台中援交友留言,武陵農場國民賓館.ez洪爺的家.ez極樂台灣18禁.ez甜心寶貝影片區.ez自慰台灣 情色.1111視訊.愛愛姿勢大全.性愛姿勢.閃亮天使520聊天室 .lover99秘密情人網 .全民愛愛區.影片-章子怡.AV女優聊天室.情色妹.網愛..妹妹視訊.AA片免費試看.成人遊戲區.本土 性愛 影片-章子怡.情色大網咖-.天堂-性愛區.視訊大奶.a片大網咖.黑澀會 視訊.hh002.ben10遊戲天堂-性愛區.s383情色大網咖-免費性愛影片.a383l影音城.plus28論壇

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

kentcabe said...

交友104速配網,視訊交友,成人韭南籽,18成人,ut男同志聊天室,成人圖片區,交友104相親網,0951成人頻道下載,男同志聊天室,成人貼圖,成人影片,tt1069同志交友網,成人視訊,aio交友愛情館,情色視訊,情色視訊,色情遊戲,交友戀愛小站,jp成人,熊貓貼圖,成人圖片,成人文章,正妹,成人小說,杜蕾斯成人,ut 聊天室,熊貓貼圖區,交友聊天找e爵,ol制服美女影片,777成人區,bt成人,女同志聊天室,貼圖片區,一葉情貼圖片區,6k聊天室,69成人,成人貼圖站,色情影片,聊天室ut,免費成人影片,

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...

Can anyone recommend the top performing Endpoint Security utility 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 system 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

ErikDMetzger said...

正妹牆成人視訊交友cleansing視訊交友lovelinessa片天堂台灣論壇成人a漫畫a片免費試看sogo色論壇sogo成人論壇383movie成人影城383影音live秀85cc成人片觀看交友戀愛進行室成人影片tt1069同志交友網成人視訊aio交友愛情館情色視訊情色視訊色情遊戲交友戀愛小站jp成人

Anonymous said...

[url=http://www.youtube.com/watch?v=pa8xW-AQGHA]HandySpionage[/url]

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

Anonymous said...

I just downloaded the update to my iphone and it erased all of music. I am in itunes but I do not know how to download the music back to my iphone. Could someone please help me out and provide as specific instructions as possible? Thanks.



________________
[url=http://unlockiphone22.com]unlock iphone[/url]

wqef456r said...

Such attentively manages your ~
.....................................................

FrederickI_Ferre馨儀 said...

More haste, less speed.......................................................

social27hub 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

Timmy 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