Azure

Getting Tabular Object Model from Power BI XMLA Endpoint

It’s very easy to get TOM (Tabular Object Model) for datasets hosted in Power BI Service and extract various metadata from it (tables, measures, etc.). We will do it just now using a simple C# console application and download TOM for one sample dataset using Power BI User credentials. Similarly,…

Read more
Azure

Accessing the Power BI Admin API as Service Principal

End of the year 2020 Microsoft extended the Power BI Service API with new admin access-related endpoints and added the option to use Service Principal instead of a dedicated administrative account for connection. You can read more details on it in this official Power BI  Blog post. In this step-by-step…
Azure

Power BI API Connector (sample application)

PowerBIApiConnector is a simple console application that will help you to test two things: a connection to Azure Active Directory and that you have properly configured an Azure application to access the Power BI Admin Read-Only API. Application is created in .NET.Core 5.0 and you can download it as: PowerBIApiConnector_Executable…
T-SQL

OFFSET with FETCH NEXT and Clustered Index Scans

The OFFSET… FETCH NEXT extension to SELECT statement was introduced first in SQL Server 2012. It should basically support simple pagination of query results from the application. For large datasets, it’s really important to understand how it is internally implemented, and based on these findings, I won’t recommend using it…

Read more
T-SQL

Updating LOBs using the WRITE() column method

The WRITE (expression,@Offset,@Length) extension of the UPDATE statement can be used to perform updates that insert or append new data in minimally logged mode if the database recovery model is set to bulk-logged or simple. Minimal logging is not used when existing values are updated. This all is valid for…
DBA

DAC cannot be used to perform database backup

DAC (Dedicated Administrator Connection) is a well-known function of SQL Server for the case that the server isn’t responding to regular connection for various reasons like heavy-load etc.  Having the magic DAC in the pocket can lead administrators to the opinion that they are save in such a case, that…
SQL Server

Scalar Functions and Parallel Execution Plans

This article is just a simple demonstration of improvements in scalar functions handling implemented in SQL Server 2019. Prior to this version when the scalar function was used in a query, it was blocking parallel plan generation. You can read more on this topic in this article. One of the…

Read more
T-SQL

What is the default data type of NULL?

When a NULL value is stored in a column it’s easy to say what is the data type of it: it’s the data type of the column. This means that the NULL value stored in the DateTime column can be used in all the date & time functions like DATEADD()…
BI

Extracting Metadata from the Power BI Report File

The more and more is Power BI used everywhere it’s starting to be really painful to incorporate it in any metadata management solution. This article is a result of 10 hours investigation on how to get metadata from Power BI files. To be honest, a lot of workarounds, community tools,…
T-SQL

Using Binary Hash as an Alternate Record Key

It’s a common scenario in data processing solutions that we must import some data for further processing and we can’t dictate source format or data types used. This can complicate things, especially in the case when source data key values are in string format with untrivial length. Character data types…

Read more