To flatten a dimensional model built in Excel using pivot tables, follow these steps:
- save the original worksheet with the pivot table as a CSV (tab delimited is even better)
- select the range or column that contains the blanks (if you have blanks under the header, don’t select the column header in the range)
- select Edit -> Go to… -> Special
- select blanks (at this point Excel will select all the blank cells
- press equal
- point to the cell above the first selected cell
- press <ctrl>+Enter and Excel will copy the formula to all the blank cells
- to replace the formula by the values, simply save the worksheet again as CVS, or copy and paste special the cells as values
- that’s it, you’re done. (Don’t forget to save.)