Forum Discussion
How to structure query/tables?
Hi,
At the moment, my queries are structured like this: Answers, Companies, Filters.
Problem is, "Answers" contains Facts (Answers) as well as Filters (Segments, SubSegments, F1, For Companies of Size).
"Companies" contains each company only once - but also some facts about a company (contact, evaluated in, size...)
"Filters" contains values according to which the answers should be filtered - but some Filters have to filter Companies (and through that: Answers) some have to filter Answers directly.
Different type of Filters exist: Toggle-Filters (On/Off), Singular-Value-Filters (A or B or C), and Multiple-Value-Filters (A AND sometimes B AND sometimes C, or B AND sometimes C...)
How to structure them?
A) "in general" ("Star-Schema"...but which columns belong to dim, which to fact...which table connected to which?)
and B) especially the Filter-table: One table containing all Filters? One table containing all Filters of a Type (Toggle vs. Single vs. Multiple)? One table for all Filters belonging to Answers and one table for all Filters belonging to company?
I am confused because here (!) I cannot easily distinguish between facts and dims.
Here is my pbix:
https://www.dropbox.com/s/28qz5nnw9glxacn/Testordner7.pbix?dl=0
2 Replies
Hi, Anonymous ;
a)According to the official document.
Dimension tables describe business entities—the things you model. Entities can include products, people, places, and concepts including time itself. The most consistent table you'll find in a star schema is a date dimension table. A dimension table contains a key column (or columns) that acts as a unique identifier, and descriptive columns.
Fact tables store observations or events, and can be sales orders, stock balances, exchange rates, temperatures, etc. A fact table contains dimension key columns that relate to dimension tables, and numeric measure columns. The dimension key columns determine the dimensionality of a fact table, while the dimension key values determine the granularity of a fact table.
Generally, dimension tables contain a relatively small number of rows. Fact tables, on the other hand, can contain a very large number of rows and continue to grow over time.
b)Your table does not look like a star model, can you tell me what you are trying to do with DIM and fact? You can tell the specific requirements and then use your original model to solve them.
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
