Date dimension blowing out size of PBIX file

[Shane_Kerr]

We have a date dimension table in our (SQL Server) data warehouse that contains a row for each date between 1 Jan 1900 and 31 Dec 2099 (approx 48k rows). The table has about 30 columns and is 12Mb in size on disk.

When this table is imported into PowerBI (with no other data) the size of the PBIX file is 186 Mb. Saving this table in to Excel, it is only 9Mb. What is happening inside PowerBI that is causing the disk size to increase so dramatically?

Here is the table schema:

CREATE TABLE [dim].[Date](
	[DateSurrogateKey] [bigint] NOT NULL,
	[Date] [date] NOT NULL,
	[CalendarYear] [smallint] NOT NULL,
	[CalendarQuarterNumber] [smallint] NOT NULL,
	[CalendarYearQuarter] [varchar](7) NOT NULL,
	[CalendarYearHalf] [varchar](7) NOT NULL,
	[CalendarMonthNumber] [smallint] NOT NULL,
	[CalendarMonthName] [varchar](12) NOT NULL,
	[DayOfMonth] [smallint] NOT NULL,
	[WeekNumberOfYear] [smallint] NOT NULL,
	[DayOfWeek] [smallint] NOT NULL,
	[DayOfWeekName] [varchar](12) NOT NULL,
	[CalendarDayOfYear] [smallint] NOT NULL,
	[IsWeekDay] [bit] NOT NULL,
	[IsPublicHoliday] [bit] NOT NULL,
	[DateDisplayddmmyyyy] [varchar](12) NOT NULL,
	[DateDisplayddMMMyyyy] [varchar](12) NOT NULL,
	[DateDisplayMMMddyyyy] [varchar](12) NOT NULL,
	[DateDisplayyyyymm] [varchar](7) NOT NULL,
	[DateDisplayMMMyyyy] [varchar](8) NOT NULL,
	[FirstDayOfMonth] [date] NOT NULL,
	[LastDayOfMonth] [date] NOT NULL,
	[FinancialYear] [varchar](12) NOT NULL,
	[FinancialQuarterNumber] [varchar](2) NOT NULL,
	[FinancialYearQuarter] [varchar](12) NOT NULL,
	[FinancialYearHalf] [varchar](12) NOT NULL,
	[FinancialMonthNumber] [smallint] NOT NULL,
	[FinancialYearEnd] [date] NOT NULL,
	[GovtYear] [varchar](12) NOT NULL,
	[GovtQuarterNumber] [varchar](2) NOT NULL,
	[GovtYearQuarter] [varchar](12) NOT NULL,
	[GovtYearHalf] [varchar](12) NOT NULL,
	[GovtMonthNumber] [smallint] NOT NULL,
	[GovtYearEnd] [date] NOT NULL,
	[QuarterEnd] [date] NOT NULL,
	[Season] [varchar](20) NULL,
	[DWCreateDate] [date] NOT NULL,
	[DWCreateLoadID] [int] NOT NULL,
	[DWCreateFileID] [int] NOT NULL,
	[DWUpdateDate] [date] NULL,
	[DWUpdateLoadID] [int] NULL,
	[DWUpdateFileID] [int] NULL,
 CONSTRAINT [DateDateSurrogateKeyPrimaryKey] PRIMARY KEY CLUSTERED 
(
	[DateSurrogateKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

[SofBL]

You possibly forgot to disable create auto date time in options section of the report.  Especialy when there is an invalid date or a date way beyound the range it is going to create rows for all values in between. causing the whole model to become big. 

[v-piga-msft]

Hi @Shane_Kerr ,

By my research, I have not found any blogs and documents about this.

For your question, I'm not sure if this issue is casued by the internal setting in power bi when loading data model.

Maybe @Greg_Deckler has other ideas.

Best  Regards,

Cherry