Transparency–reports become the pane of glass


The journey to becoming a data led organisation through the implementation of a data centric reporting structure

When you ask yourself the question “what does reporting mean to me?” It is a highly subjective question. The answer is of course that reporting means different things to almost everyone in an organisation, Team leaders will use it monitor and manage their teams, while C-level consumers will use it to validate and assess progress along strategic goals. The promise of Big Data and in many ways the biggest value add that it can bring to any business is that suddenly it is possible to have everyone accessing the same data ideally through the same dashboards.

The idea of transparency scares a lot of people, but the bigger question is of course is it right to be afraid to be transparent internally? Under no circumstances am I advocating a showing customers a warts and all reporting window, but internally we must. All too often strategic goals have been created or devised using pie in the sky highly spun tales of business performance and then success is equally measured on these spun metrics. Therein lies challenge #1 of moving your organisation down the transparent route – Performance Tweaking.

Performance Tweaking – The act of presenting your performance by excluding “outliers”

Removing outliers or areas outside your control is internally – for many organisations – standard practice, however if you look at what can be done when a corporation reports financial  data you’ll see a radical shift – transparency – a business cannot just exclude bad transactions and only report on successful ones, they can classify the bad as such but still it must be accounted for. Day one on your journey to transparent reporting will no doubt involve a good deal of this – assessing what goes into metrics and how to balance the view, a team leader will want to see everything but they will not want “everything” to be seen by a C-level.

Challenge #2 – Fear – “What would happen if a C-Level drilled down to my team to see what they’re doing!” normally disguised as “Senior managers don’t have time to look at all the data so why make it available?” The goal of transparency is that absolutely everything should be available to everyone, and make no mistake like any new tool/toy it will be experimented with when it is first received leading to some very difficult – but ultimately worthwhile conversations as alignment begins be achieved. Make no mistake however after the launch and the first couple of weeks the toy becomes a tool and a tool is there to be used. The fear however that someone else of another manager or even worse someone senior will know more than “me” is a terrifying one. Traditionally businesses run internally on a currency of Information. The information I have about how my team/department/division runs enforces the traditional hierarchical management chains and serves to enforce practices that in the information age can ultimately be counter productive.

Fear – The fear that others may come to know more about my area of responsibility than I do

Challenge #3 – the unmentionable – the third challenge is often the most difficult, it is characterised by the throw away comment of “this is too complex for me” and “I’m not an analyst how can I be expected to understand this?” The reality is that in many ways this is one of the biggest enemies to progress, the view that a big data tool, must somehow be too complex for any “normal” consumer to understand. In truth this is proof of the requirement to have a clear development model with a consumer focused design philosophy, your single pane of glass must have a clear and simple layout that is both intuitive and self guiding.

For a toddler it’s going well

As Power BI approaches 2 (24-July-2017) I’m amazed by the progress that has been made so far and the pace that growth and development are happening. The milestones are coming thick and fast! Thinking back to my kids when they were two I’m really surprised, by just how much Power BI can do already. I wonder how many human year are in a Microsoft year? #thingsToMakeYouGoHmmm

The end of Free?

In a lot of ways Free is now dead certainly for a corporate customer perspective. I’m sure Microsoft is actually very happy about that as I have been told by their team in the past the Free is NOT for corporate customers.

A new hope?

At last there is an Enterprise strategy within Power BI, this will not be seen as great by many people, but for me in the corporate world it’s great. Power BI Premium means that suddenly I don’t have to go cap in hand back to management every five minutes to ask for another few Pro subscriptions. Free never really worked in a corporate environment since you could only share manually (or 3rd party) refreshed data, suddenly the shackles are off. It will be a challenge selling the initial jump up to management, but considering you are in a much, much better place once you switch it’s worth it. All that’s missing now is a confirmed date for release… come on Microsoft!

Overall I’m really excited by the changes that have come about around this birthday, and really looking forward to taking customers through the changes and watching as the data expands peoples understanding of their own business. I’ve never seen anyone see Power BI for the third time and not just be dumbstruck. The First view is always seen as a scripted demo, the second people are always overwhelmed, but by the third, they start to get it and start to build their internal use cases and realise just how much time this will save while giving themselves so, so much more.

