Tag Archive for Analytics

6 Steps For Creating Golden Records

If you are an organization seeking to improve the quality of the data in your business systems, begin by automating the creation of Golden Records. What is a Golden Record? A Golden Record is the most accurate, complete and comprehensive representation of a master data asset (i.e. Customer, Product, Vendor). Golden Records are created by pulling together incomplete data about some “thing” from the systems in which they were entered. The System of Entry for a customer record may be a Customer Relationship Management (CRM) or Enterprise Resource Planning (ERP) system. Having multiple systems of entry for customer data can lead to poor quality of customer master data – even giving your employees bad information to work off of.

But why not simply integrate the CRM and ERP systems, so that each system has the same information about each customer? In theory, this is a perfect solution; in practice, it can be difficult to achieve. Consider these problems:

  1. What if there are duplicate records in the CRM? Should two records be entered into each ERP? Or the reverse: what if one CRM customer should generate two customer in the ERP (each with different pricing terms, for example)?
  2. What if one or more ERP systems require data to create a record, but that data is not typically (or ever) collected in the CRM? Should the integration process fail, what will be the remediation process?
  3. What if one of your ERP systems cannot accommodate the data entered in CRM or other systems? For example, what if one of your ERP systems cannot support international postal codes? Are you prepared to customize or upgrade that system?

There are many more compatibility issues that can occur. The more Systems of Entry you must integrate, the more likely you are to have many obstacles standing between you and full integration. If your business process assumptions change over time, the automated nature of systems integration itself can become a source of data corruption, as mistakes in one system are automatically mirrored in others.

Golden Record Management, by contrast, offers a significantly less risky approach. Golden Records are created in the Master Data Management (MDM) system, not in the business systems. This means that corrections and enhancements to the master data can be made without impacting your current operations.

6 Steps For Creating Golden Records

At a high level, the process of creating Golden Records looks like this:

  1. Create a model for your master data in the master data management system. This model should include all the key attributes MDM can pull from Systems of Entry that could be useful to creating a Golden Record.
  2. Load data into the model from the variety of SOE’s available. These can be business systems, spreadsheets, or external data sources. Maintain the identity of each record, so that you know where the data came from and how the SOE identifies it (for example, the System ID for the record).
  3. Standardize the attributes that will be used to create clusters of records. For Customers and Vendors, location and address information should be standardized.
  4. If possible, verify attributes that will be used to create clusters of records.
  5. Create clusters of records, by Matching key attributes, to create groups of master data records. The cluster identifier will be the Golden Record identifier. You can also think of this in terms of a hierarchy. The Golden Record is the Parent and the source records are the Children.
  6. Populate the Golden Record, created in MDM, with attributes from the records in its cluster (the source data). This final step, called Survivorship, requires a deeper understanding of how the source data was entered than the previous five steps. We want to create a Golden Record that contains all the best data. Therefore, we need to make some judgements about which of the SOE’s is also the best System of Record for a given attribute (or set of attributes).

Great! We’ve consolidated our master data, entered from a variety of systems, into one system which also contains a reference to a parent record, called the Golden Record. This Golden Record is our best representation of the “thing” we need to understand better.

But wait! The systems of entry, the systems your business USES to operate, have not been updated. Can you still take advantage of these Golden Records?

The answer is “yes” – you can take advantage of the Golden Records in two ways:

  1. As the basis for reporting, because each Golden Record is also a “roll-up” of real system records that are referenced by orders, returns, commissions, etc. Golden Records provide a foundation for consistent Enterprise Reporting.
  2. As the basis for data quality improvements in each system of entry, assuming these systems can import a batch of data and update existing records that match a system ID.

These benefits of Golden Records are gained without the high risk and high costs that come with systems integration. Further, if you have modeled your master data correctly, it is possible to automate the data quality benefits of Golden Records Management, by updating these systems in real-time. See how BlumShapiro can help with your master data needs and golden record creation.

