I have a data warehouse question that I would like some advice with...
Like most companies we get our data from flat files. These flat files are processed and then dumped into SQL staging tables. Then, an SSIS process transforms the data and pushes it out into our production DW environment. The staging tables consist of about 250 columns. We take about 50 of those columns into our production environment into a fact table. Our VP now wants all of the columns from the staging table pushed into our production data warehouse. This is most likely going to slow everything down and cause bottlenecks in processing.
What are some ways to approach this? Can a fact table with over 250 columns still perform well?
I was considering column store indexes, or perhaps splitting the fact table into 2 or 3, but the downstream affect could be costly. We have a lot of SSIS packages and processes that point to that particular fact table. I am not sure what the correct approach to this would be. I would welcome any advice or suggestions.
As always, thank you very much in advance SSC. I hope you are all well and staying safe!
Everyone has a plan until they get punched in the mouth. --Mike Tyson