Power BI - Creating Measures for Live Connection is now possible

Good news! With Power BI May 2017 update, we can create measures for Live Connection made for Analysis Services Tabular Model. Earlier, we had no way of creating measures when connect to Analysis Services databases, hence had to use measures available only with the model. This limited the usage of Power BI with Analysis Services as users could not create measure as they want.

Now it has been addressed up to some extent. We can create measures using DAX against both Live Connection - Analysis Services Tabular Model and Power BI Service Datasets.

Here is an example of it.

I have connected with AW Internet Sales Tabular Model 2014 database using Live Connection. As you see, the column chart is created with Sales Amount against Month Number and it is filtered for Year 2013. In addition to that, I have added a new measure called SalesAmountYTD using the following DAX;

SalesAmountYTD = TOTALYTD(sum('Internet Sales'[Sales Amount]), 'Date'[Date])

Note that the measure is not part of the Model and it is considered as a Report Measure. It can be created either clicking New Measure Button or clicking the ellipsis button in any attribute.

Power BI - How to changed the account credentials set with Power BI Desktop

If you are maintaining multiple Power BI accounts, you need to changed the credentials as per the account needs to be used when publishing the file created. This was asked by one participants who came for my Power BI workshop. He has been using his office account for publishing his files and now he needs to make a new file and publish to his new Power BI account.

Is there a place to set the Power BI account?

There is no specific setting for this. The only way of handling this is via Signing In and Signing Out menu which is in File menu.

If you need to change the account, sign out from the account set and sign in again with required credentials.

How to check whether my Power BI is Free or Pro?

Once registered or if someone has registered a Power BI account for you, how do you know whether it is Free or Pro account? If it is Free, how can I get it upgraded to Pro?

Here is the way. Once you logged into Power BI Online Service, Click on setting. The click on the first menu item that is Manage Personal Storage. The Manage Storage page says whether it is Free or Pro and if it is Free, you can upgrade to Pro by clicking Try Pro for Free button.

SQL Server - All Pooled Connections in use? How many Pooled Connections are there?

You may continuously experience the following error with your application;

Unhandled Exception: System.InvalidOperationException: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.

You may rarely see sudden crash in your application. You may see that SQL Server suddenly hangs forever until you restart the service. There could be many reasons for this and one of the reasons could be related to the Connection Pool.

What is Connection Pooling? How does it work and what limitations are there with it? How can I check whether there are pooled connections? These are the things you should know if you experience an issue related to it.

Let's understand Connection Pooling

Establishing a connection goes through several steps that need resources and has a cost. If an application continuously uses the SQL Server Database by opening and closing the connection, it is better to keep the connection without discarding and reuse when required without re-creating for each an every call. This is purely for improving the performance. When the application first time tries to make the connection, it creates the Connection Pool. Then the connection is added to the pool and once the connection is closed, it will be kept for a certain time period for reusing for new requests come for the same Connection Pool. This connection is called as Pooled Connection. If the Pooled Connection is being used and a new request is received for the same Connection Pool, then a new Pooled Connection is created in the same pool and used. Connection Pool is based on several keys in connection string and they are used for identifying the right Connection Pool to get a connection. If the request comes from a different connection string, it creates a new Connection Pool and maintains Pooled Connection in it.

By default, maximum number of Pooled Connections can be maintained in a Connection Pool is 100. If there are 100 Pooled Connections in the pool and all are being used, and another request comes for the same pool, you will experience one of above mentioned issue.

How can I see the number of Pooled Connections in Connection Pool?

There are multiple ways of seeing this. What I use is a simple TSQL as below;

xxx

This gives you connections and number of Pooled Connections (NumberOfConnections). There is no specific threshold for determining whether the number shows is good or bad. However, if you see ALWAYS see a number above 50, that might indicate an issue.

Why I see a higher number of Pooled Connections?

There can be two main reasons for seeing a larger number. One is, obviously, if you have many number of concurrent users working with your database using the same connection string. Second is, your application does not close the connection used and opens again. It will add a new Pooled Connection to the pool. Remember, opened connections cannot be used until they are closed (There can be exceptions, see the below example).

See the below C#.Net code;

int x = 1;
SqlConnection con;
SqlCommand cmd;
string sql;

