Managed SSBI with Power BI: Core Dimensions

In this post I want to revisit the core datasets I talked about in a previous post about Managed SSBI.

One problem that we may have overlooked when building a bunch of core datasets in that post, is that certain dimensions tend to duplicate across the datasets. Imagine a scenario where the single master data source of a managed self-service setup is a data warehouse, which sources all the required dimensions. When you have, for example, core reseller sales, internet sales, and finance datasets, each one will have a calendar dimension and a few others created in each of these datasets. This is not ideal if you think about the extent of the duplication and effort that is required.

This is where, once again, using DQ for PBI datasets and AS comes into play, where you could draw up a layered core dataset architecture. If we take the example of AdventureWorks’ fact tables in the data warehouse (single master data source) you can figure out what the business processes are. Then, when you draw up an enterprise bus matrix, this is a snippet of what you will arrive at:

You will note that dimensions such as Calendar (Date), Salesperson, Product and a few others span multiple business processes. These are ideal candidates for the first layer of the core datasets. The corresponding core workspace looks like this; a dataset each for the business processes (with the exception of sales quota and inventory):

Each dataset has at least one of the following dimensions: Calendar, Salesperson and Product. Hence, I shall focus on these for the post.

Since, reseller sales has all the three dimensions, I shall focus on this first, and download the dataset from the service:

_{The reseller sales dataset}

I will get to work by keeping Calendar, Salesperson and Product, and removing the Reseller Sales and Metrics tables:

_{Dataset stripped down to only the common dimensions}

I will name this dataset Core Dimensions and publish to the core workspace. This will be the dataset that all core datasets will refer to for common dimensions.

Then, I will edit each core dataset by removing the dimensions that correspond to what’s in Core Dimensions. Next, I will connect to the data hub, select the Core Dimensions dataset and select the required dimensions. Finally, I re-create the relationships, for the dataset to look like this (This is the new and improved reseller sales):

If you look at the lineage in the core workspace, this is what you will now see:

Note: For the steps to direct query a dataset, you can refer to my previous post on extending a core dataset at the edge.

Managed SSBI: Extending a Core Dataset at the Edge

My last post gave an introduction to managed self-service BI, and the concept of discipline at the core, flexibility at the edge.

Now, what happens when an analyst, for instance, has a set of sales target spreadsheets and wants to compare the figures with sales metrics so that salespeople’s performances can be measured? It certainly needs a new dataset. However, flexibility at the edge has to prevail in the right way. This post will look at how we can go about this keeping to discipline at the core, flexibility at the edge.

Note: The analyst’s requirement is at current local to their group or department. It has not yet been made an organizational requirement. That’s how most requirements start out: A requirement at the departmental level, and then when enough people start reaping the benefits within and outside of the department, it can get absorbed into the core.

To mash up data from a core dataset with the spreadsheet data, the analyst typically will have to either get a copy of the dataset or export the required data to spreadsheets. But this can cause a lot of problems, such as:

• Core datasets are behind lock and key, and are only for reading from
• The dataset gets duplicated
• The metrics and other analytics get diluted back into plain data
• Multiple versions of the truth

This is where DirectQuery for Power BI datasets and Analysis Services comes in. (Though still in preview as of the date of this post) this functionality allows one to extend an existing dataset into their own. Which means the analyst can now build a dataset with their sales targets, and then “pull in” what’s necessary from the core dataset. The content of the core dataset will not be a copy, rather a reference, thereby reducing effort and preventing duplication.

Let us get into the depth of it using the reseller sales example. The existing core reseller sales dataset consists of one fact table and three dimensions. A separate table for metrics serves as the home for the measures and KPIs:

_{Core reseller sales dataset}

The analyst has reseller sales targets stored in a SharePoint folder across four annual files:

Step 1

The analyst needs to bring this into a new (edge) dataset. This video shows how that can be done:

_{Importing reseller sales targets from SharePoint}

This is what the model will look like once the targets are imported:

Step 2

