0

I have a PBI data model, which is a star schema, but am wondering if it's optimal or 'correct' when it comes to creating DAX measures.

My fact table in PBI looks like this; it's a database view:

development_PK   development_spend
DEV0001          £1000
DEV0002          £3000
DEV0003          £2000

To bring in a new dimension (something I want to filter or slice by), I create a view in the database that has the development_PK and the relevant value from the location table:

development_PK   location
DEV0001          UK
DEV0002          France

I'll then join on development_PK in the PBI data model.

I could, of course, bring the primary key from the location table into the view I'm using for the fact table, where it would be a foreign key, and then simply connect to the location table.

My question is abstract in the sense that I'll be creating more data models and want to 'future-proof' them - I know certain ways of data model design can cause undesired results when writing DAX measures, e.g. in a single table model, auto-exist will create unexpected row counts with certain slicer configurations.

deethreenovice
  • 127
  • 1
  • 2
  • 17
  • If you're not updating a DB, NFs & constraints don't matter. BI "references" are used to describe queries/views. There is no "best" in engineering until you define yours in enough detail that everyone could evaluate a "best". Don't use a word unless you know what you mean by it. Even if you mean "basic & straightforward" this is too vague. You really aren't asking a question here. You don't even say what you're trying to accomplish in doing what you describe. "a bit like" is not helpful. Apply the techniques in references & ask 1 specific researched non-duplicate question where first stuck. – philipxy Mar 02 '21 at 12:11
  • [Does a data warehouse need to satisfy 2NF or another normal form?](https://stackoverflow.com/a/46523586/3404097) – philipxy Mar 02 '21 at 12:16
  • I actually used the word optimal first - is that too vague? Also, I'm not creating a data warehouse – deethreenovice Mar 02 '21 at 12:34
  • But Power BI does work like a data warehouse. In both cases, the schema is built to allow easier reporting, easier aggregations, easier selection of dimensions, filters and hierarchies – Panagiotis Kanavos Mar 02 '21 at 12:39

0 Answers0