Berry_Brian-240About Brian: Brian Berry leads the Microsoft Business Intelligence and Data Analytics practice at BlumShapiro. He has over 15 years of experience with information technology (IT), software design and consulting. Brian specializes in identifying business intelligence (BI) and data management solutions for upper mid-market manufacturing, distribution and retail firms in New England. He focuses on technologies which drive value in analytics: data integration, self-service BI, cloud computing and predictive analytics

KPI’s in Power BI: Not as hard as you think

Power BI just keeps getting better. The addition of the KPI visual to the standard palette is just another example in a long line of improvements, and the subject of this quick article.

Key Performance Indicators have been around long before the computer age. Show of hands: Who has ever browsed the new car showroom and NOT looked at the window stickers listing the vehicles’ MPG ratings? I thought not. While maybe not a true KPI as explained by Gerke & Associates, Inc here, Miles per Gallon is something we all understand when talking about the performance metric of a car. Most cars now come with computerized displays that will show instantaneous MPG, or an average over time. Keep these in mind when we transition this discussion over to Power BI.

In SQL Server Analysis Server cubes, you had the ability to create KPI’s inside the cube. They could then be browsed by the tool to which it was connected, something such as an Excel Pivot Table. Thought slightly different, KPI’s were also available in Analysis Server Tabular Models and Excel PowerPivot, both precursors to the Power BI Desktop.

But data analysts and modelers may experience premature disappointment to find that there is no way to create a KPI when using the Power BI Desktop designer. Knowing it was there in Excel PowerPivot models doesn’t help. It was there before, why did they take it out? Enter the KPI VISUAL.

KPI on Palette

By selecting this visual, you can create a KPI out of any metric in your model. It has three simple fields in the designer to define its appearance: Indicator, Trend axis, and Target goals. Let’s take a look at each of these in turn.

  • Indicator: This is the aggregated column or measure being considered. It could be as simple as the Sum of Sales. You do NOT need to slice this metric down to the latest value, such as [Sum of Sales for the last full month] or anything crazy like that. The base metric will suffice. The reason is explained below.
  • Trend axis: Grab a date type field for this (obviously) and (not so obviously) select the granularity: year, month, etc. Doing so will tell the KPI visual how you want to aggregate the Indicator metric over time.

At this point, your KPI should display two things: a black number and a grey shaded area in the background. We’ll explain all this at the end.

KPI 2

  • Target goal: This can also be an aggregated column or measure similar to the Indicator. If you don’t have one, it’s easy to create a ‘static’ goal by adding a new Measure with a simple static value such as: “KPI Goal = 100”, which is what I did for this demo.

Now, there’s only two options when you get all those things set: either it looks right, or it doesn’t. Consider the following two KPI Visuals from Power BI, both created from the same set of data, both using the same field settings.

KPI compare

The green one on the left I can tell you is how it is supposed to look based on the data I entered. It’s easy to see in the 6 rows that there has been a steady increase over the last 4 years.

KPI Data

So why did my initial attempt at a KPI result in the red one on the right? Apparently there is a dependency on the order in which the KPI is designed. If you selected a metric to add to your canvas first, and then decided to switch it to a KPI, you may get erroneous results. If, however, you start by adding an empty KPI to the canvas and then populating the three fields, you will probably see what you expect. If it doesn’t look right, the fix is quite simple really: remove the Indicator field and re-add it! It may be quirky, but it works.

Now let’s talk about all the pieces of information contained in this one (now correctly formatted) KPI visual. Referring to the green version on the left above, the bold number 120 corresponds to the value calculated (probably summed) for the latest point of the Trend Axis. Based on my data, that is the point for 1/1/1015. Typically, data would be spread over may dates over the years, but the aggregation of the Indicator, and the granularity of the Trend axis will determine the latest point to be displayed. The shaded green area is the trend plot for the Indicator. This shows a decrease at the very beginning, but steady increase after that. Next we see the green check-mark and green shade, indicating that this point is above the goal. (Using the format menu for the KPI you can reverse this if a lower number is better.) Finally, the small black text below the Indicator shows us the Goal, and the distance from that goal.