while (x < 1000)
{
        con = new SqlConnection("Server=.;Database=AdventureWorks2014;Integrated Security=SSPI;");
    con.Open();
    sql = "BEGIN TRAN; SELECT * FROM Production.Product WHERE ProductID = " + x.ToString();
    cmd = new SqlCommand(sql, con);
    cmd.ExecuteNonQuery();
    Console.WriteLine(x.ToString());
    x = x + 1;
}

As you see, I executes the database code many times. Once executed, it will iterate the loop making multiple connections in the same pool and at a certain stage, an exception is thrown;

And if I check my TSQL code at this time, I will see that number of Pooled Connection as 100 for the application session.

Once the pool is filled with 100 connections, it cannot add another connection to the pool. Did you notice that code has created through 238 connections? This indicates that some connections have been reused even though they are not closed. However, this is something you need to always check if you get this error. If you see that code does not close the connection, it is better to check all codes and add closing code, making sure that the connection can be reused and no connection pool related errors.

Can I create connection without adding them to Connection Pool?

Yes, it is possible though it is not recommended. If you add Pooling=False to the connection string, it will not add to connections to the pool but you may experience some performance issue.

Power BI - Can we use DAX functions with DirectQuery mode?

Have you tried to use DAX functions for creating measures in Power BI when the data source connection is Direct Query? If you have tired, you have already experienced this issue. Is it possible to use DAX functions with Direct Query mode?

Yes, it is possible up to some extent. Let me take you through an example. Let's connect with AdventureWorksDW2014 using Direct Query mode and access DimDate and FactInternetSales tables. This brings three relationships between two tables, delete them all and create a new relationship between DimDate.FullDateAlternateKey and FactInternetSales.OrderDate column.

Now, if we try to create a measure for calculating YTD on SalesAmount using TOTALYTD, you will get an error saying;

Function 'DATESYTD' is not supported in DirectQuery mode.

What is the reason for this? The reason for this is, by default, DAX functions cannot be set for calculating measures when the mode is DirectQuery. However, it is possible enable some functions for this mode by enabling Allow unrestricted measures in DirectQuery mode setting. It can be opened with File Menu -> Options and Settings -> Options menu.

Once it is checked, you need to close the Power BI Desktop and open it. After that, you will see that you can use the function. Remember, even though you have enabled it, it does not allow you to use all DAX functions.

Embedding a Power BI Report to the Blog (External Web Applications)

This is how you see your Power BI reports when it is embedded to an external application using code generated for embedding.

As you see, it is fully interactive, highlighting, drilling down, etc. works as it was opened with Power BI.

For generating the code for embedding; open the report with Power BI online service. Click the Publish to the Web in File menu. You will see two outputs; One for emailing the report as an embedded content and other is for publishing to the web.

If you need to see all generated codes for your reporting in Power BI Online Service, click Settings and select Manage embedded codes.

Power BI - DAX TOTALYTD does not calculate the values properly

There are multiple ways of calculating YTD values using DAX function and one of the easiest ways of calculating it is using TOTALYTD function. This needs some specific settings and once everything is set, it works as we expected. However, I noticed an issue, it may not be an issue but it is something to remember when using this function.

Let me explain what I did first. I connected with AdventureWorksDW2014 database and imported two tables: DimDate and FactInternetSales. Since the DimDate dimension is a Role-Playing dimension, it shows three relationships between these two tables. I removed two and kept only the link between DateKey and OrderDateKey.

This is how the relationship has been set.

Once it is done, I created a new measure called SalesAmountYTD as below;

SalesAmountYTD = TOTALYTD(SUM(FactInternetSales[SalesAmount]), DimDate[FullDateAlternateKey])

In order to see the YTD values for months of 2013, I created a Column Chart and set EnglishMonthName of DimDate (sorted by MonthNumber) as the X Axis. Then both SalesAmount and SalesAmountYTD are added as values and used CalenderYear for filtering the entire page for 2013. This is what I see when it is viewed;

As you see, YTD values are not getting calculated accurately. I did many searches and applied many solutions and nothing worked. But finally I figured it out, it is all about the columns I have used for linking both tables. The original relationship between these two tables is based on DateKey and OrderDateKey. When I change the columns for defining the relationship as FullDateAlternateKey and OrderDate as below, it started working.

Here is the output.

