Designing Periodic Snapshot Tables

Hello,

Currently i have a dataset which contains subscriber info with his avialable balances as below:

SUBSCRIBER_ID | BALANCE1_ID | AVAILABLE_BALANCE_1 | BALANCE2_ID | AVAILABLE_BALANCE_2 | BALANCE3_ID | AVAILABLE_BALANCE_3

#################################################################
1a | 1 | 100 | 2 | 30 | 3 | 25
2b | 3 | 30 | 5 | 18 | 2 | 45
3c | 2 | 55 | 1 | 65 | 5 | 20

we are getting this info on a daily basis at the end of the day as a periodic snapshot.

My question is how can i model this data in my data warehousing system.

Here the BALANCE_ID can vary for each subscriber as you can see.

If i want to query for a subscriber on a particular day what was his available balance with balance_id as 1 , then i need to store each column as a row for each balance.

Actual number of balances for a subscriber is 40 and number of subscribers is around 0.4 Million. For example purpose i have used only 3 balances.

How can i design an effective periodic snapshot for this ?

Can anyone help me on this ?

Thanks,
Suhrid Ghosh

If you want to hold this level of detail then you'll need to create a fact table that holds a record for each balance - I wouldn't try and flatten out the data so that one fact record holds all 40 balances for a subscriber

I would just create a fact table that looks like this:

Date_ID
Subscriber_ID
Balance_ID
Balance_Amount

If you have balances that don't change every day then you could add eff. start and end dates to the fact table.

However, I'm wondering why you are using a dimensional model design to answer a question like "for subscriber on a particular day what was his available balance with balance_id as 1"? A Dimensional model DW is designed to provide aggregated/filtered results from large datasets - it is not for transactional reporting which is what you are doing - picking a single record from your dataset.

Regards,

Hello Nick,

Thanks for looking into to this. I will create a fact table that holds a record for each balance for a day. In this case the amount of records for 1 million subscribers with 40 balances for a day will hold 40 million records which will grow drastically for a year or so.

Do you feel this is the only effective model to achieve it considering space requirements.

Thanks,
Suhrid Ghosh

Hi,
each fact record will be pretty small so while you have a large number of records they won't actually take up much space.
If not every balance changes every day then I would implement start and end dates on your fact record so you only create a new record when a particular balance for a particular subscriber changes.

Regarding whether I think this is an effective model then the answer is no - but mainly because I don't think any dimensional model would be an effective way of delivering what you want. DMs are there to support analytical queries and what you are doing is a transactional query not an analytical query. Therefore your balances should be in in a normal relational table with a primary key of Subscriber_Id + Date and columns for each balance ID and balance amount i.e. basically what you had for your dataset in your first post.
I'm not clear why you want to put this data into a dimensional model if all you want to retrieve is single records - you have those single records in your source and putting it into a DM format is giving you no benefit so I'm not sure why you're bothering. Do you have another use case for this data?

Regards,

What are some real life examples of the things represented by a balance id? How many balance id's are there, 40?