March of change

Well the March 2017 Power BI Desktop update has arrived and all I can say is wow!

Matrix Preview is just that, suddenly I was back in the late 90’s watching “bullet time” for the first time.
“Wait you can make a functional matrix table now that can also be used to replace a load of slicers?” Mind was blown!

Themes seem like such a minor thing, but as soon as you try and undertake corporate work you end up spending more time changing the colour scheme all the time to meet your corporate standards. Suddenly you can do it all easily and save yourself hours. Great feature, but keep adding to this guys, there’s more we need.

I’ll do more posts on these features as they evolve.

Upwards and Onward my friend

So you need to do a comparison across time periods, this has always slightly confused me I’ll be honest. Earlier though I had to figure it out, basically the requirement was to work out what happen in the last month and in the last three months. The idea being to understand if things are improving over time or (please don’t be the case) getting worse.

Now with all the time intelligence that’s out there in Power BI it is really easy to do much of this and DATESINPERIOD() is a real friend. I will no doubt end up adding another tag for Time Intelligence formulae or methods because they really are the ultimate glue that holds most of Power BI or PowerPivot together. Let’s face it half of the time you want to compare things over time don’t you?

DATESINPERIOD(<Possible Date Values (column of dates)>,<Context Date for reference>,<periods for range>, <Period selection>

with Time intelligence it is really important to make sure that you have a dates table. Recent advances in Power BI mean your date table can be much simpler, but you still need to think about what exists in that table and what features are a must from there.

The context date is really the pivot around which Dates in period works I don’t want to call it a start date because you can go forward in time from there or backwards it is the starting point, but I find that simplistic.

Periods are the key here, firstly how many if that number is positive then you will count on that many periods from the context date if it is negative you will be counting backwards from the context date. Possible periods are Day, Month, Quarter, Year

So let us see a sample


So break this down…

  1. Calculate – Basically work this all out and a return a single (scalar) value
  2. [Measure] – What are you actually wanting to do, you could call an existing measure or write one here
  3. DATESINPERIOD(Dim_Calendar[Date] – return a list of dates from the column
  4. [SpecificDate] – Context value a date that will be used to reference the periods against
  5. -3, MONTH – Month refers to the period and a count of those in this case -3 so go back three month

The clever bit for me was to get the [SpecificDate]


What was that?

Break it down, basically it returns the last day of the previous month (with data), typically you run this against a transaction table to try and get the correct context, but I’m sure you could run it against your Dim_calendar table, I just did it differently in this example what are we doing

  • Find the single value (scalar) for the last date in the column
  • Filter the column to be a month ago

The worst possible thing about time intelligence is it mostly isn’t compatible with Direct Query.

A plan forms

A plan forms

Like many companies the company I work for has concerns about the cloud, like most we’re certain that if any of our data ever touches the cloud then childen will be sacrificed and hostile states will take ownership of all our secrets. Maybe there’s some truth in that or maybe they’ve been watching too much “Mr Robot” wait does that mean I work for Evil Corp?

I’m sure the truth is somewhere between the two extremes no matter. The argument is a common one and it is one that really Microsoft entered the market place I believe unprepared to really deal with – sure the late data aquesition of DataZen kind of went some way to showing there was an intent at on-premise and of course Piramid (still never actually seen this up and running or working, could it be a myth (am I talking to you? Do you understand me or is this just a lame attempt at 4th wall interaction). Mind you Microsoft’s own book kind of spells things out the best I’ve seen so far. My personal version is that SSRS was losing ground to other ERP solutions SAP and Oracle really pushing thier own solutions, SQL based Data Warehouses were starting to really suffer, they’re cumbersome and the cost of ownership has increased as SQL licence requiremenrs have increased,  six figure sums just for SQL licences for solutions make them costly and the benefits are hard to realise. Then everything changed… my guess is around 2010 challengers started to come up. Suddenly it was possible to do things without the established vendors I remember in 2007 writing an Access Database that services circa 10’000 users. I’m not going to claim it was particularly good or that it was massively transactional, but when compared to the traditional view at the time of “buy a SQL licence” was just wrong. Some work and some thought and it was invalidated. That was just my personal experience. Looking at the market out there, solutions becan to use SQLite or MySQL or whatever the customer was running, mean while the ERP giants of Oracle and SAP continued to expand the application usage within their customers again as people consolidated their applications and looked to maximmise their investment.

2012 (or there about) cloud began to pick up, yes it still took another couple of years but the cloud solutions started to come up in 2012, Enterprise level organisations started to discuss private cloud and what that would mean for them. Into this environment and at this time Microsoft decides to reinvent it’s BI strategy, but quietly (SQL Enterprise licences are still a great seller, why risk that too soon and of course risk your business until you’re sure). So quietly Microsoft launched what I always think of as the “Power Suite” for Excel, PowerPivot and PowerMap initially, then PowerQuery and Power View were added. The purpose of this was as will tell you, a new way of dealing with data was needed.

Wowsers Ross stop prattling on …is that another 4th wall interaction?

Ok so where are we? Yes the paranoia of a business these days is around putting data in the cloud so PLAN A: is to not use the cloud storage – “Eat my shorts Dark Army” – our data will remain in our data centres BUT all queries will have to travel across the WAN. PLAN B is to use Microsoft Cloud Storage, but having the end customers demand the cloud storage is better than me suggesting it.

The first draft of my 3rd Data Model is already in play and goinART3.0g through testing, so far it’s not too bad. Direct Query to SQL seems to be ok with Power BI Desktop – just awaiting our clearence for Power BI Pro to do true gateway testing via the cloud. So far our Incident Managment, Request Fulfilment and Satisfaction Surveys are able to be reported against. Make no mistake using Direct Query requires much more planning, but the rewards are there. I’ll keep posting more as I get further into the whole Direct Query rabbit hole!

Keep on developing folks!



Power BI vs [another tool]

Well this took much, much, much longer than I would like, but we have eventually got agreement that Power BI is the best tool for our needs (ITSM Reporting). I now get to get on with the fun right?

The Lesson

When evaluating anything, be it a software product or even something more trivial like a sofa it’s vital to understand your requirements before you start to consider the solution. What is the role of your software (or sofa), what do you have to have, what are the nice to haves. Only once you have that list can you really consider what the solution should be. Make a quick study initially, what is definitely in and definately out? Only then should you start to invest your valuable time and effort into in-depth evaluation of tools.

REMEMBER: Your time is the most precious thing out there, if you’re going to spend it on something then make sure you’re not wasting it on something!

For me we ended up with two tools that broadly met the specifications and they were honestly both completely fit for purpose, the issue was our architecture team wanted one and I wanted the other. I wasn’t being a complete plum, it was more that the architecture favoured tool seemed to bring back centralised management of reporting and the multiple packages required to publish. Power BI is very close to my heart because it pushes a core belief of mine “Self Service”. Data democratisation is a coming wave and it would be to the detriment of any IT organisation to fight against it. Data is now so readily available to all in their personal lives that they expect similar at work.

After a great deal of slow progress management have viewed it Power BI as aligning to our Office 365 Strategy so worth bringing into our arsenal.

Wait what’s the lesson, you’re going off topic? The lesson is clear understand your requirements before looking for a tool. In my case in our journey through excel tools we’ve really built knowledge within our user community, that knowledge must continue to be forstered. The Democratisation of Data is the major area I want to support  and continue to grow. Efforts will continue and over the next year I want to build a real hard core community. The evaluation really found that the single layer model that Power BI has would be a better fit for us. By single layer I mean that everything is done in the same tool/engine. The other tool we were assessing (another top 3 from Gartner) has a desktop commponent that must be used to create and then a web component that is used to consume and has limited redesign capability. The final deciding factor was transparancy… Our IT organisation has 200+ people who’s activity is monitored via the ITSM reports, that means that anyone in that list may want to see what the reports are saying about them (there are all sorts of reasons for this) it may not always be appropriate to give someone a licence if they just want to seen their performance before the mid year review or end of year review. Power BI Desktop gives us that capabililty, the desktop does not require a licences and is able to grant access to the report data all be it without all the bells and whistle, the overhead of doing this was miniscule compared to the other assessed tool.

The big thing here is of course will be learn this lession and embrace it. I used to work in Enterprise Architecture and our department view was very much do multiple quick studies on new technology all the time and then evalauate requirements against those to then ideally have a top 3 for the customer to do deeper dive on. That was a different time and the company I was with then was not looking at an application rationalisation mentality. However the mind set is key, having a single “Standard” is doomed to failure, choice automatically sets the discussion to be in terms of which one of our choices fits your needs. There is some solid psychology behind this honest.

OMG a magic formula – real facepalm moment

OMG a magic formula – real facepalm moment

Ok, so we’ve all been there. We’ve all needed to pad numbers especially for dates, months are the worst, they kill when you have a mix of one and two digit numbers. While building my latest date dimension I had to combine year and week number to form a column, I’ll not bore you with everything but adding a “0” to pad out those one digits had to be done to make it all work. I looked and I looked and then BOOOM! I found it!

Then today I needed it again for something else, so my advice is write it down, store it keep it close to you, it’s going to be one of those things you use loads during key times then you’ll not touch it for months, days or hours?



Read more

I’m still here

Wow has it been three months, I’ve been _20160621_001133.JPGinundated with work and have to say my blog has suffered because of it. In summary over the last three months I have…

  1. Produced the final version of our Excel reporting tool
  2. Begun the evaluation of reporting tools
    1. Tableau
    2. Power BI
  3. Completed Tool Evaluation (it wasn’t actually that difficult, quite a major differentiator between the tools)
  4. Oh yeah and run 69 miles – I know I’m such a child, that really was a lesson though. I learned that sometime childish jokes can be oh so painful

Confirmation of selection is now what I’m after, with that I can start to really enjoy the implementation phase.

CSI is still the thing that most organisations I meet and speak with are bothered by. CSI (Continual Service Improvement) or Kaizen is not about everything you do actually being better. It is about understanding the difference and then selecting the most suitable, so gradually you tweak performance and delivery. For example – this is so British – when you make a cup of tea should you put the milk in first or last – one does not take sugar with one’s tea? It would surprise many to hear that there is a chemical difference so a subtle flavour difference. The reality is in this case its pesonal preference, but in the quest to find “Your [personal] favourite cup of tea” you have to try both, then select the way you like more. The order you try them is not relevant the relevance is the comparison.That is just the same as “If we do step x this way or step x that way which is best?”

The whole idea that bad can be good blows senior execs minds. They just cannot accept it, but really it makes perfect sense. That’s the other side of my job anyway. I hope you can now see why I spend so much time with reporting – hiding under a desk – its because I in order to know if A or B is best we have to be able to compare and that means report on the two states.

Too many Dates?

Real world item often have more than one critical date, Orders will have an Ordered Date, Dispatched Date, Delivered Date etc… In Incident Management the core dates to begin with are Created Date and Resolved Date, but there may also be Status transisiton dates i.e. On-Hold/Off-Hold/Assignment Changes etc… I struggled trying to work out how to fit this into a “Narrow your data” mentality. In the end I tried a completly different solution in 2.0. I pivoted the dates in my attempts at PowerPivot godness I probably went a little too far – I couldn’t get the original idea to work.

Pivoting the dates wasn’t as bad for me as it may be for you, I was able to do it on the SQL server, I created a view from the original source of the tables, that view pivoted the data and then PowerQuery imports the view, drops the mic and walks off like a pro

SQL Source – I wrapped mine in a create view statement

SELECT [Incident GUID], DateSwitch.DateValue, DateSwitch.DateType
FROM TblAll_Incidents
UNPIVOT (DateValue for DateType in ([Created Date], [Resolved Date], [Closed Date])) as DateSwitch

I’ll break it down because I know I promised not to get overly technical. I’ll also share a secret – GUID, if you talk to DBA’s or System Architects long enough you’ll come across this term and they’ll invariably try and look down their nose at you if you ask what it means so I’ll tell you. GUID stands for Global Unique IDentifier. Simply put a GUID should be unique within the system – if not the whole wide world. They are 16 digit strings of numbers and are semi-randomly generated (I’m not 100% on the science of their generation) basically though when someone says they’re going to use GUID’s it means they’re going to set the Database up properly, numeric 1,2,3,4… indexes not only repeat for each table they also commit the biggest sin in database terms… the change width, 1, 10, 100, 1000.

For those old enough to remember it that was the worry about the Millennium bug “How will a computer cope if it has to put 100 (three digits) into the space for 99 (two digits)?” Millions if not Billions was spent so we wouldn’t find out the hard way.

The UNPIVOT section is going to get me into trouble, basically it’s going to turn a column into a row in SQL PIVOT would turn a row into a column, in essense though I’m sticking to what I said, I pivoted the dates using the UNPIVOT command in SQL. So we’re turning a column into a row, remember though at any given point in the query we’re only looking at one row. That means our single row will be slit into the number of rows you mention in the the UNPIVOT. Our Tickets have three dates [Created Date], [Resolved Date] and [Closed Date] so our unpivot turns our one row into up to three (not all tickets have been resolved or closed remember.


Suddenly there was a way of really pulling the column counts down for the imports. The second step occurs in PowerQuery. When importing that DateValue column change the datatype to just be Date – you lose the time but the reality is for most reports you won’t use it. Retaining the time in the SQL though means that IF or in my case more likely WHEN senior management say “We want to know what time tickets were created” you can handle it

I got too keen when I first hit upon this and tried to combine all the dates from all the processes I needed to report – they’re still like that in the SQL, however PowerPivot (or more likely my skills) let me down. If you work out how to combine the dates from a single FACT table into a single “Date Pivot” please let me know!

Another day another village?

I am starting to really gain an understanding for how Dr Frankenstein must have felt, that terrible mix of pride at what he’d created and then horror as he watched it lurch off towards another village.

Today I recieve a Typical Request, this is the sort of thing I get used to, I’m so used to it now that that as you can tell I generally butcher my notes, be kind, normally there are only for me to read.

As a “Doer” (note I give myself a capital), I’m used to getting tasks like this, although if I’m honest I’ve done a pretty good job of collating this into something meaningful and in this particular case the customer had actually mocked something up… very unusual! This is however a great one to show you how it goes typically when I receive a request like this.

Step 1: Meet with customer
Step 2: Panic – What am I doing, what have I agreed to, why am I speaking to myself?
Step 3: Refer to Data Model, does it have the core data I need
Step 4: Create additional Measures as required
Step 5: Start setting out reports
Step 6: Meet with customer(s) and present results
Step 7: Rework as required (never ever underestimate this step, your first pass will invariably miss the point and not through anyone’s fault)

Step 7 is by far the hardest step for people to understand, this goes back to my time in Consultancy but is a hard lesson to learn and can be a bitter pill to swallow. Emergent Behaviour is not the same as people doing a “Lou” (See Lou and Andy), the reality is much more complex while at the same time being simpler – I’ll jack all this in at some point and become a motivational speaker – EMERGENCE. Emergent Behaviours are mathematically unpredictable without going through the steps. A great example of this is Langton’s Ant. So when you present to your customer/manager/trained/untrained imp a report and they say “couldn’t it do…” or “but I wanted…” generally what they really mean is “Holy Cow that’s blown my socks off, that reports shows me <Insert point here>, I wish I could dig deeper”. The Emergent Behaviour is that if you didn’t do the first draft/pass they wouldn’t know what they wanted. There is no way to predict the outcome without following the steps. Knowing that and releasing the stress of being asked to make changes is a vital step. Likewise, the frustration often felt by the requestor needs to be tempered (I find however reminding them off this rarely moves things forwards) hopefully one will read this an have that “Holy rusty metal Batman” moments (other super heros are availalbe!) and realise that in fact that first pass was necessary to help distil what it is they are looking for and to drive the key thing they actually want – An Invaluable Report rather than worthless jibber jabber.

Good hunting Starbuck!