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.

«February 2016»
MonTueWedThuFriSatSun
25262728293031
12345

Power BI Publish to Web for Anonymous Access is Here

Earlier this week on Wednesday the Microsoft Power BI made an incredibly exciting announcement and released Power BI “publish to web” as a preview feature. This is HUUUUGE news! This was probably the top requested feature and its finally here thanks to the hard work and dedication of the Microsoft Power BI team!

Read Getting Started with R Visuals in Power BI

Power BI “publish to web” allows you to easily expose a Power BI report to the world through an iframe that can be embedded wherever you like.

To publish your Power BI report to the web, log into your Power BI site.

Find the report that you want to share and click File in the top left.
Power BI publish to web

You’ll see a message pop up box similar to below. Click the yellow button to create the embed code.
Power BI publish to web preview

This is where you’ll see a very important warning!
WARNING: Reports that you expose through the “publish to web” feature will be visible to everyone on the internet! This means NO AUTHENTICATION is required to view the report that is embedded in your application.
warning 2

Once you do that, you’ll receive an embed code that you can then use to expose your Power BI report within your blog as seen below!

https://msit.powerbi.com/view?r=eyJrIjoiYTNjNzcwNjctNTczMy00ZDMxLWFlMGUtMDViODA1NGZiNmI0IiwidCI6IjcyZjk4OGJmLTg2ZjEtNDFhZi05MWFiLTJkN2NkMDExZGI0NyIsImMiOjV9

As you can see the report maintains all the interactivity features of Power BI. And as your Power BI report updates and changes, those changes will be reflected in your embedded Power BI reports!

Pretty awesome!

Additional Resources

Read the Power BI “publish to web” announcement here.

Read the Power BI “publish to web” documentation here.

Feedback

Let me know what you think of this feature or if you have any questions. Leave a comment down below.


Read more
67
8

MDX NON EMPTY KEYWORD VS NONEMPTY FUNCTION

Non Empty vs NonEmpty

Hey everyone, in this blog I want to address a very common MDX Question. What is the difference between the NON EMPTY keyword and NONEMPTY function? To take it a step further which one should you use?

Non Empty keyword VS NONEMPTY Function.

The big difference between the NON EMPTY keyword and the NONEMPTY function is when the evaluation occurs in the MDX. The NON EMPTY keyword is the last thing that is evaluated, in other words after all axes have been evaluated then the NON EMPTY keyword is executed to remove any empty space from the final result set. The NONEMPTY function is evaluated when the specific axis is evaluated.

Should I use NON EMPTY keyword or NONEMPTY function?

Ok Mitchell, so you told me when each of these are evaluated but really you haven’t told me anything up until this point. Can you tell me which one I should use already? Well, unfortunately, it depends. Let’s walk through an example of each using the BOTTOMCOUNT function.

BOTTOMCOUNT FUNCTION with NON EMPTY Keyword

In this example I’m returning the bottom ten selling products for internet sales. Notice that I have returned all products that have no internet sales, this is not necessarily a bad thing, maybe you want to return products that don’t have sales.

image

However if you don’t want to return these products then we can try using the NON EMPTY keyword. In the below example you can see the results when I add NON EMPTY to the ROWS axis.

image

WHOOOAAA, what happened?? A lot of people would have expected the results here to show the bottom ten products that DID have sales. However, that is not the case, remember that I said the NON EMPTY keyword is evaluated LAST after all axes have been evaluated. This means that first the bottom ten selling products which have $0 in sales are first returned and then the NON EMPTY keyword removes all that empty space from the final result.

BOTTOMCOUNT function with NONEMPTY function.

So let’s try this again, if you want to return the bottom ten products that had sales then we must first remove the empty space before using the BottomCount function. Take a look at the code below:

image

In this code we first remove the empty space before using the BOTTOMCOUNT function. The result is we return the bottom ten products that had internet sales. Once again neither one is right or wrong here it just depends on what you want in your final result.

NON EMPTY Keyword vs. NONEMPTY Function – Performance

There is a very common misconception that the NONEM

Read more
91011121314
15161718192021
22232425262728
29123456

SQL Internals Reading Data Records Part 2: Null Bitmap Offset

  • 27 June 2012
  • Author: BradleyBall
  • Number of views: 5394
  • 0 Comments
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.


NULL BITMAP OFFSET

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;
Go

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

CREATE DATABASE demoInternals
GO

USE demoInternals
GO

/*
Let's create a Clustered Index
*/

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

/*
Insert one data record
*/
INSERT INTO dbo.myTable1
DEFAULT VALUES

GO


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)
GO


 

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 TRACEON(3604)
GO

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

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), http://easycalculation.com/hex-converter.php.

 

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)
       )
GO

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

Now let’s find our data page.

DBCC IND(demoInternals, 'fixedRecord', 1)
GO

 

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 TRACEON(3604)
GO

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

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.

 

 

WRAP UP

 

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.

 

Thanks,

 

Brad

Print
Categories: Analysis Services
Tags:
Rate this article:
No rating

BradleyBallBradleyBall

Other posts by BradleyBall

Please login or register to post comments.