Let's say I have a star schema and I have created numeric keys from dimensions. I merge the dimensions with the fact table, I expand the numeric keys, and I remove the dimension - on which I merged - from the fact table.
These keys keep changing, depending on sorting, etc.
I let these tables be created with a dataflow (long refresh).
After that there will be many small Excel files created that have equal dimensions to the dimensions in the datamodel above. People will add facts in these Excel files.
Now I want to include this data in the data model, by allowing a refresh that doesn't refresh the underlying datamodel, but only adds the new facts from the Excel files to the datamodel.
I do this by using the dataflow tables in power bi desktop and set them to not refresh when clicking refresh in the report.
But how do I merge the data from the Excel files with the fact table from the data model? I only have numeric keys in the fact table, but I don't have these keys in the Excel files.
Or must I keep the dimensions in my fact table to be able to merge against in a later stage? I could hide them so it would still look like a clean fact table, but these extra columns feel like bloat... I don't want them.
Or do I create a different fact table for the Excel files and simply link it to the dimensions? This feels stupid as well, because they actually belong in the existing fact table.
Does anyone have an idea how to deal with this?