I am not sure the exact issue or theory related, have already asked some experts, once received the feedback, will share it. This must be a small thing that I do not see, if you know it, please share it :).

Power BI - Understanding Cross Filter Direction with a simple example

When loading data to Power BI, it automatically detects Relationships between loaded tables and set the Cardinality and Cross Filter Direction. The Cardinality refers the relationship between two tables such as Many to One or One to Many BI or  One to One and Cross Filter Direction decides how tables are treated in visualizations in reports.

There are two types of Cross Filter Direction: Both and Single. When the relationship is Both, two tables linked are treated as a single table for aggregation data in visualization. This means, both tables can be used for aggregation, one table is for aggregation and the other is for filtering. When it is set to Single, the lookup table or the table used for filtering cannot be used for aggregation.

Let's take an example and see how it works. Here are the steps for testing this.

1. Open Power BI and connect with SQL Server AdventureWorksDW2014 database.

2. Import five tables: DimCurrency, DimProduct, DimProductCategory, DimProductSubCategory and FactInternetSales. Power BI automatically detects the relationships and tables will be shown as below;

3. Double click on any relationship and see the set Cross Filter Direction. Let's see the relationship between DimCurrency and FactInternetSales.

As you see, Both has been set as the Cross Filter Direction. This is the default for many relationships, however, for certain scenario, it will set the it as Single. For example, when you load these tables, if you load DimDate as well, you see that all relationships are set to Single. The reason for that may be, DimTable is Role Playing Dimension, there are three links between this table and the fact.

4. For testing purposes, let's make all Cross Filter Direction as Single.

5. Now let's go to Report section and add a Table widget. Let's add SalesOrderNumber from FactInternetSales and CurrencyName from DimCurrency table.

6. Let's add another table that shows EnglishProductCategoryName from DimProductCategory and CurrencyName from DimCurrency table.

7. As you see, first tables shows currency used for each order and second shows currencies used with each category. Let's try to get the number of currencies used instead of the name, means going to perform an aggregation over DimCurrency.

8. Let's create a measure called NofCurreciesUsed. Go to Data section and add a measure like below.

      NofCurreciesUsed = COUNT(DimCurrency[CurrencyName])

9. Let's go back to the Report and change both tables. Remove CurrentName from both tables and add newly created Measure. This is what you should see once it is added.

10. As you see, Aggregation over the DimCurrency is not working as expected. The reason for this is set Cross Filter Direction. The DimCurrency table cannot be aggregated using FactInternetSales (link is set via it) because Cross Filtering is not enabled. Let's change the Cross Filter Direction between DimCurrency and FactInternetSales back to Both.

11. Now you should see the the correct values in both tables.

This is it works. For more information read: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-create-and-manage-relationships/

ODBC Scalar Functions in SQL Server

Do you know that you can call set of ODBC Scalar functions inside SQL Server? Yes, it is possible and they can be used with ad-hoc statements, stored procedures and functions.

There are different types of functions and they are categorized as String, Numeric, Date time, System Functions, and Data type conversion. You can see the list at https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/appendix-e-scalar-functions.

When you call an ODBC function, you need to make sure it is encased with curly brackets and started with fn. Arguments that are required for the function can be mentioned with parenthesis.

Here are some examples;

SELECT {fn ASCII ('A')} As Column1;
SELECT {fn Now()} As Column1;
SELECT 'Hello' + {fn Space(10)} + 'World' As Column1;
SELECT {fn USER()} As Column1;

SQL Server Splits the page when I change an Integer Column Value

I have written few posts on Index Fragmentation and how they can be checked. External Fragmentation occurs as a result of Page-Split and it happens when there is no space in the page to accommodate the change (new record or an update). When no space, it moves approximately 50% of records to a new page, accommodating the change to relevant page.

Read Understanding Index Fragmentation – SS SLUG Aug 2013 – Demo I post for understanding fragmentation.

Read SQL Server - Checking Index Fragmentation - Understanding LIMITED, SAMPLED and DETAILED for understanding various modes that can be used for checking fragmentation.

For understanding page-split, lets take an example. Have a look on below table.

-- creating test table
CREATE TABLE dbo.TestTable
(
    Id int PRIMARY KEY
    , Value char(4000) NOT NULL
)
GO
 
