Category: Self-Service BI

Business Intelligence - BI, On-premises Data Gateway Enterprise Mode, Power BI

Quick Tips: On-premises Data Gateway, Network Ports Test

Network Ports Test in On-premises Data Gateway

The latest version of On-premises Data Gateway February 2019 (3000.1.257) has been released on 1st March 2019. The release note is quite short and it doesn’t really provide a lot of information. At least that’s the case as at today (6 March 2019).

There is One new feature however, that I found very interesting is the “Network Ports Test” under the “Diagnostics” tab. This is an important feature I believe that gives you the ability to deal with your network administrators or your security officers much easier. You can now easily run a test directly from the gateway UI and pass the test results to your network admins if any of test steps fails.

As you can see in the screenshot you can click on “Start new test” and then open the completed test results to send them back to your networking team is needed.

That was really quick wasn’t it?

Smile

Check this out if you want to learn about processes involved with implementing On-premises Data Gateway across organisations and how to com up with an implementation plan.

WARNING: The above post is a really long one.

Smile

Azure Analysis Services, BISM Tabular, Business Intelligence - BI, Data Warehousing - DW, DAX, Excel, Power BI, Power BI Desktop, Power Pivot, Power Query, Quick Tips, SQL Server, SQL Server Analysis Services - SSAS, T-SQL

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” →
Azure, Azure Analysis Services, BISM Multidimensional, BISM Tabular, Business Intelligence - BI, On-premises Data Gateway Enterprise Mode, Power BI, Power BI Desktop, Power BI Service, SQL Server, SQL Server Analysis Services - SSAS

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” →
Azure, Azure Analysis Services, BISM Tabular, Business Intelligence - BI, DAX, Dynamic Management Views (DMV), Power BI, Power BI Desktop, SQL Server, SQL Server 2016, SQL Server Analysis Services - SSAS, SQL Server Management Studio - SSMS, T-SQL

Automate Testing SSAS Tabular Models

Automate Testing SSAS Tabular

In real world SSAS Tabular projects, you need to run many different testing scenarios to prove your customer that the data in Tabular model is correct. If you are running a Tabular Model on top of a proper data warehouse then your life would be a bit easier than when you build your semantic model on top of an operational database. However it would be still a fairly time-consuming process to run many test cases on Tabular Model, then run similar tests on the data warehouse and compare the results. So your test cases always have two sides, one side is your source database that can be a data warehouse and the other side is the Tabular Model. There are many ways to test the system, you can browse your Tabular Model in Excel, connecting to your Data Warehouse in Excel and create pivot tables then compare the data coming from Tabular Model and the data coming from the Data Warehouse. But, for how many measures and dimensions you can do the above test in Excel?

The other way is to run DAX queries on Tabular Model side. If your source database is a SQL Server database, then you need to run T-SQL queries on the database side then match the results of both sides to prove the data in Tabular Model is correct.

In this post I’d like to share with you a way to automate the DAX queries to be run on a Tabular model.

Straight away, this is going to be a long post, so you can make or take a cup of coffee while enjoying your reading.

While I will not cover the other side, the source or the data warehouse side, it is worth to automate that part too as you can save heaps of times. I’m sure a similar process can be developed in SQL Server side, but, I leave that part for now. What I’m going to explain in this post is just one of many possible ways to generate and run DAX queries and store the results in SQL Server. Perhaps it is not perfect, but, it is a good starting point. If you have a better idea it would be great to share it with us in the comments section below this post.

Requirements

  • SQL Server Analysis Services Tabular 2016 and later (Compatibility Level 1200 and higher)
  • An instance of SQL Server
  • SQL Server Management Studio (SSMS)

How does it work

What I’m going to explain is very simple. I want to generate and run DAX queries and capture the results. The first step is to get all measures and their relevant dimensions, then I slice all the measures by all relevant dimensions and get the results. At the end I capture and store the results in a SQL Server temp table. Let’s think about a simple scenario:

  • you have just one measure, [Internet Sales], from ‘Internet Sales’ table
  • The measure is related to just one dimension, “Date” dimension
  • The “Date” dimension has only four columns, Year, Month, Year-Month and Date
  • you want to slice [Internet Sales] by Year, Month, Year-Month and Date

So you need to write four DAX queries as below:

EVALUATE
SUMMARIZE(
    'Internet Sales'
    , Date'[Calendar Year]
    , "Internet Sales", [Internet Total Sales]
)
EVALUATE
SUMMARIZE(
   'Internet Sales'
   , 'Date'[Month Name]
   , "Internet Sales", [Internet Total Sales]
)
EVALUATE
SUMMARIZE(
    'Internet Sales'
   , 'Date'[Year-Month]
   , "Internet Sales", [Internet Total Sales]
)
EVALUATE
SUMMARIZE(
     'Internet Sales'
    , 'Date'[Date]
    , "Internet Sales", [Internet Total Sales]
)

It is easy isn’t it? But, wait. What if you have 10 measures related to 4 dimension and each dimension has 10 columns? That sounds laborious doesn’t it? Well, in real world scenarios you won’t slice all measures by all relevant dimensions, but, you still need to do a lot. What we are going to do is to generate and run the DAX queries and store the results in a table in SQL Server. How cool is that?

OK, this is how it works…

  • Creating a Linked Server for SSAS Tabular instance from SQL Server
  • Generating DAX queries using Tabular DMVs
  • Running the queries through Tabular model and getting/storing the results in a SQL Server temp table

Continue reading “Automate Testing SSAS Tabular Models” →