Data Warehouse Automation (continued *) – article number two in the why/how/what/who/ series of Data Warehouse Automation
Start with automating what you have, replace by automating what you need
This article concentrates on updating your existing Decisison Support System by implementing Data Warehouse Automation.
In the second article in our 5-series about Data Warehouse Automation, we will focus on how you could get started. Previous article, we investigated the why, how and what of Automated Decision Support.
Today, we will develop some of the strategies, which could allow you to ease into an automated approach, and add as you go along.
About user feedback. How real-life meets theory – for a clash – SIMAC S3S case description
Today’s menu includes:
I. Working with iterations and applying an iteration questionnaire
II. Determining the next best thing to do & Data Warehouse Automation
III. About user feedback. Real-life meets BI theory – for a clash
Whether you do or do not use formal Agile (**) or Scrum method, most Business Intelligence (BI) methods work with some form or Iteration. You need to find a way to break down the work in doable chunks, and get cracking. Key question to BI work, or Automated BI work remains:
What will be the next best chunk to crack?
Many factors need be taken into account (see third part:Agile discussion case example SIMAC Industries)
To name but a few:
- Who are we building this for? What roles, which departments?
- Is this an extension on exisiting work, or is this something completely new?
- How many people will benefit from this work?
- How hard is the task at hand? How much time will it take? Who needs to get involved?
- Do we have te data? Do we have a clear idea on what to build?
As cutting work into smaller pieces (aka user stories) and fast incremental result is core to the approach, Agile lends particularily well to Data Warehouse Automation.
As described in previous article (*), a user story aims at uniting what the Business Users are thinking about with what the BI team is able to deliver. An exercise in mind reading.
The reason why Agile has become the superior approach, in my view, is that short iterations allow for fast corrections.
In the classical approach, false interpretations of effort and wrong results only become apparent much later – raising the cost for Re-Work, not to mention time lost. Though we may not hope to get 100% accurate in our telepathic efforts, it will be a lot easier to correct, improve and even change as we go along.
False interpretations will certainly happen in Agile, we will just notice a lot faster. Even in classical method, you will end up cutting work in smaller, manageable pieces. Once pieces of work have been layed out, we can start working efficiently.
However – there may be another factor we may need to consider.
Independent of deployment method used, so even if you do not use Agile Iterations, you may need to consider this:
Nothing we ever build in Business Intelligence is new (***)
Whatever we are building will be replacing an existing element. Some examples:
- A shared customer/market dashboard – to replace individual gut-feeling
(& loads of Excel sheets)
- Corporate MIS portal – to replace departmental Sheets and Reports
- A new set of KPI’s – to accommodate a change in strategy
- Tableau Dashboards – to replace SAP Business Objects
- Data Warehouse Automation – instead of a hand-coded Taylor-made DW
When determining the order and priority of work, before dealing with who, how and why, it may serve to ask:
What are we replacing ?
I. Working with iterations and applying the iteration questionaire
In fact, there is a set of questions you could use when scoping Business Intelligence work. Our starting point is the user story, starting with the words: “In order to …”
Allow me to build on the example used in our previous article (*)
In order to … determine future marketing means (budget), focus (target groups) and campaign efficiency (return), I need to be able to measure customer campaign responses. I also want to investigate relationship between discounts and purchase value and measure the return on campaigns and resources.
Before planning work and priorities, we have to be able to answer three main questions, answered according to the example above:
1. What is it we are trying to achieve in building this user story?
What do we hope to address in answering the BI questions?
We would like to get a clear view on marketing spend. We would also try to match campaign responses. How much effort was put in. If we achieve this, then we should be able to better use our resources, and get better return in subsequent campaigns. This will allow for the local marketing teams to comply with corporate guidelines, but also allow them some exceptions based on local differences. It will allow us to measure differences between regional offices (marketing spend / number of new or returning customers per region), or campaign efficiency per customer group.
2. For whom are we building this? Which roles and responsibilities, how many people will (potentially) be using this?
This would be used by Country Managers and local Business Development teams, as they are responsible both for deciding on campaigns and marketing spend.
3. How does the question break down in building blocks: measures, dimensions and output components such as reports, dashboards, websites, …
Ok, so we need to following dimension groups:
- a customer hierarchy per geography, in order to know where people buy our stuff, and where they do not and measure targetted campaigns
- customer per category, to know what kind of customers buy what from whome, and which category yields best return – using the corporate standards
- a product hierarchy per category, in order to compare with major competitor statistics
- a marketing (campaign) hierarchy per territory, product category and customer group
As key numbers we likely need turnover, margin, rate of return, customer average spend, marketing budget, sales budget, goals, KPI’s and actuals.
As visualisation, we would like to have some online platform or website, accesible via smartphone, tablet and PC – but it should not be overly difficult to get hard-copy for meetings or presentations.
4. What is it that we are replacing?
Our current system is based on financial figures and CRM statistics and a manual mash-up process. Mostly presented in Excel sheets and using some scripting and coding, linked to an overview Dynamic Powerpoint presentation. Facts and figures have been pulled from the corporate Finance & Accounting system, and mtached up with CRM and external (competitive) data. As each of these data sources stems from different teams, it has proven next to impossible to use the data across the different teams.
So why ask?
The BI team often interacts with different groups of users and may be best placed to compare and evaluate options & identify overlapping interests. Experience has taught me that BI systems often start out by replacing … existing BI systems.
Figuring out what it is we are trying to replace serves three-ways:
First: Avoid extra work which will yield little extra return
For each new element we want to create, we run the risk of creating double work. Double work depletes money & resources. Adds to our risk rather than to the return. Double work often is created because it is found easier to copy and vary than to identify joint interest. In the long run, the variations will of course become hard to manage. By considering what it is we are likely going to replace, we want to make sure we are not adding a variation to a theme already existing in the system.
Not every Business Question, even when it clearly deviates from mainstream useage, should be handled as a BI project. Not every user story deserves our attention, especially if we can join forces with other stories from other departments.
Using our example:
If a local Business Unit has specific (deviating) questions, then either they may be valuable for the whole company, or they need to be treated as a variation or local exception. Often, a local exception – after it has been qualified deeper, turns out to be a variation of theme.
From both a management and governance perspective, variations can be valuable – but need to be managed as such. Double work will sometimes be necessary, but should be avoided at the cost of … cost.
Second: get extra return at little extra cost
Secondly, when we understand more about what is it that we are going to replace, we could identify people who might benefit from the same work. After all, as the original system came from somewhere – it must have been serving some people well at some point in time.
Yes, the new user story may serve a new target group, but what about the old system:
did it serve the same group of people?
If the old system had multi-purpose, like our example citing Financial Data, there might be an opportunity to expand our BI audience. This may have opposite effect to our first consideration (above): with little or no extra work, we may serve a wider group better, and keep our cost of maintenance at a more reasonable level.
As part of the original dataset is coming from the Finance team, would there be any benefit in making our rework available to them?
It certainly would not hurt to ask …
Thirdly: resolve work outside the system
As illustrated by the SIMAC case example below, understanding what we are replacing can bring different insight altogether. Some questions are too specific, focussing on such a small group of end-users, that it does not make sense to incorporate their requirements into corporate Decision Support. Not as a variation of theme. Not even as a local copy. So we seem to be left to do the unpopular thing and say “No”.
... and find another solution to satisfy their needs.
In this particular case, figuring out which facts are required, and making sure they get delivered outside of the main system may be the superior option, as illustrated by the case example further down.
Similarly, some questions are of the figure-and-forget nature. i.e. something a user is merely curious about, not something a lot of users would be spending a lot of time with.
Figure-and-forget is often a reason for creating superfluous measures or reports. Handing down a dataset to be handled by the user may be our best option, one that we certainly could facilitate.
This brings me to the topic of Data Discovery, which has similar issues: data discovery is – by nature and definition – often about discovering the potential value of new data sets. Does it make sense to bring data discovery into your main Data Warehouse architecture? Something to consider …
II. Determinining the next best thing to do & Data Warehouse Automation
Ok, so how do we now proceed to set priorities and determine the next best thing to build?
This is of course, a classic Agile question (****).
Even in classical method Business Intelligence work, we need to evaluate what would make sense to build next. Having answered the 4 key questions on the User Stories above will help you create an overview like the one on the article picture above (curtesy of S3S-SIMAC).
Start by creating an overview of user groups or stakeholders (top of screen), gradually add the structure of the decision support system, domains, reports, dashboards, scorecards (center), list data sources below.
On another board, describe the first user story, and answer the questions above. By simply mapping elements on the structure board – using post-it notes or magnetic tags, you quickly get an overview of what has been built, to whom it concerns and where it is being used. After a while you will also be able to identify underserved stakeholder groups. This may help in deciding for whom we could build the next user story.
Should we build deeper, or widen our BI audience?
In most BI cases, there is an extreme focus on one part of the organisation, whereas others are far less involved. Building something new targeting a small new group of Business Users may bring in a new crowd. They will likely have specific questions, which may be harder to tackle. We will likely even have to handle new source data or output formats.
Simply having them on board may also mean that they get introduced to the work already in place. After having been successful in introducing new stakeholders or Business User group to the party, as they start working the numbers, we get to increase ROI at little extra effort.
The extra effort will only consist of ne, role specific questions they may start asking, yielding in new user stories.
So although fast ROI could be expected form building yet another thing for the special branch people within sales, adding new users to the system might bring broader return. However, bare in mind the replacement question as addressed above.
Should we continue building on Sales, Finance or HR?
Again, if we are able to maintain an overview, you get to see how a system develops, who is using what, which domains and people have been served and which have been underserved.
So what about Automation?
Data Warehouse Automation helps fulfil the Agile & iteration agenda. In Business Intelligence, Change seems to be the middle name. Any type of change can have dramatic impact on work and (often) re-work. However, most of the work is about changing details, or creating small variations – as discussed above.
Data Warehouse Automation allows BI teams to focus energy on the harder tasks at hand, by automating small change issues – some examples:
- DWA allows for fast prototyping, which enables a better communication between Business and BI team, for instance in a Data Discovery scenario
- DWA allows for automating all steps in the delivery process, from creating raw prototypes, to test/QA all the way to deployment
- If need be, Data Warehouse Automation can allow us to create small spin-off data marts,
allowing for variations without the need for overhead management
- DWA allows for all process and project documentation to get created & BI code to get generated
Most Business teams do not care too much about all this. The only things that tends to matter to the Business is that we are able to deliver a lot fast and accurate.
III. About user feedback. How real-life meets theory – for a clash
(a story from the field)
A couple of years ago, say spring of 2012, I was invited over to a customer and together we learned some of above lessons … the hard way.
I was asked to join a meeting together with their BI team and some key users. SIMAC-3-Services had been working with our DWA software, replacing a hand coded environment. The effort had been going on for two months and were about to roll out reporting to the workforce.
SIMAC is a service and installation outsourcing company, providing technical assistance and field technicians for some the largest Telco and Utility players in Europe. They market an end-to-end application for Field Service Management. In both instances, they were using TimeXtender DWA Platform to provide BI and MIS both for internal company reporting as to provide stats and reporting for the SLA contracts with customers. Most of their customers are active in Telco and Utility industries, where SLA contracts tend to be source of profit & aggravation.
S3S is not a large organisation, and we enjoyed a good relationship with both senior management and the BI team. When I was invited to present the user story approach above, I expected to meet up with a small group. The agenda of the meeting was to help present general idea’s about Business Intelligence as a way to kick off the project.
To my surprise, I was guided into the large conference area to meet up with quite an extensive audience. In the room were most senior executives, COO, the CFO. But also people involved in staffing, HR, some line managers, people from dispatching, team leaders and – as I recall – two field technicians & a Union representative.
Senior management on one side, workforce representatives on the other – arms crossed.
Not a very endearing start.
As we were about to start the meeting, the CEO got called away into an urgent customer conference – he urged us to get started and told everyone he would join the meeting at a later time. At that time, Agile and DWA were virtually non-existent in the BI space. SIMAC embraced Agile for the development of their Field Engeneering platform, called Service Cruiser ™. As they were using Agile with great success and their BI team leader was an adept, it just made perfect sense for them to continue working in Agile iterations with regards to implementing the BI project.
At that time, the term Data Warehouse Automation had barely been coined by author Dave Wells (*****), and The Data Warehouse Institute. So using DWA to support an Agile approach – at that time – was quite cutting-edge early adaption.
After a brief introduction, I was asked to present. I started by asking participants for a short introduction as to how they had decided to join the meeting. This is what I found out about the kick-off meeting:
Business Intelligence was already in use, and initial responses form some of the stakeholders were rather negative. Some of the reports were used to determine bonuses for the Field Service Technicians, and people had been complaining about results. HR, who were required to give input in this process had been left out of the discussion to some extent. The union representative complained that some of his good people had been badly judged by the reports, despite their excellent previous state of record. The new reports also implied a new way of working, as KPI’s were now tied in directly into SLA KPI’s. That one of their biggest customers was about to reopen the three year contract, and that SLA adherence would quite likely become an important criterion. This contract would become up for grabs in the ensuing months.
After having heard some of the initial arguments – I decided to go into a general BI approach pitch, referring to some other customer cases in which I had been involved. I was worried about the general feel of the meeting, but felt like there was no real time left to waste.
After the initial presentation we decided to use mapping as in the article photo above & ask the participants where they could identify with what had been created as well as with stuff in the current BI pipeline. It quickly became obvious to all that though some of the stuff had been created for other stakeholders, it would be of interest to them as well. It also became obvious which stakeholder groups had been underserved, and which ones had received more than fair resource attention.
As a result of the meeting, some things happened:
- First, stakeholders were granted access to dashboards and reports already in place – which took no real extra effort
- Second, a redesign of the report/dashboard structure was implemented based on the joint interests of some users, making it easier for them to relate to the other domains (Finance, Supply, Planning) …
- Thirdly, after discussing the nature of the bonus reports, the union representative agreed to evaluate on the validity of the data included
And not least – but last, one of the requests from the HR department was handled as an outside-DWA user story, a single report was created and appended as an outside link to the system. As this single report saved two people almost 2 days of work per month each, they were quite happy with this. The call was made to append an outside URL to the decision support system, allowing the HR staff to get acquainted with other elements of the Decision support system. I believe the S3s example is a good illustration to some of the examples and considerations we addressed earlier.
The story does not end here...
By the end of the summer of 2012 (2 months later) we were invited back in for a follow-up discussion. This served as a good way to evaluate progress made by the S3S Business Intelligence team. The HR people as well as the other stakeholders testified to a broad and general acceptance of the BI system, time saved and a more common view on strategic KPI’s. Technicians and other employees felt more involved, a reason I will explain in a minute.
Data Warehouse Automation enabled S3S to use a trial-and-learn (and error) approach to implementing Business Intelligence in an Agile, iterative fashion. With a small core group of (switching) key users, they quickly broadened and widened the usage of Business Intelligence within the organisation.
Later that year, as their most important customer put out new RFP for their maintenance activity, the Business Intelligence system allowed S3S management to assure profitability across service lines, to plan and re-plan resources in strong adherence to client SLA requirements, while being able to offer a competitive price. And make a good margin on services rendered.
The most tangible benefit was for the employees: the S3S bonus system, in line with the client SLA, allowed technicians to plan their day activity. Overtime was never paid by the client, unless urgent and requested outside of SLA. Bonus would depend on people going the right interventions as efficiently as possible, and to end-customer satisfaction. The Business Intelligence system would feed back all these parameters to the team leaders and technicians. Technicians get the liberty of planning part of their work accordingly.
i.e. one young technician (who just got married and needed to pay of a mortgage) would typically opt for extra work and extra bonus. A more senior member of staff would typically opt to finish the day within bonus, take on no extra duties and opt to stop early and pick up kids from school.
This degree of freedom resulted in a superior working climate, unprecedented in the services and installation industry. Freedom and self-organisation of teams/individuals takes preference over procedure. This resulted in a higher employee satisfaction, and lower churn.
It made S3S a more desirable employer for future hires.
Facts and figures started to fall into place, and the original complaints by the union representative dissipated. It turned out that the employees who had been underperforming were in fact … underperforming. This was already well known amongst field technicians, it just had not become apparent to management.
Not only did figures bring out a factual representation of performance, it gave credit and bonus to the ones doing a real good job. As a result, some of the employees adjusted tasks and performance; some employees received extra training or were assigned new tasks. Both activities resulted in better quality work, less client complaints (and less SLA penalties) increased bonus payment. Some refused to do so and left the company. This did not happen overnight. The set of KPI’s had to be revised and tuned many times, as new insights allowed them to. Data Warehouse Automation made it a lot easier to simulate test and deploy changing requirements, and KPI’s.
Business Intelligence and Data Warehouse Automation now allows for S3S to plan activities which align KPI’s on all levels involving all stakeholders, inside and out. From SLA requirements (= more revenue, less penalty) imposed by clients, management KPI’s, planning, supplies, setting correct contract priorities, tuning financial parameters and so on.
The S3S BI setup allowed a Business Agile approach, which includes a degree of self-organisation seldom seen in a blue-collar environment.
It allowed S3S to secure a major contract with their biggest customer, and still turn a healthy profit. The new contract was signed by the end of 2012. The contract included a yet another change in the SLA and client KPI’s, which was easily accommodated for in reporting and bonus systems a little as days later.
Business Intelligence allowed S3S to attract the right kind of field service specialists and grow faster, both inside and outside of their territory.
A little later, by the end of 2014, one of their clients was taken over by an international Telecom group and the S3S approach quickly recognized as a standard in the international field services industry.
(*) Previous article in the series: Automated Business Intelligence: Why, How, What ?
(**) see Agile Manifesto. Which, among other things prefers focus on Interaction and Team Individuals over Processes and Tools. Same as Data Warehouse Automation: I would love for you to buy our Software (Tool), but I what I am trying to sell you on is the DWA (Data Warehouse Automation) concept.
There. I have said it.
(***) Ok, save some exceptions, or the first time you introduce new data, or new technology, or BigData. But the bulk of our work, effort and cost will not be about such exceptions.
(****) Great article on WikiPedia, listing further reading on Agile and Agile BI
(*****) Dave Wells Blog – though today he seems to be focussing on BigData, I would dare give him credit for coining Data Warehouse Automation, fall of 2011.