-- inserting 2 records
INSERT INTO dbo.TestTable
    VALUES 
    (2, replicate('a', 4000)), (5, replicate('b', 4000));

It has two records. Since the approximate size of a record is 4004 bytes (4 for int column and 4000 for char column), SQL Server can use one page for holding both records. The size of a data page is 8KB. The below query proves that both records are in the same page.

SELECT Id, Value, ph.*
FROM dbo.TestTable
CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%) ph;

If I make a change to Value column, SQL Server does not need to split the page as the current value uses 4000 bytes. Example, if I change the first record value as REPLICATE('x', 4000), it can simply replace the old value, hence no page split. See below code and the result.

UPDATE dbo.TestTable
    SET Value = replicate('x', 4000)
WHERE id = 2
 
SELECT Id, Value, ph.*
FROM dbo.TestTable
CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%) ph;

Just like that, if we make a change to the integer value, we should not see any change on pages used. Let's make a small change and see.

UPDATE dbo.TestTable
    SET Id = 3
WHERE id = 2;
 
SELECT Id, Value, ph.*
FROM dbo.TestTable
CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%) ph;

Did you notice the change? Why the second record has been moved to a new page? Mean Page-Split has been occurred with this changed?

Is it because of the data type? It is not. Note that this is not just an integer column. It is the PRIMARY KEY; The clustered key. What happened was, SQL Server updated the Value column as an in-place update. It does not require additional space. However SQL Server does not perform in-place update for key columns. It performs delete-plus-insert operation for key column updates. It requires space for the INSERT. That is the reason for the page split. Understand that this behavior is not only for integer, it is for all key columns.

This is why we discourage you to select a column as the key column if it can be changed by a business operation. It is always recommended to use a non-volatile column as the key column that do not expect changes on values.

SQL Server - Checking Index Fragmentation - Understanding LIMITED, SAMPLED and DETAILED

Indexes get fragmented based on the operation we perform against records and modification we do against index keys. We use one of the Dynamic Management Function which is called sys.dm_db_index_physical_stats for checking both Internal Fragmentation and External Fragmentation. Today, I had to check one my clients data table for fragmentation which is a very large table. Since this is a quick look, I decided the use SAMPLED mode for checking the fragmentation instead of my usual mode DETAILED. Once the check is done, we had a short-conversation on the mode selected; difference between them and why should use them. This conversation resulted this post.

If you need to know Internal and External Fragmentation with a sample code, please see my post: Understanding Index Fragmentation – SS SLUG Aug 2013 – Demo I.

Here are the differences between three modes;

Mode	Details
LIMITED	Fastest way of scanning the index for fragmentation. For B-Tree indexes, only the Parent Level Pages are scanned. For Heaps, associated PFS and IAM pages are checked and data pages are scanned. This cannot be used for checking Internal Fragmentation. This still shows External Fragmentation because it uses Pointers to the Leaf Level in Parent Pages for calculating the External Fragmentation.
SAMPLED	Slower than LIMITED as it scans leaf pages too. It uses 1% of all pages for scanning, hence the fragmentation values are approximate. If the number of pages are lesser than 10,000, then DETAILED mode is used instead of SAMPLED mode.
DETAILED	Slower than LIMITED as it scans leaf pages too. It uses 1% of all pages for scanning, hence the fragmentation values are approximate. If the number of pages are lesser than 10,000, then DETAILED mode is used instead of SAMPLED mode.

The reason for me to use SAMPLED mode for checking is, the number of records it has. Since it takes long time for scanning all pages, I decided to use SAMPLED mode because it could help to me determine whether the index is fragmented or not.

This shows how the fragmentation is shown with all three modes;

How to find the related file and the page numbers of records in SQL Server data table

Sometime, for some administration works, we need to know which file has been used for holding our records and the related page numbers. How can we easily find these information?

Generally we use DBCC IND and DBCC PAGE but there are two more great functions that can be used for finding the same.

The first function is %%PHYSLOC%%. This returns the RID (Record Identifier) as a hexadecimal value. The RID consists file number, page number and, record number. The second function is a table-valued function which is fn_PhysLocCracker. It accepts the RID returning from %%PHYSLOC%% and returns three-columned table for file number, page number and, record number. Combing these two, we can get the information as we want. Here is an example;

