Azure, Azure Analysis Services, BISM Tabular, On-premises Data Gateway Enterprise Mode

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” →

Azure, Azure Analysis Services, Azure Data Factory, Azure Data Lake, Power BI, Power BI Desktop, SQL Server, SQL Server Integration Services - SSIS, Tampa Dev

Azure Data Lake, Azure Analysis Services, Power BI and How They Relate

Loading Data From On-prem SQL Server to Azure Data Lake Store and Data Visualisation in Power BI

I recently spoke in Tampa Dev Azure Meeting in a webinar form on 31st Jan 2018. We received interesting questions during the presentation. My aim was to introduce basic concepts of Big Data, Azure Data Lake, Azure Data Lake Store (ADLS), Azure Data Factory (ADF)  and Power BI.

I would like to thank Tampa Dev organisers and all attendees for giving the opportunity to speak in this session.

In this session you learn basic concepts of:

  • Big Data
  • Azure Data Lake
  • Azure Data Lake Store (ADLS)
  • Azure Data Factory (ADF)
  • Azure Analysis Services (AAS)
  • Power BI
  • And how they relate

Session recording:

Continue reading “Azure Data Lake, Azure Analysis Services, Power BI and How They Relate” →

BISM Tabular, DAX, Power BI, Power BI Desktop, Power Query, SQL Server Analysis Services - SSAS, SQLite

SSAS Tabular Model Documenter with Power BI without DMVs

SSAS Tabular Documenter with Power BITechnology is growing fast and we are enjoying it. We reshape our daily created data in a form that satisfies our needs. One of the technologies which is used more commonly these days is SQL Server Tabular Models, SSAS Tabular in short. Lots of industries decide to go with SSAS Tabular in their new projects and some defined new projects to slowly switch their existing SSAS Multidimensional to SSAS Tabular. I know, there is a big debate around SSAS Multidimensional vs. SSAS Tabular. But my aim is to prevent going through that sort of discussion. As the title implies, this post is about documentation that I believe is one of the most important parts of every project which is also sacrificed the most. In this post I explain how to document your SSAS Tabular model in Power BI Desktop and Excel. I know, there are some products you can find in the internet that can generate documentation in various formats like Word, PDF, HTML and so on. But, If you’re looking for a free and somehow more intuitive way of documenting your SSAS Tabular Models with Power BI then this article is for you.  Through this article, we create a documentation tool with Power BI. I call it SSAS Tabular Model Documenter. In this method we don’t use DMVs at all. For those who are not familiar with DMVs I shortly explain what DMVs are, if you’re already familiar with DMVs you can jump this section.

You can download a copy of SSAS Tabular Model Documenter in Power BI template format (pbit) at the end of this post. It is a Christmas present for you.

What are DMVs?

Dynamic Management Views, DMVs in short, are queries that retrieve metadata information about an instance of SQL Server Analysis Services. DMVs work on both SSAS Multidimensional and SSAS Tabular server modes. DMVs can be used to monitor server operations and health. The DMV query structure is very similar to T-SQL, therefore you use “SELECT” statement followed by “$System” which is an XMLA schema rowset. The DMV queries look like below:

SELECT * FROM $System.<schemaRowset>

So you can open SQL Server Management Studio, connect to an instance of SSAS (Tabular model for the sake of this post) and run the following query to get lots of information about tables in your Tabular model:

select * from $SYSTEM.TMSCHEMA_TABLES

Running DMVs in SSMSRead more about DMVs here.

Note:This method only works with SSAS Tabular 2016 and above.

Document SSAS Tabular without DMVs

In SSAS Tabular 2016 and above there is a tiny metadata database that can be loaded in Power BI Desktop or Excel to document the corresponding SSAS Tabular model. The database is a SQLite database. In the previous post I explained how to visualise SQLite data in Power BI. The requirements for this post are the same as the previous post, so I encourage you to check it out. Therefore, I just explain how to find the metadata file and how to build a model in Power BI Desktop. I also explain how to do the same in Excel for those of you who would like to add some annotations or comments to the outcomes.

Continue reading “SSAS Tabular Model Documenter with Power BI without DMVs” →

Power BI, Power BI Desktop

Visualise SQLite Data in Power BI

SQLite + Power BI

As the name implies, SQLite is a light weight transactional SQL database engine. It is open-source and free for use either for personal commercial. SQLite is widely used in mobile apps and many other desktop applications that need an internal light weight free SQL database. In this post I explain how to visualise SQLite data in Power BI.

Requirements

To be able to go through the process you need to meet the following requirements:

  • Latest Version of Power BI Desktop (Current version: 2.52.4921.682 64-bit (November/2017))
  • Install SQLite ODBC driver: Make sure you install 64 bit version if your Power BI Desktop is 64 bit version like mine!

Note: You may install both x32 and x64 bit versions of the driver if your other applications are in x32 bit.

  • An existing SQLite database

Note: In case you just want to try this and you don’t currently have a SQLite database you can download a SQLite version of “Internet Sales” of AdventrueWorksDW2016 that I made available for you.

How it works

Like any other Power BI cases, it’s really easy to load data from an existing SQLite database to a Power BI Desktop model. You just need to use ODBC data connection and connect to a predefined “ODBC DNS” OR use a “Connection String”. I’ll explain both methods. After you load data to your Power BI Desktop, you create the relationships then you’re good to go and create flashy reports. Continue reading if you’re interested to an step-by-step guide to visualise SQLite data In Power BI.

Importing SQLite Data to Power BI Using ODBC DSN

  • Open the correct version (x32, x64) of ODBC after you downloaded and installed SQLite ODBC Driver
  • Click “Drivers” tab and make sure SQLite ODBC Driver(s) successfully  installed

ODBC Drivers Continue reading “Visualise SQLite Data in Power BI” →