Bring in the core reseller sales.

Since the DQ for PBI Datasets and AS feature is still in preview, it needs to be first enabled in Power BI Desktop.

Now, we direct query (and not Power Query) the core dataset from the data hub:

And follow these steps, as shown in the video:

1. Select the dataset of choice
2. Select the database (dataset) or table that are required. In this example everything.
3. And then you’ll have the core tables showing up within your local dataset
4. Create the necessary relationships
5. Create the necessary measures

Publish it, and now you’ve got the edge dataset extended off of a core dataset. Notice the measure % Reseller Performance that was created using data from the core dataset (Reseller sales) and reseller targets.

An introduction to a Power BI strategy designed with Discipline and Flexibility

One of the major components of the analytics solutions that I build for my customers, is the business intelligence subsystem. Essentially from an end user’s perspective, the BI subsystem is the entire solution, whereas the rest, the “big machine”, is under the hood, known only to a select few. However, a post on the big machine is for another time. In this post we shall focus on the BI subsystem.

Regardless of the size of the organization it is essential to have a strategy for business intelligence and how it should be implemented. The strategy may not even need a big machine under the hood, and its function can be handled by a tool like Power BI in many cases.

When putting together a business intelligence strategy using Power BI, Microsoft recommends three primary strategies that an organization can adopt. Out of these, the one that I tend to go with is managed self-service BI, which brings forth the concept of discipline at the core, flexibility at the edge. This concept is the dominant strategy used for BI at Microsoft itself; explained very nicely in this article. It’s my personal favorite, because I find it an effective means of onboarding customers once the core platform is built with the required standards (discipline), and then help them adopt the solution from the edge, thus providing them with the best of both worlds.

Source: Microsoft

The core consists of three elements:

1. The single master data source: which in most cases is the “big machine”, where data from all relevant systems are brought together, processed and made available for analysis),
2. Standard corporate BI (which are the measures and dimensions set up in a standard manner and applicable across the organization) and
3. Consistent taxonomies (where standard definitions and hierarchies are established for organizational KPIs along with calculations that depict the definitions)

The latter two can be realized using Power BI datasets (sometimes in collaboration with a data warehouse).

Core

So, when implementing the core, standard corporate BI with the consistent taxonomies can be built as a dataset (or bunch of datasets) per business function, such as, a sales dataset, a finance dataset, a marketing dataset and so forth.

The below example, shows a finance dataset and two sales datasets at the core:

_{Core models in core workspace}

Edge

The analytics are now made available for end users to build their reports and dashboards off of at the edge, i.e., at their departmental or functional levels. Each user or group of users will create their own workspaces where they will self-serve the metrics and data from the core models, and author their content for various purposes. For instance, the sales team will have their package of monthly reports, a package of dashboards to present at the quarterly sales review, and so forth.
This way the need for users and analysts to collect, collate and build their own datasets is greatly reduced since the metrics and KPIs they need are already pre-created using a high standard. Note the reports in the example below where they are sourced off the core models.

_{Sales analysis in an edge workspace}

In the next couple of posts, I shall be getting a little into the internals of the core.

Import data from a SharePoint folder in Power BI

I’ve been asked the question many times, especially when I teach Dashboard in a Day: How do you import data from a bunch of Excel files stored on a SharePoint folder?

