Modeling Historical Data

[ViniciusScur]

Hello,

I'm in a situation that I don't know the best way to do it, I'm looking for opinions to see if there is a better way.

The source of origin brings me only current data, without a historical data, which I need for the purpose of BI.

I thought of two solutions, both have their downsides.

1º)
I created two fields in the fact table ('initial effective date' and 'end effective date').

The first time imported, I inform the current date for the 'initial effective date' (Current date: 07/17/2021) and for the 'end effective date' I put 12/31/2999.

When there is a change in any field in this table, I update the 'end effective date' of the record already imported to the date before the current date (Current date: 07/19/2021 then it will be 07/18/2021), then I create a new record with the rule informed previously ('initial effective date' = 07/19/2021 and 'end effective date' = 12/31/2999).

With this I create a data history since the first time imported.

Example of fact table data:
'sk sells' - 'initial effective date' - 'end effective date' - 'total sales'
    1       -         07/17/2021        -       12/31/2999       -     5,000
    2       -         07/17/2021        -       07/18/2021       -     3,000
    2       -         07/19/2021        -       12/31/2999       -      300

Problem this way, is that I have to treat the values by measures, taking the selected date in the calendar table and filtering by these effective dates ('initial effective date', 'end effective date').
This takes time because I have a database of millions of rows and I need to do this filter for every measure, even if it's a normal sum.

2º)
Another option I thought to solve the problem above was to create an intermediary table between the calendar and the fact table. This temporary table links the effective dates with each record active on that date. For example:

In the calendar table we have the following dates:
'sk calendar' - 'date calendar'
  01012021   - 01/01/2021
  01022021   - 01/02/2021
  01032021   - 01/03/2021

In the fact table we have the following data:
'sk sells' - 'initial effective date' - 'end effective date' - 'sk city' - 'total sales'
    1        -        01/01/2021         -     12/31/2999         -   10      -       50
    2        -        01/02/2021         -     01/02/2021         -   9        -      100
    3        -        01/03/2021         -    12/31/2999          -   9        -      10

In the intermediate table I link each calendar date to each active record in the fact table on that date:
'sk calendar' - 'sk sells'
  01012021   -    1
  01022021   -    1
  01022021   -    2
  01032021   -    1
  01032021   -   3

With these tables I can relate the tables, without having to make specific measures for this, I don't need to filter by dates, thus getting faster.
Problem with this, is that the size of the database of this intermediate table is huge, because I work with thousands of records.
To get an idea, today it has 350 thousand records, and for each day it will be linked to these records.
If we take the dates from the example above, we would already have in the table at least 1,050,000 records, not counting the possible new records, until the end of the year it will have an exorbitant volume.

I would like to see if I can get another opinion to resolve this situation.

Thank you very much in advance.

Regards, Vinicius

[MFelix · Accepted solution]

Hi @ViniciusScur ,

I believe that you canm solve the issue on the first option by using the calculated groups.

That functionality allows for your to set up a calculation on any selected measure.

check the link below that allows for you to have access to explanation and examples:

https://www.sqlbi.com/calculation-groups/

[ViniciusScur]

Thank you @MFelix ,

I'm going to read about the calculations groups as you mentioned and try to implement them.