Forum Discussion
Complex modeling with 4 tables. need help!
Ok, so i have a rather large request that I have been mulling over for weeks. I have 4 central tables, along with other key tables (no need to get into these, but wanted to note it)
These 4 tables each have an account ID number, a date, and their own unique fields
1. one table has account volume and revenue
2. one table has account support desk calls
3. one table has survey responses
4. one table has no date, but is used as a key for segmentation purposes
I would like to combine all of these tables, or atleast use them as outer links to a central table that i can then combine. the problem I am running into is that each table will have multiple rows for account number either for the date, for the application they use or origin of the support desk calls, so i end up getting a ton of singlular value expected errors.
Right now I am using the survey responses table as the main one that has many to many connections with the other tables (each table will have a different set of account ID numbers, but I am only looking for account ID's that are found on all 4 tables.
Any help would be appreciated
5 Replies
So far you don't have a data model yet, just a bunch of tables somehow wired together. In a data model you have dimension tables (stuff you filter by) and fact tables (stuff you calculate). Dimensions control facts in a 1:* single direction relationship. Can you identify the type of your tables?
Yeah, so i am lookingto build a star schema, the difficulty is that the dims tables have multiple distinct ID rows due to multiple categories they are in. So ACME, under ID number 2342 is present in 2 rows because they are in industry apparel and electronics.
So it would look like this, where I am obviously getting singluar value expected errors. There are associated revenues per each one of these categories, so do I have to do top 1 of each account id so there is only 1 row for each unique ID?
Thanks,
Account ID Account Name Industry 2324 acme apparel 2324 acme electronics 131131 fred warehousing 13134 sarah apparel 432121 jessica warehousing 432121 jessica electronics Also, all 4 are fact tables, but i would like the category fields from each table to use across all tables.
Power BI is the wrong tool for that. If you need an associative model, use Qlik.
In Power BI you will have the best success with a star schema data model. Normalize your data so you have dimensions for Date, Account, Industry etc, and facts that describe actual transactions.