With this type of control in your hands, you can easily create KPI’s that display the same Indicator, but for different time slices such as by year, quarter, month, week, or day, depending on your needs.

Want to learn about some additional features in Power BI. View our series on Row Level Security in Power BI:

Part One

Part Two

It’s Easy to Assess and Share Your Project Portfolio Health

 

Successful companies continually improve the way in which they envision, manage and execute their internal projects.  Companies which execute their projects effectively (on-time and on-budget) have a tremendous advantage in  the marketplace – provided they are doing the RIGHT projects.

But. we hear a common pain from many of our clients around reporting and insights – How can we monitor the overall health of our Project Portfolio, without getting mired in Resource constraints and “Eye-Chart” style reporting?  Executives need continuous insight into how their portfolio is performing, and to do this they need to calculate the ROI and Health of each project individually and then aggregate.  They also frequently need to consider Resource Availability, Schedule Constraints and Costs when assessing the health of any given project.

We have been working with our Project and Portfolio Management clients to help them build dashboards and visualizations of project data managed in Microsoft Project Online.  Microsoft Project Online is a Cloud Hosted Software-as-a-Service (SaaS) solution for Project Management Offices (PMO’s), Project Managers and Project Teams to formally manage ALL of the projects in their portfolio in a disciplined manner.  Blum Shapiro’s Project and Portfolio Management practice helps companies improve their Project Management capabilities, often with technology tools such as Microsoft Project Online.  You can learn more about our Project and Portfolio Management practice here.

Microsoft Power BI is uniquely suited to the building of these dashboards and visualizations, because it is also a SaaS type of product – which means you don’t need to purchase BI servers or make room in your data center.  That would make this into another project!  Further, it is designed to connect to, shape and model ALL data you may need, whether this data is On-Premises, in the Microsoft Cloud, or in another Cloud.  Finally, it’s really, really EASY to get started.

Power BI comes in two licensing offers: Free and Pro.  The Free Edition works well for individuals or departments who want to build personal dashboards for themselves, simply to keep track of or analyze important data for which they are responsible.  We’re running workshops throughout the fall called Dashboard in a Day where we help clients connect to cloud datasets (such as Project Online) and build themselves a simple set of reports and dashboards. Truth in advertising: it takes less than a day.

However, collaboration is valuable.  Nobody wants to hold status meetings with Senior Management at their desk or even be tied to a projector.  Therefore, we recommend that companies with a clear Project Management directive upgrade to the Pro Level ($10/user/month), and the biggest reason is in order to take advantage of Content Packs.  A Content Pack is a collection of pre-built Datasets, Reports and Dashboards which can be published and shared across an entire enterprise.  As easy as it is to connect and consume data in Power BI, some knowledge of the source systems is extremely helpful.  Since not everyone needs to understand how data is stored in Microsoft Project Online, let’s arrange and model the data for our colleagues and direct reports, then share our insights.

Blum Shapiro Consulting offers a Power BI Content Pack which contains pre-built reports and dashboards for Microsoft Project Online.  All that is required is for us to change the web address of our data sources from ours to yours, publish the content pack to your Power BI tenant, and your organization will have instant visibility into the project portfolio.

In order to get this instant visibility, users would follow these simple steps:

First, Sign up for Power BI Pro.

Once signed up with an Organizational Account, users will have their own dashboard viewer.

Second, In the lower right hand corner of the application, Click Get Data

Power BI Get Data

Under My Organization, Click Get

Power BI Content Pack Library

Select from one of several Content Packs (in this case, Project Online Customer Immersion Experience) and click Connect

POL Content Pack

After about 10 seconds, users will have a prebuilt set of Reports and Dashboards to view.

POL Dashboard

Before we leave, we’ll help you set up an Hourly Refresh Schedule on the data from Project Online.  That way, the dashboards you share will always be up to date.

Contact us to learn more about Power BI Content Packs, Microsoft Project Online or our Dashboard in a Day workshops.