×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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

Star Schema Design

Star Schema Design

Star Schema Design

(OP)
This is my first attempt at designing a star schema for a reporting requirement and I'm new to this entire concept.
I have the requirement to report kwh used for a service in a month. This kwh can be broken down into different "buckets" based on the type of rate the service is billing on. I need to know how much kwh was in each of those buckets in a month. The issue I ran into is that rate can change retroactively, changing how the kwh is bucketed.

So i have this.
Fact_Service_Daily_Usage
UsageKey
UsageDate
RateKey
ServiceKey
TotalUsage
InsertDatetime
LineageKey

An example of this usage could be....

10/01/2019
Bucket 1
123
100 KWH
---------------------
10/01/2019
Bucket 2
123
300 KWH

But then the business realized the service was on the wrong rate and changes it. Then the kwh would look like this for the same service.

10/01/2019
Bucket 1
123
400 KWH

How can i handle this in a fact table?? with the changes.
Please help.

RE: Star Schema Design

At first glance, I thought your lineage key was going to be used to track changes. If not, what is the lineage key?

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity


RE: Star Schema Design

(OP)
its tracking the last time the table was loaded.

RE: Star Schema Design

Do you want to have a separate table for the inactive records? If yes, then when you have a rate change, write the old record to an inactive table and update the current record with the new values. You may want to add an indicator/flag to indicate that the rate has been changed.

If you want to store all of this in the same table, then you would need to have an inactive flag for the record with the older rate.

There are probably a half dozen other ways to handle this. Do you understand the concept of Type 1, Type 2, and Type 3 Slowly Changing Dimensions? If not, research that topic. Let me know if you want more help after you've studied SCDs.

With this scenario, writing the inactive record to a separate inactive table would be similar to a Type 1 change. How often does the rate change retroactively? That might be a factor to consider as you choose your solution (type 1,2,3, or something in between).

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity


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