Should I split tables by year?

[jmvidal]

Hi,

I am starting from scratch my dataset and have a common structure of:

• Order lines
• Delivery note lines
• Invoice line

since many years ago.

I was wondering if it makes sense to split those tables in two each, with current year on one table, and past years on another, so my refresh process would take much less data and time every day, as far as old data should never be updated.

On the other side, as order headers, delivery note headers and invoice headers are much smaller, I would keep one for each, for the whole years.

Daoes it make sense?

Thanks,

[V-lianl-msft]

Hi @jmvidal ,

Yes, you can create a DAX using the "union" function. If you are a power Bi premium user, you can use "incremental refresh"

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jmvidal]

Thanks, I''l be trying UNION.

Not a premium user yet.

[jessegorter]

I don't think that is the common practise. You might want to use aggregations if you have really large data sets though.

[jmvidal]

Thanks for the suggestion.

I'am avoiding aggregation till now because I need to keep a certain level of granularity.

[Anonymous]

@jmvidal  The BI Accountant has this approach that is along the same lines: https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/

[jmvidal]

Thank you @Anonymous !

After reading the article looks like it is not a common way to deal with large data series when you just need to update recent info.

I thought it would be a more popular request...

[Anonymous · Accepted solution]

@jmvidal I am doing this with transaction data: 2015 to 2019 data is static and then 2020 data is refreshed. Then I use the UNION command. It makes the data refresh much faster.

[jmvidal]

Thanks, that makes sense. That's definitely what I'm going to try.