Quick Tips: Time Dimension with Granularity of Seconds in Power BI and Analysis Services Tabular Models

Quick Tips: Time Dimension with Granularity of Seconds in Power BI and Analysis Services Tabular Models
Photo by Markus Spiske

In an article I posted a while back I showed different methods of creating Time dimension in Power BI and Tabular models. The Time dimension I explained was in Minutes. In this post I show you simple way to create Time dimension supporting Seconds. As this is a quick tip, I only show you how to get the Time and ID columns in the Time dimension. If you need to add time bands (time buckets) check this out for more details.

Time Dimension in Seconds Grain with Power Query (M):

Copy/paste the code below in 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]))),
#"Changed Type" = Table.TransformColumnTypes(#"Time Column Added",{{"ID", Int64.Type}, {"Time", type time}})
in
#"Changed Type" 
Time Dimension in Power Query

Time Dimension in Seconds Grain with DAX:

Run the DAX expression below in a new calculated Table in Power BI or SSAS Tabular model:

Time in DAX = ADDCOLUMNS(
                       GENERATESERIES(1, 86400, 1)
                        , "Time", TIME(0, 0, 0) + [Value]/86400
                        )
Time Dimension in Seconds Level with DAX
Continue reading “Quick Tips: Time Dimension with Granularity of Seconds in Power BI and Analysis Services Tabular Models”

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”