How Power BI Transformed Our Data Culture

[MCornish]

My business has had a massive data overhaul in the last 18 months, with all of it being supported by Power BI with SQL server backend. I wanted to detail here my experience with going from literally nothing, to full use of Power BI, what problems we encountered, and how PBI and the Community helped us to overcome them.
 
Power BI has completly changed the way in which the business views and accesses data.  Power BI has truly changed our workplace for the better.

First, a bit of background details.

Me: I am a 33yr old SQL Server Data Architect, that spends my spare time watching videos of Power BI and SQL

Who I work for: A medium B2B wholesaler

Starting tech: 2 SQL servers (1 production, 1 reporting) with an ODS database, SSRS

Current tech: 2 SQL servers (1 production, 1 reporting)

                      50 Power BI Pro licences

                      SSRS 

As you can see in terms of "up front cost" of the our reporting solution, its literally just Pro licences (and time).

Day 1 - It begins 

I started with my employer 18th February 2018, by the end of my first week, the recommendation I handed to the Head of IT was simple. "Delete everything, start again".

Around 75% of all reporting was done from querying the live system, (on-prem Microsoft Dynamics), the other 25% was from a copy of the live database for day-1 reporting. We had data silos everywhere and nobody got the same number as the person next to them when reporting figures. This was accepted as business as usual. As long as the number was about what was expected, it was good enough. Which meant when the right figure was lower/higher, proving it was correct was a chore.

After putting forward my proposal for a data warehouse to be built I was given only 2 instructions:

1. We had to use SQL Server. Great, that's my bread and butter.
2. Power BI was the main reporting tool going forward, with SSRS as a subscription based reporting tool.

Number 2 was a little mild panic. I had only ever really glanced at Power BI and that was way back when it first got started and was, to be fair, lacking in some basic features.

Phase 1 - Everything is awesome

So I got to work, did my ETL processes using change tracking in SQL Server. Dimensioned up the data and by the end of it had produced a good snowflake schema'd data warehouse. The days were happy and joyous. SSRS gave me the ability to write some very good reports, but, I knew that Power BI was on the horizon. So I did what all developers do to start with, I made some mistakes, big ones.

Phase 2 - Mistakes have been made

Mistake 1 - Not building a proper model

Coming from SSRS, i thought, "hey lets just throw everything in here as one big table, it works for SSRS". I created a view in SQL server that gave me the current years sales, imported it, and tried to write reports of the back of that. This approach is very deceptive, because, it does work, kind of. When testing out this approach I did some basic SUMs, COUNTs etc and they all returned the numbers i was expecting. 

The problem came later down the line when I needed to do things like time intelligence, slicers that had all dimension values even if there was no data for them.

***Enter stage right - community.powerbi.com***

It didn't take long to realise that every post I looked at mentioned the phrase "Data Model". This lead me to the conclusion that I was being stupid! My data is already prepared for data modeling just sitting there in SQL server. I could just bring those tables in and then jobs a good'n.

Once I had this realisation, modelling just became select table X and Y and Power BI linked them on its own because it reads the foreign key constraints from the DB.

Mistake 2 - Renaming columns, EVERY TIME

In my data warehouse all of my columns have nice developer friendly names like 'CustomerBusinessName', 'DayOfYear'. So I import these dimension tables and spend a good while going through and renaming my columns to more user friendly naming. Great, i have a working model, my new funky time intelligence functions work and champagne corks are flying.

***Enter - "The Users"***

It doesn't take long before the dreaded question comes your way. "It's good, but, I need to see......". **bleep**, that data doesn't fit into the model, its completely unrelated and doesn't belong.

No matter, I'll build another model for this one. That's when it dawned on me. I'm going to have to go through all my dimension tables and rename them all again. What if i name it different to the other one? Will it confuse the users?

My solution. SQL Views. Select from my dimension tables changing the column names (and adding any columns are that are to difficult in DAX at the time ).

So, now I have multiple models.......great, now what?

Phase 3 - "You can do better than that"

This is where the Power BI Service really kicks the competition to the curb. Realising that the reports I am now writing are literally just dragging and dropping fields onto a canvas, I decided to give control over to some "Power Users".

I started holding internal Power BI training sessions (ironic as my own skill was still very much beginner). We would go over quick measures, creating tables, charts, slicers, filters etc and then I would make a point of getting to the end of the training and saying "Notice i have spent the last hour using the mouse alone". 

It surprised me more than the users at first. Everybody always talks about "Drag and Drop Reporting" and "Self-Service" but every implementation I had come across was still just a bit to, tecky.

The introduction of certified datasets meant I could let people connect to these datasets from outside of the workspace and create to their hearts content.

So, we now have centralised models, accessible by anybody in the business (with a licence), and 9 times out of 10 if 2 people write the same report, they come out with the same answer. With the 10th time being different filters. For some this is nivarna and the end of the story. I wanted more. By this point I was sold on Power BI and all the different things it could do and was researching everything I could find about it (shout out to Adam and Patrick @GuyInACube  and Sam @EnterpriseDNA on youtube and on here, you guys have saved me more times than i can count). 

Phase 4 - Now that's impressive

There were 2 main things that we as a business were not utilising (besides all the features that are locked behind premium). 1 was Dataflows, I'll hold my hands up here and say they still confuse me and i need to look into them again soon. The second was Dashboards.

When i originally looked at dashboards right back at the beginning I thought, "Why would you want the ability to take a tile out of context". Then, one night as I was watching a film on Netflix, as the main characters were talking about who killed the victim. I found myself looking at the TV screen behind the characters. It was showing a daily figures and graphs for this fictitious company. I thought, "That would be cool". Then i remembered the Power BI dashboards and started playing with those.

As a POC I built a quick dashboard that links to a streaming dataset (again @GuyInACube  on youtube helped with this) that gets refreshed from Microsoft Flow every 5min with warehouse pick/pack stats.

Our Operations Manager was thrilled with the end product and is now being live updated on a 32" TV in his office. He doesn't have to go down into the warehouse just to gauge how busy the day is.

This then lead to the owner of the business wanting one similar. So that's what he got. A dashboard with 48 tiles that he views on his iPhone just by asking Siri to show him his "Magic numbers", a trick he loves to pull out to impress friends.

Long story short, (too late), we now have six 55" TVs around the business with departmental dashboards with a plan for a further four over the course of the year.

Conclusion

With Power BI we have manged to move our business from being a reactive workplace, to a proactive workplace.

We can now say that we truly have a self-serve, drag-and-drop reporting facility that is changing the way in which we work. Gone are the days where 1,000 line spreadsheets get e-mailed out every morning just so the first half of the day is spent analysing it row by row to find problems. The problems are now flagged and ready to be actioned before anybody has entered the building.

The Power BI apps on iPhone and Android have been brilliant for on the go, out of the office updates. Alerts for set conditions are great (although could use some work in how timely they are delivered on mobile devices) and help keep me updated when things are starting to go a bit pear shaped.

Dashboards have led to more transparency between departments. Workspace apps (especially with the new navigation layout) have given our business overview reports a very professional, high-end, feel to them. Certified datasets has enabled end users to get the right dataset for what they need. 

All of this combined has created a workspace where people agree on the figures they present to each other and discuss their impact, not whether they are correct or not.

[Anonymous]

Great article! Amazing to see how Power BI can truly transform business towards a more data driven culture. Congrats!