-1

Building a BI system (dashboards) in Excel using imported tables (from excel files). We're using Excel 2016 query, data model, measures using DAX expressions, resulting in more pivot tables (some of which are reloaded into data model), etc.

My question: is there "best practice" on 1) naming these data elements and 2) documenting these bits to have a more complete system documentation.

Background: I'm the senior "hacker" munging these things together. But I need to move this towards being sustainable. I did some prototyping work and when I went back a week later it was challenging to reconstruct my thoughts and relationships...

I've seen folks refer to use of PowerBI flow diagrams to support documentation; but it seems to be more of the "icing on the cake" than the "cake" itself.

So what "bread and butter" documentation approaches have you, more experienced developers, taken to ensure that your systems are clearly documented so that others can pick up where you left off???

JMKõ
  • 61
  • 1
  • 8

1 Answers1

0

For naming, I follow the Kimball Group's advice for data warehouses/marts, e.g.

https://www.kimballgroup.com/2014/07/design-tip-168-whats-name/

I rename many/most Query steps to reference the column or table name, e.g. Added Custom => Added Customer Name, Append Queries => Append Customers. The idea is to be able to pick the right step first time when coming back for maintenance.

You can select all the Queries in the Query Editor window and copy their code, then paste it into Word etc as the starting point for your documentation. You can also screen-shot the Query Editor's Query Dependancies pop-up.

For the Power Pivot logic, try this solution:

https://powerpivotpro.com/2014/03/automatically-create-data-dictionary-for-your-power-pivot-model/

Mike Honey
  • 14,523
  • 1
  • 24
  • 40