Quick Tips: Time Dimension with Time Bands at Seconds Granularity in Power BI and SSAS Tabular

Time Dimension with Time Bands at Seconds Granularity in Power BI and SSAS Tabular

I wrote some other posts on this topic in the past, you can find them here and here. In the first post I explain how to create “Time” dimension with time bands at minutes granularity. Then one of my customers required the “Time” dimension at seconds granularity which encouraged me to write the second blogpost. In the second blogpost though I didn’t do time bands, so here I am, writing the third post which is a variation of the second post supporting time bands of 5 min, 15 min, 30 min, 45 min and 60 min while the grain of the “Time” dimension is down to second. in this quick post I jump directly to the point and show you how to generate the “Time” dimension in three different ways, using T-SQL in SQL Server, using Power Query (M) and DAX. Here it is then:

Time Dimension at Second Grain with Power Query (M) Supporting Time Bands:

Copy/paste the code below in Query Editor’s Advanced Editor to generate Time dimension in Power Query:

let
Source = Table.FromList({1..86400}, Splitter.SplitByNothing()),
#"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "ID"}}),
#"Time Column Added" = Table.AddColumn(#"Renamed Columns", "Time", each Time.From(#datetime(1970,1,1,0,0,0) + #duration(0,0,0,[ID])), Time.Type),
    #"Hour Added" = Table.AddColumn(#"Time Column Added", "Hour", each Time.Hour([Time]), Int64.Type),
    #"Minute Added" = Table.AddColumn(#"Hour Added", "Minute", each Time.Minute([Time]), Int64.Type),
    #"5 Min Band Added" = Table.AddColumn(#"Minute Added", "5 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/5) * 5) + 5, 0)), Time.Type),
    #"15 Min Band Added" = Table.AddColumn(#"5 Min Band Added", "15 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/15) * 15) + 15, 0)), Time.Type),
#"30 Min Band Added" = Table.AddColumn(#"15 Min Band Added", "30 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/30) * 30) + 30, 0)), Time.Type),
#"45 Min Band Added" = Table.AddColumn(#"30 Min Band Added", "45 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/45) * 45) + 45, 0)), Time.Type),
#"60 Min Band Added" = Table.AddColumn(#"45 Min Band Added", "60 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/60) * 60) + 60, 0)), Time.Type),
    #"Removed Other Columns" = Table.SelectColumns(#"60 Min Band Added",{"Time", "Hour", "Minute", "5 Min Band", "15 Min Band", "30 Min Band", "45 Min Band", "60 Min Band"})
in
    #"Removed Other Columns"
Continue reading “Quick Tips: Time Dimension with Time Bands at Seconds Granularity in Power BI and SSAS Tabular”

Quick Tips: Export Power BI Desktop and Power BI Service Model Data In One-Shot with DAX Studio

Exporting Model Data to CSV 
or SQL Server in One Shot

Update 2021 March:

You can now export the data direct from Power BI Desktop using my tool, Power BI Exporter. Read more here.

In some of my old posts, which are the most popular ones, I explained how to Export data Power BI Desktop or Power BI Service data to different destinations like CSV, Excel and SQL Server. In this quick tip I explain a very easy way to export the model data as a whole to either CSV or SQL Server with DAX Studio.

Daniil from XXL BI well explained this method, but I’d rather quickly explain how it works and add some more information.

After release 2.8 of DAX Studio, you can now quickly export the whole model to CSV and SQL Server in one shot.

Enabling Export All Data in DAX Studio

  • Open DAX Studio
  • Click “File”
  • Click “Options”
  • Click “Advanced”
  • Tick “Show Export All Data button”
DAX Studio Export Power BI Model Data Settings

Export Power BI Model Data to CSV

DAX Studio Export Power BI Model Data to CSV
Continue reading “Quick Tips: Export Power BI Desktop and Power BI Service Model Data In One-Shot with DAX Studio”

Preparing On-premises Data Gateway Implementation Plans for Enterprises

Definitive Guide to On-premises Data Gateway Implementation
Photo credit: Kayla Duhon

