Increased data size after new date column

[HR3038511]

Hi, 

we added a new date column (changeddate) to one table:

The column increases the model size by quite a bit. Any ideas how to reduce the size for this date column?

Thanks!

[Anonymous]

Hello,ribisht17 ,govind_021 ,Ritaf1983 and Gabry ,thanks for your concern about this issue.

Great advice from everyone!
And I would like to share some additional solutions below.
Hi,@HR3038511 .I am glad to help you.
I'll just make some brief summaries of the suggestions made by other users, and I hope you find them helpful.

If you want to reduce the impact of data columns on model capacity.
You can start from the following aspects.
1. Reduce the number of dates as much as possible, and try to use integer or other numeric types to save them, in general using integer types to save data can make occupy a large percentage of memory reduction. (provided that it does not affect the use of the date field itself)
2. Turn off the auto date/time feature in Power BI Desktop.
This is one of the official Microsoft recommended ways to reduce semantic model memory.
URL:
Data reduction techniques for Import modeling - Power BI | Microsoft Learn

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian

[ribisht17]

Hello   @HR3038511

Adding a date column can indeed increase the model size, but here are some strategies to reduce it:

1. Date Format Optimization: Instead of storing dates in a verbose format, use a more compact representation. For example, converting dates to an integer format (e.g., YYYYMMDD) can save space.

2. Remove Time Component: If the time component is not needed, store only the date part. This can significantly reduce the size.

3. Indexing and Partitioning: Use indexing or partitioning based on the date column to improve query performance and reduce the data footprint.

4. Data Compression: Apply data compression techniques provided by your database management system. Most modern databases support data compression, which can help reduce storage size.

5. Date Range Limit: If the date range is limited (e.g., only data from the past year), ensure that only relevant dates are included in the column.

6. Summarization: Aggregate or summarize date-based data where possible. Instead of storing individual dates, store summary statistics for periods (e.g., weekly or monthly aggregates).

7. Splitting Data: If the table is very large, consider splitting the data into smaller, more manageable tables based on date ranges.

   Regards,

   Ritesh 

   Please mark the answer if helpful, so that it can help others as well  

[govind_021]

Hey , 
Please Look for following points

• Remove the time portion from the column if not needed, keeping only the date.
• Convert the column to an integer format, such as YYYYMMDD, to save space.
• Change the data type to Date instead of DateTime if time values are unnecessary.
• Aggregate or summarize the data to a higher level, such as monthly or yearly, if detailed dates are not required.
• Archive older data into a separate dataset if it is less frequently accessed.
• Sort the column to improve VertiPaq compression efficiency.

  Best Regards,
  Govind Sapkade
  Data Analyst | Power BI Enthusiast | Microsoft PL 300 Certified Power BI Data Analyst | MS Fabric Enthusiast
  🎥 Subscribe to my youtube channel for hands on tutorials : You Tube

  📊 Let’s connect on Linkdin : Linkdin

[Ritaf1983]

Hi @HR3038511 

The increase in your model size is likely due to the way VertiPaq handles date columns, especially when they include both date and time components. Here are a few best practices to help reduce the size:

Disable Auto Date/Time:

Go to File > Options > Data Load and uncheck the Auto Date/Time option. This feature automatically generates hidden date tables for each date column, which can significantly inflate your model size if not required.
Split Date and Time:

If your changeddate column includes both date and time components, consider splitting it into two separate columns:
One column for the date only.
Another column for the time only.
This reduces cardinality (the number of unique values) in each column, which is a key factor in model size.
Optimize Storage Format:

For the date-only column, ensure the column is formatted as a Date data type, not DateTime.
If time is stored in the second column, format it as Time.
Remove Unnecessary Precision:

If the time component has a precision that isn't necessary (e.g., milliseconds), round it or remove it entirely to further reduce cardinality.
Consider Aggregations:

If granular date-time values aren't necessary for analysis, you could aggregate the data (e.g., by day or hour) and store only the aggregated date values.

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

[Gabry]

Hello,

it looks like it is date time and not just date. Do you need also the time? If no convert to date, if yes split the date and the time in 2 columns.

Let me know if it works