SELECT p.BusinessEntityID, p.Title
    , p.FirstName, p.LastName
    , ph.*
FROM [Person].[Person] p
CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%) ph

Adding Power BI Reports to Reporting Services

The integration between Reporting Services and Power BI was started with Reporting Services 2016, allowing us to pin SSRS Reports to Power BI Dashboards. Not only that, we can have Power BI files hosted in Reporting Services but Reporting Services does not support opening them inside the portal.

This integration has been extended and it will be available with the next version of SQL Server. With this, we can create reports using Power BI and add them directly to Reporting Services. Not only that, Reporting Services Portal supports opening Power BI Reports inside the portal. Can we see it now? Yes, Technical Preview is available for testing this.

In order to test this, you need to download two files; Power BI Desktop For SSRS (PBIDesktopRS_x64) and SQLServerReportingServices.

Power BI Desktop for SSRS is a separate Power BI instance and it can be installed side by side without removing existing Power BI installation. SQLServerReportingServices is the latest installation for SQL Server which is a standalone installation. It installs Reporting Services and allows us to configure just like the way we do with Reporting Services 2016.

You can download both files from: https://www.microsoft.com/en-us/download/details.aspx?id=54610

Installing Reporting Services

Let's start it with Reporting Services installation. Just like the way you install any other software, double-click on SQLServerReportingServices.exe and start the installation. You will get the usual agreement window, accept it and continue.

And you should see the final output within less than one minute;

This works without any issue even if you have SQL Server 2016 installation in your machine. The configuration of newly installed Reporting Services can be started by either clicking the button in the last window or searching and opening the right Configuration Manager. If you search for it, you will see two with the exact name (if you have already installed 2016), need to pick the right one.

When you open the Configuration Manager for the one you installed, you should get a similar screen, note the instance name: RSServer.

If you remember the 2016 installation, you know that you get an option for configuring Reporting Services during the installation: Install and Configure or Install Only. Since we did not get anything like that with this installation, we need to configure everything manually. First thing is creating the database.

Go to Database page and click on Change Database.

It starts the wizard, make sure that you select Create a new report server database. This requires a SQL Server database engine. If you have not installed SQL Server, then you need to install it before configuring the report server database. If you have already installed Reporting Services 2016, then you have a Report Server database that is for 2016 instance. If you have, DO NOT OVERWRITE THE EXISTING ReportServer DATABASE. Make sure you give a different name for it. As you see below, I have named my Report Server database as ReportServer_2017.

You can accept the default values for other pages in the wizard unless you need to change it.

Once it is configured, you need to configure Web Portal URL and Web Service URL. Again, make sure it does not conflict with your existing Reporting Services URLs. As you see, I have set the Virtual Directory for Web Portal as Reports_2017.

You need to do the same for Web Service URL as well. Once done, you should be able to browse the Reporting Server with the configured URL.

Installing Power BI

No difference between this installation and standard Power BI Desktop installation.

Once the installation is completed, open it (Note that if you had previous Power BI Desktop installation, now you have two Power BI instances). You should see SQL Server Reporting Services tag when opening Power BI. That confirms that you open the right instance.

Creating Reports and Publishing to Reporting Services

Note that this can be used for creating standard Power BI reports as well. However, if you create a report for Reporting Services, you need to make sure the following;

• Source is Analysis Services
• Connection type is Live Connection

At the moment, it supports only Analysis Services but we will surely see all other types with future releases.

Connect with your Analysis Services database and create a report as you need. This is what I created.

In order to publish this to Reporting Services, you can either save this directly to Reporting Services or save as a local file and upload it using Reporting Services Upload File menu. Let's save it directly to Reporting Services.

You should see new saving option as SQL Server Reporting Services.

Select it and enter the URL configured for the portal.

Name it and click OK.

You should see the success message if it can connect with your Reporting Services. Once saved, go to the portal and see it. You should see the added Power BI report. With the previous version of Reporting Services, if you click on added Power BI file, it downloads the file. But with this version, if you click on it, it opens the report and all functionalities works just like the way it works with Power BI online service.

Good thing is, it allows you to edit the report using Power BI;

Once you modified, you do not need to upload the file again because Power BI can directly save it to Reporting Services. The changes will be immediately appear in Reporting Services.

See, how easy it is. Try and see, you will see a lot more options with future releases.