Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

«November 2015»

DirectQuery in Power BI Desktop

In the latest Power BI Desktop a new Preview features was released that now allows you to connect using DirectQuery to either SQL Server or Azure SQL Databases.  DirectQuery is a really neat feature that allows you to point to the live version of the data source rather than importing the data into a data model in Power BI Desktop. 

Normally when you want to get an updated dataset in the Power BI Desktop you would have to manually click the refresh button (this can be automated in the Power BI Service), which would initiate a full reimport of your data.  This refresh could take a variable amount of time depending on how much data your have.  For instance, if you’re refreshing a very large table you may be waiting quite a while to see the newly added data. 

With DirectQuery data imports are not required because you’re always looking at a live version of the data.  Let me show you how it works!

Turning on the DirectQuery Preview

Now, because DirectQuery is still in Preview you must first activate the feature by navigating to File->Options and settings->Options->Preview Features then check DirectQuery for SQL Server and Azure SQL Database


Once you click OK you may be prompted to restart the Power BI Desktop to utilize the feature.

Using DirectQuery in Power BI Desktop

Next make a connection either to an On-Premises SQL Server or Azure SQL database.

Go to the Home ribbon and select Get Data then SQL Server.


Provide your Server and Database names then click OK. ***Do not use a SQL statement.  It is not currently supported with DirectQuery***


From the Navigator pane choose the table(s) you would like to use.  I’m just going to pick the DimProduct table for this example and then click Load.  You could select Edit and that would launch the Query Editor where you could manipulate the extract.  This would allow you to add any business rules needed to the data before visualizing it.


Next you will be prompted to select what you want to connect to the data. Again, Import means the data

Read more

The Big Data Blog Series

Over the last few years I’ve been speaking a lot on the subject of Big Data. I started by giving an intermediate session called “Show Me Whatcha’ Workin’ With”. This session was designed for people who had attended a one hour introductory session that showed you how to load data, to look at possible applications … Continue reading The Big Data Blog Series
Read more

SQL Internals Reading Data Records Part 2: Null Bitmap Offset

  • 27 June 2012
  • Author: BradleyBall
  • Number of views: 5273
Hello Dear Reader as I’m writing this the remnants of Tropical Storm Debby have finally blown off the coast of Florida and the sun is starting to shine.  The kids are so happy to see the blue skies that they are only out done by the dogs, whom I discovered are not fans of ran whatsoever. This is the first tropical storm we’ve had since we moved to FL, and the earliest one on record for hurricane season.  We’ll have to see if this is an omen of things to come or if it was just a happy accident that gave the state some much needed rain.

But enough of the rain and the Sunshine you stopped by for some talk about SQL Internals and that is just what we are going to get to.


When last we met we were discussing how to read the Tag Bytes of a Data Record.  As you will recall I posted the following picture of a Data Record from Paul Randal (@PaulRandal | Blog) and the MCM Video series on Data Structures.  I’ve updated it to point to our next topic of discussion the Null Bitmap Offset. 


I’ve heard this also referred to as the Fixed Data Record Length portion of the record, and the two confused me at first until I realized they were one in the same.  The purpose of these bytes are to tell us how much fixed length data is stored in the fixed length columns.  To stay consistent we will use the same example as we did in Part 1 .

So if you are missing that code here it is, first we’ll create a database and insert a record.

USE master;

IF EXISTS(SELECT name FROM sys.databases WHERE Name=N'demoInternals')
              DROP Database demoInternals


USE demoInternals

Let's create a Clustered Index

IF EXISTS(SELECT NAME FROM sys.tables WHERE name=N'myTable1')
       DROP TABLE dbo.myTable1
       myID INT IDENTITY(1,1)
       ,productName char(500) DEFAULT 'some product'
       ,productDescription CHAR(1000) DEFAULT 'Product Description'
) ;   

Insert one data record
INSERT INTO dbo.myTable1


You can see that we have nothing but fixed length records in this example.  We’ll use DBCC IND and DBCC PAGE to get our values again.

DBCC IND(demoInternals, 'myTable1', 1)


Remember PageType 10 is an allocation unit and we want to look at our data page so we want PageType 1.  We’ll turn on Trace Flag 3604 so we can see the DBCC PAGE output on our SSMS screen.


DBCC PAGE('demoInternals', 1, 276, 3)

I’m stripping the data out to only what is relevant for today.  The output of DBCC PAGE will have much more information on there as well.

0000000000000000:   1000e40501000000 736f6d65 2070726f 64756374  ..ä.....some product


Today we are looking just at the block in red e405, these are hexadecimal values that are group together in a two byte pair.  To read them we need to reverse them, instead of e405 we are actually looking at 0x05e4.  We’ll use our conversion web page from yesterday to see what this value is (for the tool just type in 05e4),


The value we get back is 1508.  If you look at our schema and add that up it seems a little off at first.  An integer is 4 bytes, plus 500 for our char, plus 1000 for our second char values, 4+500+1000=1504.  So where did the other 4 bytes come from? 


Our Tag Bytes are 2 bytes, and our Null Bitmap Offset are also 2 bytes.  Add those four in and you get 1508.  So let’s do one more example just to test this out.  We can’t use yesterday’s second example because it was the same table, we can’t use the third because all we did was add a variable length column to the table which wouldn’t show up in the fixed length portion of a record.  Looking at that example we can see that.

0000000000000000:   3000e405 01000000 736f6d65 2070726f 64756374  0.ä.....some product


So we will need to make a new table with a different value for our fixed length fields. 

Create Table fixedRecord
       ( myID int
       , mychar char(5)

INSERT INTO dbo.fixedRecord(myid, mychar)
VALUES(1, 'X')

Now let’s find our data page.

DBCC IND(demoInternals, 'fixedRecord', 1)


Remember to set our Trace Flag for 3604 on, if you open a new SSMS query for the script, and look at our data page.


DBCC PAGE('demoInternals', 1, 282, 3)

This table that we have created has a 4 byte integer field and a 5 byte char field for a total of 9 bytes.  Add in our 2 bytes for our Tag Bytes and our 2 bytes for our Null bitmap offset and we should be sitting at 13 Bytes. (Once again only posting the relative portion of the DBCC PAGE output.)

0000000000000000:   10000d00 01000000 58202020 20020000           ........X    ...


Our results are 0d00, remember to reverse these so we get the hexadecimal output of 0x000d, this actually translates down just to d which is equal to 13.





I’ve enjoyed studying and learning on this topic, and judging by the number of hits on part 1 you guys did toLaughing.  Hope to see you next time Dear Reader and as always Thanks for stopping by.





Categories: Analysis Services
Rate this article:
No rating


Other posts by BradleyBall

Please login or register to post comments.