It’s a fairly simple process. It’s just that you need to know that you first connect to the SharePoint site (which could have quite a large number of folders and files, and several document libraries. You will need to know the path of your files, and then you keep filtering till you get to your content. I do this in two steps:

1. Filter the document library
2. Filter the folder path

In my example. I have four sales target spreadsheets: all with the same structure from different years. They are stored in a SharePoint folder, as follows. I’ve also marked how to identify the site, the document library and the folder path:

The following video takes you through the steps to bring this data to Power BI:

and there you have it! Importing data from a SharePoint folder to Power BI. If it’s SharePoint Online, then you would not even need a gateway to setup refresh schedules.

Of KPI Trees and Scorecards: Using Goals in Power BI

I recently spoke about Goals in Power BI at the local Power BI meetup.

The feature is currently in preview, introduced some 8 months ago, and has quite a lot of promise. For me, it is particularly exciting since I am working with a large customer, who is a perfect candidate to implement goals for. So, what is Goals in Power BI?

Let us take a quick scenario first: Organizations, regularly (if not frequently) monitor indicators of their business performance to ensure their goals and aspirations are met. Sometimes these aspirations are difficult to keep track of due to various complexities. Consider a goal called Reduce employee turnover and increase satisfaction (something that I picked up from here). To effectively understand and track its progress, the organization would probably have a few key performance indicators (KPIs) that make it easy to look at reducing employee turnover and increasing satisfaction objectively. One such KPI could be a low human capital Turnover Rate while another could be a high Employee Satisfaction Indicator. Collectively these KPIs will help determine the achievement of the goal within a stipulated period (such as a calendar year). Similarly an organization will have many goals that are aligned to organizational KPIs or metrics. Sometimes, certain KPIs/metrics may cascade down the organization’s departments, where each department’s performance determine the overall organizational performance.

Goals (preview) in Power BI

Goals in Power BI breathes new life into the scorecards functionality that was familiar to many of us BI folk once upon a time (pre-Power BI times). In Power BI, goals are contained within a Power BI artefact called a scorecard. A goal, (at the time of writing), has:

• A current value (that is either manual, or data-driven) of the metric
• A target value (that is either manual, or data-driven) for the metric
• A status (which can be manually updated, or rule-driven) to indicate how the goal is faring
• A start and end date for the goal
• Owner(s) of the goal to drive accountability

– New Goal –

Once set up with all organizational goals; the owners will need to regularly monitor the goal, and keep track of it. Power BI provides options for owners to regularly check-in on their goals, and update statuses. A rule-driven approach can also be used to keep goals up to date.

A Use Case

If you were wondering what an ideal use case is for Goals in Power BI, consider a KPI tree. Imagine a set of strategic goals that an organization aspires to achieve during the financial year. The goals are spread across multiple departments, while some goals may have sub-goals.

Now, once the goals, and sub-goals are all determined, you choose the KPIs/metrics that would drive the goals, along with the targets for these KPIs/metrics. Where would you choose these KPIs/metrics and their targets from? A dashboard that is used to monitor organizational and departmental KPIs; ideally an executive dashboard that is regularly used to run the business. Even more ideal is if the dashboard is powered by a certified dataset that is a result of a standard architecture and analytics process – essentially your core dataset(s). It is important to configure the KPI/metric that is measured by the goal by filtering it, for instance for the current year, or the department for which the goal applies to. Targets for the goals, too can come from a dashboard, or even be set to a manual value in its absence.

Given here is a sample scorecard with goals, just to envision how an organizational KPI tree may look like. You first set it up with all the goals, subgoals and such.

– Sample scorecard with goals –

You then link each of them to a KPI/metric from an appropriate dashboard. The required filters can be configured when selecting the KPI/metric at this time, so that it aligns with the context of the goal.

Envision and what’s next

Now, envision this; owners accountable for the goals regularly check-in on their goals. To see the status of each goal in the real world, they tap into dashboard directly from the goal. Based on what they see and analyze, they go back to the goal and update their check-in with a status.

– Go-to dashboard for more context or to analyze –

If the goal is tracked via rules, the status and trend will indicate if the going is good or not. If it does not look good, the owner goes into the dashboard, drills down to where the problem is, or perform some ad hoc analysis on the problem, and then updates their check-in.

– Checking in on a goal –

A couple of, what I would consider, must-have functionality in Goals for Power BI is the ability for subgoals to rollup to the main goal, and for a goal to to cascade down a hierarchy (such as departments). According to the current Power BI release wave, both these functionality should be available for preview next month. That would definitely require a follow up post.

Additional reading

• Introducing Goals in Power BI
• Get started with goals in Power BI