So my head is spinning more than a little after this week. I’ve had a data strategy meeting with whom and for what purpose I genuinely do not know. Then three or four separate data model development meetings then reviewed my teams work… All of this has made me feel it is time to share my design philosophy and how I go about building Power BI reports.

I use a four step process and for my personal opinion that fits the product best, the key thing to remember is that you do not always have to go forward, I’m a huge believer in emergence and emergent behaviours, BI is a prime emergence environment

Emergent behavior is behavior of a system that does not depend on its individual parts, but on their relationships to one another. Thus emergent behavior cannot be predicted by examination of a system’s individual parts.

In other words as reports develop and value is shown addition relationships or correlations become clear. This understanding is what drives my design principles, hopefully even if you disagree you will appreciate some of the ideas and can incorporate them into your own modelling principles.

By dividing the report creation process into four distinct stages expectations and delivery gateways established, typically you should try to sign off each stage before moving forward, but you have to accept the reality that emergence willl often move you backwards, that is fine and must be accepted, pushing on regardless to the recipie for disaster.

Before we go further you must accept that this is intended for building a report from scratch rather than consuming a cube or other completely predefined report. Look out for my other posts drilling into the detail of each of these steps.

Step 1 – Source

Identify where the data to be reported upon will actually come from. This is such a fundamental step but is often completely forgotten. I have lost count of the amount of meetings that I’ve started with asking “Where do you get this data from now?” to be greeted with a room full of blank faces. If you have no clue where it comes from now building a report is impossible. Along with understanding where you will get the data from the frequency it can be gathered must also be captured, it’s not possible to report accurately between data gathering events i.e. if you can only gather the data at month end, then you cannot acurately report on progress through the current month.

Think of this as a requirements gathering step.

Step 2 – Shape

As the data comes into the data model relationships are established, Fact and dimension tables should be labelled, additional look up tables will start to emerge in this phase as data structures and reporting layouts begin to appear i.e. data that has to be in a particular order or shape.

I consider this the alpha delivery – a first draft if you will.

Step 3 – Measurements

During this stage it is imperative that you begin to hand the pack over to the end customer, ultimately a successful BI implementation must be owned by the group requesting the report or ultimately it will fail. It does not matter how well a pack is written or produced if it cannot meet the requirements of the customers they won’t use it

Therefore during the measurements phase you must take the Alpha untidy release and convert it into a structured beta release, structure is added to reports through the use of measures and “disconnected tables”, during this stage these must be defined.

As a note if you’re working on a secondarty report (a report built from an already published dataset – game changing feature this one) then you can only add measures, if you wish to extend the data model this must be done in the master data model.

Step 4 – Publish

With a report produced it beomes important to publish it and distribute it to a community, ultimately if a report is never going to be published to or a Power BI Server then it can never move beyond Step 3. Publishing goes beyond the simple report and relates to the consumers (whom), the dashboards (what) and the use of Apps (Where). Only when all these are considered and worked out can a report be considered to have completed all four stages.