James Fancke

Hierarchies #1: Introduction to hierarchies in Analysis Services and Power BI

A hierarchy is a concept where related items are represented as being above, below, or at the same level as each other.

Some examples of hierarchies include:

Organisation charts with different levels of managers and employees;
Calendars with dates organized in years, months, and days;
Products arranged into different classes, groups, or brands; and
Bills of material grouped into sub assemblies, kits, and components.

Each of these hierarchies include “parent” members and “child” members beneath them, and serve as a way of organizing information into different levels. Continue reading “Hierarchies #1: Introduction to hierarchies in Analysis Services and Power BI”

KPIs #2: Developing KPIs in Power BI and Analysis Services using Tabular Editor

This is a follow up to my post exploring KPIs within Analysis Services. You may want to read that post first as it provides an introduction to KPIs.

I received some feedback from Daniel Otykier (author of Tabular Editor) regarding my last post and it inspired me to dive deeper into how KPIs are handled within Analysis Services, how you can create them using Tabular Editor, as well as their implementation within Power BI.

Continue reading “KPIs #2: Developing KPIs in Power BI and Analysis Services using Tabular Editor”

KPIs #1: KPI usage in Analysis Services, Excel Power Pivot, and Power BI 🚦

KPIs, or Key Performance Indicators, are a common way to gauge performance against a set of objectives, and are an integral part of performance management.

Without getting too deep in jargon, the fundamental idea behind a KPI is that you are comparing some known measure against a target, and then grading the comparison.

Continue reading “KPIs #1: KPI usage in Analysis Services, Excel Power Pivot, and Power BI 🚦”

Creating a date table in Power Query

I use “columns” and “fields” interchangeably in this post.

There are many posts out there showing how to create a date table and I believe this is because it’s a common problem that almost everyone encounters when first making a data model in Power BI, Excel, or Analysis Services.

By “common problem” I mean that it’s the one dimension that almost every data model shares, and fitting it to your data model can sometimes be a bit tricky.

You can create date tables in SQL, DAX, M, and many other programming languages.

In this post I want to show you how you can make a date table in Power Query’s M language, as well as explain what fields I think are important and how you can create them.

Continue reading “Creating a date table in Power Query”

Workarounds for Analysis Services database admins without server admin access

This is a somewhat obscure topic, but I hope that others (if they ever find themselves in this position) find it useful!

In Analysis Services you can have administrator rights over an entire server instance, as well as administrator rights over particular databases on the server instance.

Continue reading “Workarounds for Analysis Services database admins without server admin access”