I'm dealing with an issue in one of my elasticube's where I've added more fact tables. The main fact table within my cube is our accounting system ledger, which lists every transaction our accounting team processes. I've wanted to add other fact tables to this cube, such as a static excel budget file. This budget file also has a date dimension. I thought it would easy to create a common date, simply by doing union statements to create a date dimension table composed of both tables date fields. Both these tables also share a project number dimension.
However, I've now run into an issue where the shared date field between the fact tables has created data duplication. We of course have many entries in our ledger per day. Sisense has duplicated a project's budget amount to every single transaction row of that project in our ledger for the first day of the month. (the default date for each project's monthly budget amount). So when I have a certain month selected in which I want to see the budgeted revenue, it is the correct entry but multiplied by the amount of duplicates.
The goal is to be able to compare our actual to budget revenue. One from our accounting system, the other from the static excel file. I want my users to have to only control one date filter from their dashboard. I originally thought I could solve this on the front end by simply dividing the budget amount by the amount of duplicates, but this does not work if they want to compare the revenue vs. budget for multiple projects.
An equation to determine the variance between the revenue and budget number always results in an error, I'm guessing because they're from different fact tables?
How do I improve my data model to eliminate this issue? Thank you for your help, still learning how to properly model a schema.
Please sign in to leave a comment.