How to setup Azure Log Analytics for Power BI workspace

Power BI is integrating with Azure Log Analytics (LA) to enable administrators and Premium workspace owners to configure a Log Analytics connection to their Power BI subscription. 

Once connected to Power BI, data is sent continuously and is available in Log Analytics in approximately 5 minutes. The following diagram shows how Azure Monitor operates, with the path taken by Power BI highlighted.

An Azure Log Analytics connection is currently supported for Premium workspace in Power BI. You must configure this at the workspace level as shown below.

Must be allowed by the tenant administrator under Tenant settings

Before configuring this, you must create a Log Analytics Workspace and assign the owner role for Power BI Service app.

Once you configure the Log Analytics for a workspace, the data will be available in 5 minutes. Click on Locks in Log Analytics in Azure Portal and run your queries.

You can see there is table called PowerBIDatasetsWorkspace

You can run a KQL query and analyze the data, the following query is showing count. Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modelling, and more.

 

You can also use pre-defined queries and modifies as per your requirements

 

In addition to this, you can download power bi report templates from Microsoft. All you need just provide the parameter values and publish them in Power BI service.

Please read here for more details

https://docs.microsoft.com/en-us/power-bi/transform-model/log-analytics/desktop-log-analytics-overview

https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/

Cheers!
Uma

How to retrieve data from Snowflake database in SSIS

First you need to install SnowflakeDSIIDriver and set up an ODBC data source. You can find more details about the driver and the connection details here https://docs.snowflake.com/en/user-guide/odbc-windows.html

Use ODBC source in SSIS package.

If you find any performance issues due to most of the fields are in "DT_TEXT", then use CASE statement to convert as VARCHAR in SQL command.

Cheers!
Uma

How to get Power BI Report Server (on-premise) Key from purchased Power BI Premium

If you have purchased Power BI Premium, within the Capacity settings tab of the Power BI admin portal, you will have access to your Power BI Report Server product key. This will only be available for Global Admins or users assigned the Power BI service administrator role.

In the admin portal, navigate to Capacity settings, then select Power BI Premium capacity.

Cheers!
Uma

Configuring dataflow storage to use Azure Data Lake Gen 2

Data used with Power BI is stored in internal storage provided by Power BI by default. With the integration of dataflows and Azure Data Lake Storage Gen 2 (ADLS Gen2), you can store your dataflows in your organization's Azure Data Lake Storage Gen2 account. This essentially allows you to "bring your own storage" to Power BI dataflows, and establish a connection at the tenant or workspace level.

Cheers!
Uma

How to connect Power BI SSAS cube via SQL Server Management Studio using XMLA endpoint

Power BI Premium, Premium Per User, and Power BI Embedded workspaces support open-platform connectivity from Microsoft and third-party client applications and tools by using an XMLA endpoint.

What's an XMLA endpoint?

Workspaces use the XML for Analysis (XMLA) protocol for communications between client applications and the engine that manages your Power BI workspaces and datasets. These communications are through what are commonly referred to as XMLA endpoints. XMLA is the same communication protocol used by the Microsoft Analysis Services engine. Data sent over the XMLA protocol is fully encrypted.

It's a Power BI capacity setting. The XMLA Endpoint setting applies to all workspaces and datasets assigned to the capacity.

Another special Tenant setting for Excel analytics: Allow XMLA endpoints and Analyze in Excel with on-premises datasets is an integration setting.

The read-only connectivity using the endpoint is enabled for the Datasets workload in a capacity. With read-only, data visualization applications and tools can query dataset model data, metadata, events, and schema. 

The Read-write operations using the endpoint can be enabled providing additional dataset management, governance, advanced semantic modelling, debugging, and monitoring. With read-write enabled, datasets have more parity with Azure Analysis Services and SQL Server Analysis Services enterprise grade tabular modelling tools and processes.

You can get the XMLA link under Power BI workspace settings:

Workspaces assigned to a capacity have a connection string in URL format like this,
powerbi://api.powerbi.com/v1.0/[tenant name]/[workspace name].

Applications connecting to the workspace use the URL as if it were an Analysis Services server name. For example,
powerbi://api.powerbi.com/v1.0/contoso.com/Sales Workspace.

Users with UPNs in the same tenant (not B2B) can replace the tenant name with myorg. For example,  powerbi://api.powerbi.com/v1.0/myorg/Sales Workspace.

B2B users must specify their organization UPN in tenant name. For example,
powerbi://api.powerbi.com/v1.0/fabrikam.com/Sales Workspace.

Using SSMS studio, you must use Azure Active Directory username and password, unless AAD integrated with Organization account. You need to make sure you have an appropriate permission to connect.

You can connect using many tools

- Visual Studio with Analysis Services projects 

- SQL Server Management Studio (SSMS)

- SQL Server Profiler

- Analysis Services Deployment Wizard

- PowerShell cmdlets

- Power BI Report Builder

- Tabular Editor

- DAX Studio

- ALM Toolkit

- Microsoft Excel

- Third party

You can read more updated details here

https://docs.microsoft.com/en-us/power-bi/admin/service-premium-connect-tools

Cheers!
Uma

How to create Gateway cluster in Power BI

Open existing on-premises data gateway and add a new gateway using “Add to an existing gateway cluster”. You can choose to use all the gateway-like round robin way, even without failure of the cluster.

Cheers!
Uma

How to replace invisible ASCII special characters or control characters

One of the common challenges of transforming data that could get complicated is the removal of ASCII special characters such as newlines or tabs. In this blog, we take a look at some details about the ASCII characters and ways of removal. Let’s look at the type of ASCII character that involves & ways to replace data transformation. Mostly we can use the REPLACE command to clear these but need more understanding to identify the special characters.

ASCII function: Returns the ASCII code value of the leftmost character of a character expression.

CHAR function: This function converts an int ASCII code to a character value.

Commonly used ASCII Printable Characters

Control characters

Use CHAR to insert control characters into character strings. This table shows some frequently used control characters.

This example uses CHAR (13) to print the name and e-mail address of an employee on separate lines, when the query returns its results as text. 

Another important thing is to identify the special characters, most of these control characters are not visible in usual applications.

To view ASCII control characters, you can use Notepad++ or SSMS

In Notepad++ enable Show All Character option.

In SSMS enable Retain CR/LF on copy and save option.

Cheers!
Uma

Creating Azure Databricks workspace and explore

Azure Databricks is a data analytics platform optimized for the Microsoft Azure cloud services platform. Azure Databricks offers three environments for developing data intensive applications: Databricks SQL, Databricks Data Science & Engineering, and Databricks Machine Learning.

You are redirected to the Azure Databricks portal.

You can format it

Click on present the workbook

Cheers!
Uma