June 8, 2011 at 3:27 am
Firstly, I must apologize if this problem has been solved before. I have searched but I'm unsure of the topic to search for.
So I decided to create the common Fictitious Video Store example.
The video store offers a 5 year subscription, paid monthly, where the client can have access to as many video as he/she wishes during that month. On the annual anniversary, the premium increases by 5%. A client can cancel their subscription, given a months notice. (But we can get to that in some other post)
My business problem is to measure the total year/quarterly/monthly money value of my video store subscriptions and the count of subscriptions. We are looking to track how the subscriptions increase over time. Note, we are not looking at the income monthly, but the total value of subscription base over time. So, it's June 2011 now. What was the value of all the subscriptions in January, February? What was the value of all the subscriptions in the 1st Quarter and the 2nd Quarter? What was the value of all the subscriptions last year?
The problem that I cannot get around is that, I can track 1 fact entry per subscription and when we signed them but when it comes to the upgrades, i.e. changes in subscription, how do I track the changes in the money values without doubling the count in subscriptions?
Thanks in advance for any help.
Gary
June 8, 2011 at 6:02 am
Arthur Gary (6/8/2011)
Firstly, I must apologize if this problem has been solved before. I have searched but I'm unsure of the topic to search for.So I decided to create the common Fictitious Video Store example.
The video store offers a 5 year subscription, paid monthly, where the client can have access to as many video as he/she wishes during that month. On the annual anniversary, the premium increases by 5%. A client can cancel their subscription, given a months notice. (But we can get to that in some other post)
My business problem is to measure the total year/quarterly/monthly money value of my video store subscriptions and the count of subscriptions. We are looking to track how the subscriptions increase over time. Note, we are not looking at the income monthly, but the total value of subscription base over time. So, it's June 2011 now. What was the value of all the subscriptions in January, February? What was the value of all the subscriptions in the 1st Quarter and the 2nd Quarter? What was the value of all the subscriptions last year?
The problem that I cannot get around is that, I can track 1 fact entry per subscription and when we signed them but when it comes to the upgrades, i.e. changes in subscription, how do I track the changes in the money values without doubling the count in subscriptions?
Thanks in advance for any help.
Gary
So - how have you tried to tackle this problem so far? What logic have you tried, and what's your current approach to trying to solve it?
-Ki
June 8, 2011 at 6:23 am
I've created the data tables for this scenario. However, as soon as I get to the upgrades, I begin to stumble.
A simplified version of the Data Model is as follows:
DimensionSubscription
SubscriptionKey
SubscriptionDescription
StartDate
EndDate
DimensionTime
TimeKey
Time Month\Quarter\Year
FactSubscription
SubscriptionKey
SubscriptionUpgradeKey*
TimeKey
PremiumAmount
Count
The problem I foresee in the cube is that: 1) If I store a fact every time the subscription upgrades, using the monthly time attribute will work fine. Remove the time dimension or roll-up to quarter/year and we have an additive problem. The YTD sum of the premiums and the YTD count of the subscriptions will be wrong.
I just can't get my head around how to give the flexibility while keep the numbers accurate.
I also toyed with linked a separate measure group for upgrades, but how would I sum the true total value of subscriptions at a point in time dealing with upgrades and counts.
This post came close to answering my questions, but I'm not clear on the solution.
http://forum.kimballgroup.com/t901-how-to-track-scd-type-2-for-accumlating-or-periodic-snapshot
Thanks,
Gary
June 8, 2011 at 3:46 pm
Arthur Gary (6/8/2011)
i.e. changes in subscription, how do I track the changes in the money values without doubling the count in subscriptions?
mmhh... you may have it backwards. Facts do not change - that's why they are factual.
Would you mind in researching Slowly Changing Dimensions a.k.a SCD? Specially take a look at Type 3 SCDs.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 9, 2011 at 12:13 am
When the numbers in your fact tables don't add up, then you haven't set the grain properly. A subscription fact does not seem to me to be the same as a transaction fact that has an entry every time a subscription is charged. It appears you are trying to reuse similar data instead of adding some redundancy to your design.
On the other hand, this does sound a lot like a homework problem....
LinkedIn - http://www.linkedin.com/in/carlosbossy
Blog - http://www.carlosbossy.com
Follow me - @carlosbossy
June 9, 2011 at 2:52 am
Thank you very much for your advice. I do agree, I'm not thinking about this correctly and battling to solve it. That is why I've turned to forums after many nights of reading about dimensional modelling and still not finding a similar scenario.
I've had a look at the type 3 SCD. I'm still not sure if that will solve my problem.
---
The "Fact" is that a new subscription was taken out. And if I'm not mistaken, if the amount is a measure, it's a fact that it'll increase. We want to measure the volumes and monthly premium growth. And the only trick is that the premium for the subscription can increase annually from the start date. I can just dump that data into a relational table and use some SQL to pull that data out to a report, but I want to put this into a dimensional model and design a cube for the power users to analyze the information themselves. It's this flexibility without loosing accuracy that is giving me a headache.
For the financial transactions, we do have another cube that records that video monetary transactions. That's easy because it's a straight forward collection on the subscription monthly which balances financially to the bank statements.
Thank you again.
G
June 9, 2011 at 9:04 am
Is there a reason that you must have a new cube if you already report transactional data in another cube? "Subscriptions" sounds to me like a slowly changing dimension (type 2). Even if you end up with a model that treats subscriptions like a monthly snapshot balance fact, you could add the new fact to the existing cube in order to leverage the existing dimensional BUS.
Muddying the waters,
Chris
Chris Umbaugh
Data Warehouse / Business Intelligence Consultant
twitter @ToledoSQL
June 10, 2011 at 5:22 am
Chris Umbaugh (6/9/2011)
Is there a reason that you must have a new cube if you already report transactional data in another cube? "Subscriptions" sounds to me like a slowly changing dimension (type 2). Even if you end up with a model that treats subscriptions like a monthly snapshot balance fact, you could add the new fact to the existing cube in order to leverage the existing dimensional BUS.Muddying the waters,
Chris
Hello Chris,
I think you have answered my question. The monthly snapshot fact table will do the trick. With some smart MDX and measures, I should be able to prevent incorrect calculates .. or at the very least some user training.
Thank you all ...
June 13, 2019 at 4:35 am
This was removed by the editor as SPAM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply