Star Schema Design

[BINewbie]

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.

 

[johnherman]

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

 

[BINewbie]

its tracking the last time the table was loaded.

 

[johnherman]

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