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”

Converting from UTC to Local Timezone in Power BI and Azure Analysis Services

This post is a quick response / supplement to Kasper’s post “Show the refresh date\time in a Power BI report and dashboard“.

Currently there is a small issue when using the DateTime.LocalNow() (and other similar functions) within the Power BI Service or Azure Analysis Services when processing a refresh of data.

Continue reading “Converting from UTC to Local Timezone in Power BI and Azure Analysis Services”

How to automate (Azure) Analysis Services processing from a remote PC

There are many blog posts showing how to automate processing of a SQL Server Analysis Services model using either the SQL server agent or SSIS.

There are also many posts out there showing different ways of processing an Azure Analysis Services database through the use of Azure Functions, automation accounts, and runbooks. (SQLDusty.com, technet, Azure blog, official AAS docs, byobi.com)

Both of the above require some level of server administration privileges or Azure resource deployment.

What if you don’t have server admin rights or can’t create Azure resources?

Continue reading “How to automate (Azure) Analysis Services processing from a remote PC”