Optimize multiple columns to increase performance-

[binayjethwa]

Hi ,

I have below colummns and in my table which are weekly wise columns for a financial year.  now I want to optimize my model and every year new year comes. 

1. I tried unpivot columns , but it will bolat my model.

Is there any way we can optimize those columns and get all the columns into single column and optimize model.

Note: there are multiple employee whose individual hours are calulated week wise and unpivot is bloating model. THere are other columns also in this table.

Time (Hours) 19 Jun 2023	Time (Hours) 26 Jun 2023	Time (Hours) 3 Jul 2023	Time (Hours) 10 Jul 2023	Time (Hours) 17 Jul 2023	Time (Hours) 24 Jul 2023	Time (Hours) 31 Jul 2023	Time (Hours) 7 Aug 2023	Time (Hours) 14 Aug 2023	Time (Hours) 21 Aug 2023	Time (Hours) 28 Aug 2023	Time (Hours) 4 Sep 2023	Time (Hours) 11 Sep 2023	Time (Hours) 18 Sep 2023	Time (Hours) 25 Sep 2023	Time (Hours) 2 Oct 2023	Time (Hours) 9 Oct 2023	Time (Hours) 16 Oct 2023	Time (Hours) 23 Oct 2023	Time (Hours) 30 Oct 2023	Time (Hours) 6 Nov 2023	Time (Hours) 13 Nov 2023	Time (Hours) 20 Nov 2023	Time (Hours) 27 Nov 2023	Time (Hours) 4 Dec 2023	Time (Hours) 11 Dec 2023	Time (Hours) 18 Dec 2023	Time (Hours) 25 Dec 2023	Time (Hours) 1 Jan 2024	Time (Hours) 8 Jan 2024	Time (Hours) 15 Jan 2024	Time (Hours) 22 Jan 2024	Time (Hours) 29 Jan 2024	Time (Hours) 5 Feb 2024	Time (Hours) 12 Feb 2024	Time (Hours) 19 Feb 2024	Time (Hours) 26 Feb 2024	Time (Hours) 4 Mar 2024	Time (Hours) 11 Mar 2024	Time (Hours) 18 Mar 2024	Time (Hours) 25 Mar 2024	Time (Hours) 1 Apr 2024	Time (Hours) 8 Apr 2024	Time (Hours) 15 Apr 2024	Time (Hours) 22 Apr 2024	Time (Hours) 29 Apr 2024	Time (Hours) 6 May 2024	Time (Hours) 13 May 2024	Time (Hours) 20 May 2024	Time (Hours) 27 May 2024	Time (Hours) 3 Jun 2024	Time (Hours) 10 Jun 2024	Time (Hours) 17 Jun 2024	Time (Hours) 24 Jun 2024
0	16	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
20	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0

[DeanWHall]

HI,

Unpivot is the correct approach to turn the above wide data into the correct columnar format - so you end up with something similar to columns for Employee, Time and then Hours.  You mention 'other columns' in the table - if these are fixed per employee (ie. address, cost centre or similar) then these should be removed into a related (Dimension) table for the employee - leaving only yht eFacts in the 'Hours table'.  This will not result in a bloat as the Power BI engine is better at stroing columns of data in lots of rows than many columns as above (columnstore).