How to keep the model tidy?

[faludigabor]

I have built a model , yay :).

But its a mess. Difficult to figure what measure is for what . when to use on or the other .

Is it me? 

I would like my model to be self explanatory. So my business users can build reports. 

Any best parctises here ? 

tx,

Gabor

[Anonymous · Accepted solution]

I've been thinking on similar issues.  You can put a description on a Power Query, but not on a Measure.  It seems to have been raised as an idea for over 12 months but doesn't have many votes yet - see Idea 9423663: Enhance Metadata - Descriptions

There's a related (older) discussion on PowerPivotPro Forum: Organizing Your Measures With a “Dummy” Table – Guest Post From Eric Hutton  This gives you a tidier view of your measures and allows for simpler cleanup of test measures etc., but also breaks the PivotTable drillthrough feature in Excel - on double-click, you drill into your measures table, not the data table.  And it still doesn't track your measure description.

And surfacing this metadata to users is another step.  I've previously used a SharePoint list to track measures from a business perspective (inc. the description of what a measure really means, custodian etc.)  It's useful as a simplistic business-side data dictionary with multi-user access, version control etc., and COULD be queried as a data source by your BI solution.  But it doesn't simply or reliably link to the technical metadata (e.g. DAX) for the published solution even if extracted via Dax Studio etc.

Bottom line: I think there's a gap in maintaining and publishing BI solution metadata, inc. measure descriptions.

[Greg_Deckler]

That is generally going to rely upon your particular data and using good naming conventions. Can you be more specific about your issues?

For example, I am working with a set of data where the customer wants to combine blog data, website analytics and email subscription information in a single data model. There are lots of tables involved. So, all of my blog queries start with Blog_[Description]. All of my website data queries are Website_[Description]. All of my email data queries are Email_[Description]. They want to track a lot of goals, so all of my measures that are related to goals are like "Goal Blog Pageviews". "Goal Website Bounces", etc.

Remember that measures can live anywhere. You can even create blank queries and give them names and then group measures into these blank tables.

Similarly, in query editor, you can group queries by right-clicking. Not so much for the data model, but sanity in keeping your queries together.

[faludigabor]

here are my issues : 

• In the model I have a Date Hiearchy that is tied to a certain event ie. parcel receipt
  • I would need another date hier for Parcel Sending - do I need to duplicate it ? 
  • If its duplicated -> users will be confiused over when to use one or the other
• I have measures with specific meaning , ie. # of late parcels (we keep getting confused when a parcel is considered later)
• Indicators ie. Self delivery  -would also need a proper description .

In Business Objects we could add a short "status text" per measure or dimension to help the user and properly document the model. 

I am looking for somethign similar. 

Any suggestions ? 

tx,

Gabor

[v-micsh-msft · Accepted solution]

Hi faludigabor,

Please also take a look at the article below:

Data Import Best Practices in Power BI

Regards

[Anonymous · Accepted solution]

I've been thinking on similar issues.  You can put a description on a Power Query, but not on a Measure.  It seems to have been raised as an idea for over 12 months but doesn't have many votes yet - see Idea 9423663: Enhance Metadata - Descriptions

There's a related (older) discussion on PowerPivotPro Forum: Organizing Your Measures With a “Dummy” Table – Guest Post From Eric Hutton  This gives you a tidier view of your measures and allows for simpler cleanup of test measures etc., but also breaks the PivotTable drillthrough feature in Excel - on double-click, you drill into your measures table, not the data table.  And it still doesn't track your measure description.

And surfacing this metadata to users is another step.  I've previously used a SharePoint list to track measures from a business perspective (inc. the description of what a measure really means, custodian etc.)  It's useful as a simplistic business-side data dictionary with multi-user access, version control etc., and COULD be queried as a data source by your BI solution.  But it doesn't simply or reliably link to the technical metadata (e.g. DAX) for the published solution even if extracted via Dax Studio etc.

Bottom line: I think there's a gap in maintaining and publishing BI solution metadata, inc. measure descriptions.

[MattAllington]

Store the measures in the table where the data comes from

use a good naming convention for your measures. E.g. All aggregates could stat with "Total", total sales, total cost etc. 

Extract your measures to a spreadsheet and create a data dictionary using dax studio. Read my blog here http://exceleratorbi.com.au/getting-started-dax-studio/