If you are a Business Intelligence consultant working on Power Platform, Azure Logic Apps and Azure Analysis Services landscape, you probably know that On-premises Data Gateway cab be one of the most essential parts of your engagements with your customers. In many cases, installing On-premises Data Gateway can be a one-man-band job but in many others, it requires teamwork effort. Either way, it can go smoothly if you already have a well-thought implementation plan otherwise, it can quickly turn into a beast that can exhaust the whole implementation team and the customer for some days.

In this post, I do my best to provide you with some guidelines that can help you with your On-premises Data Gateway implementation planning. This post may look rather long, and some of the points are generic, but it is worthwhile mentioning them. Consider the following points before, during and after the engagement:

  • Understanding the use cases
  • Culture of the engagement
  • Environments (Dev, UAT, Prod)
  • Communication
  • Security
    • Corporate/environmental firewalls
    • Proxy Servers
    • Identity Access Management
  • People
  • Documentation
  • Installation, configuration, and testing

Here is a diagram of the important points that you should consider:

Implementing On-premises Data Gateway
Implementing On-premises Data Gateway

Use cases

You need to understand the use cases of On-premises Data Gateway (Standard Gateway) for your customer. If they need the gateway for their Power Platform, Azure Logic Apps, Azure Analysis Services or all of them. This is important as you either need to have access to your customer’s Power BI Service or Azure Portal or both, or you need to assist your customer to configure On-premises Data Gateway in Azure or in Power BI Service. The next points are:

  • Accessing customer’s Azure Portal and/or Power BI Service: The customer to decide whether to create a new account with sufficient rights for you or give you the credentials of an existing account. It is important to make sure you can access all environments and you have necessary rights to install/configure the gateway
  • You assist/consult a person at customer side with the implementation: you need to make sure you communicate with that person and see if he/she understands the requirements before the implementation date. Send them a calendar invitation beforehand to make sure he/she is present at that date. Always ask for a backup person just in case of an emergency happening to the primary person.
Continue reading “Preparing On-premises Data Gateway Implementation Plans for Enterprises”

On-premises Data Gateway for Azure Analysis Services

On-prem Data Gateway for Azure AS, How it works DemoFrom April 2017 onward we can use On-premises Data Gateway for Azure Analysis Services. This means  you can connect your Tabular Models hosted in Azure Analysis Services to your on-premises data sources through On-premises Data Gateway. In this post you learn how to setup and configure the On-premises Data Gateway as well as how to configure Azure Analysis Services to connect to the on-premises database(s) via the gateway.

Requirements/Assumptions

  • You have an Azure subscription
  • You already have an up and running instance of Azure Analysis Services (AAS)
  • You already installed and configured the latest version of On-premises Data Gateway
  • Your instance of Azure Analysis Services, your instance of Power BI Service and your On-premises Data Gateway are all in the same “Region” (Check Prerequisites section below for more details)
  • This post covers the most common scenario which doesn’t involve Kerberos, Proxy Server, OKTA etc…

Reading: If you are not that familiar with On-premises Data Gateway then I encourage you to read this and for more in-depth read this article. You may also watch this video from Adam Saxton discussing On-premises Data Gateway.

Prerequisites

As stated in the “Requirements” section, it is important to check if your instances of Azure Analysis Services, Power BI Service and On-premises Data Gateway located in the same “Region”.

The reasons behind that are:

  • Your On-premises Data Gateway MUST be installed in the same Region as your Power BI Service sits in. Otherwise your Power BI cannot see the gateway, therefore, you’re unable to schedule automatic data refresh in Power BI Service
  • To get the best performance and more reliability, you have to create On-premises Data Gateway Resource in Azure side in the same region as your Azure Analysis Services lives in

But, in some cases people created their Power BI account a long time ago and perhaps they didn’t set the right region for their Power BI Service to sit in. Now, they need to create an instance of Azure Analysis Services, but, due to their organisation privacy and security, they don’t want to (or perhaps not allowed to) create the instance of Azure Analysis Services in the region that their Power BI tenant sits in. So they would prefer to create the Azure Analysis Services in another region. In that case, it is recommended to install a new instance of On-premises Data Gateway in a separate server and change the “Gateway Region” during the installation.

To check the “Region” of your instances follow the steps below:

Power BI Service Region:

  • Login to your Power BI Service
  • Click the question mark on top right side of the page
  • Click “About Power BI”

Continue reading “On-premises Data Gateway for Azure Analysis Services”