Writing for BIDN

Looking to give back to the community or learn through teaching others? Anyone can post blogs by clicking Add Blog Post after contacting us for permission.

«August 2015»
MonTueWedThuFriSatSun
272829

Power BI and Big Data

If you’re worked in the wide and diverse field of information technology for almost any amount of time, it probably hasn’t taken you long to discover that the one thing constant about IT is that the technologies and strategies involved change faster than you can learn them. And if you work in business intelligence like I do, you don’t have to look very far at all to see change. The Microsoft Power BI team rolls out a software update every month! If I want to stay learned up on the technology, I have to really be on top of things.

About ten years ago when Hadoop was first being developed at Yahoo, I don’t think anyone could have anticipated the size of the ripples (more likes cannonball sized splashes) being able to access Big Data could and would have on the IT industry. Hadoop (and other advances in hardware and software technologies) gave us something we never had before: The ability to access and report on data in real time on a scale never previously imagined gives an organization to identify and understand trends and patterns in the data and gain previously unknown insights. The organizations that are able to leverage big data will be the organizations that leave their competition in the dust.

Set Up and Configure the Hortonworks Sandbox in Azure

Not only does Power BI Desktop give us the ability to connect to Hadoop Distributed File System (HDFS) for reporting we can also mash it up with other more traditional and structured data sources with minimal effort required. But that’s not what this blog post is all about. This post is about setting up a virtual machine in Azure running Hadoop and connecting to our Hortonworks Sandbox with Power BI Desktop :).

The first thing you do if you don’t have access to a Hadoop cluster is to set up the Hortonworks Sandbox on Azure. The good news is its free (for the duration of the trial) and its super easy. Just follow the instructions at this link to set up the Hortonworks Sandbox.

Hadoop in Azure

Once that’s set up, you’ll need to add mapping for the IP address and host name to your hosts file. Devin Knight has a blog on this that you’ll find helpful.

Connecting to Hadoop with Power BI Desktop

Once your Hortonworks Sandbox is set up, you’re ready to set up your connection to Hadoop with Power BI Query. Start up the Power BI Desktop and click Get Data. Scroll down and select Hadoop File (HDFS) and click Connect.

Get Data with Power BI

From there you can follow the rest of the wizard to load the data into the semantic model.

Load Data with Power BI

Once the data is loaded, you’ll need to modify the query to navigate to the data you wish to use in your model.

In Power BI Desktop, go to the Home ribbon and click Edit Queries.

Read more

30

Three Best Practices for Power BI

Since the release of Power BI Desktop this past week, I’ve been really spending my extra time digging into the application focusing on learning and experimenting as much as I can. When my wife has been watching Law and Order: SVU reruns at night after the rug rats are in bed, I’ve been right there next to her designing Power BI dashboards like the total data nerd that I am. When my kids have been taking their naps during the weekend, I’ve been writing calculations in the model for my test dashboards. Or when I’ve been riding in the car back and forth to work I’ve been thinking of new things to do with Power BI Desktop.

Since I’ve been spending a decent amount of time with Power BI Desktop, I thought I’d take a moment to share three things to know and remember when designing your Power BI models and dashboards that I think will help you make the most of this tool and be effective at providing the data your business needs to succeed.

1. Optimize your Power BI Semantic Model

It probably hasn’t taken you long to figure this one out if you’ve built Power Pivot/Tabular models or at least it won’t when you do start developing Power BI dashboards. The visualizations in Power BI and Power View are heavily meta-data driven which means that column names, table or query names, formatting and more are surfaced to the user in the dashboard. So if you using a really whacky naming convention in your data warehouse for your tables like “dim_Product_scd2_v2” and the column names aren’t much better, these naming conventions are going to be shown to the users in the report visualizations and field list.

For example, take a look at the following report.

Power BI Dashboard without formatting

Notice anything wonky about it? Check the field names, report titles and number formatting. Not very pretty, is it? Now take a look at this report.

Power BI Dashboard with formatting

See the difference a little cleaned up metadata makes? All I did was spend a few minutes giving the fields user-friendly name and formatting the data types. This obviously makes a huge difference in the way the dashboard appears to the users. By the way, I should get into the movie production business. ;)

My point is that the names of columns, formatting, data types, data categories and relationships are all super important to creating clean, meaningful and user friendly dashboards. The importance of a well-defined semantic model cannot be understated in my opinion. A good rule of thumb is to spend 80% to 90% of your time on the data model (besides, designing the reports is the easy part).

I’d also like the mention the importance of the relationships between the objects in the semantic model. Chance are you will have a small group of power users that will want to design their own dashboards to meet their job’s requirements and that’s one of the beauties of Power BI. But when users began developing reports, they may query your model in unexpected ways that will generate unexpected behaviors and results. I only want to mention this because the relationships between the objects in the model will impact the results your users will see in their reports. Double check your relationships and ensure that they are correct, especially after you add new objects to the model since the

Read more
31

Power BI Fantasy Football Player Stats Dashboards for Download

Every year at Pragmatic Works some coworkers, including consultants, marketing staff, support team members, software development staff and project management, partake in a company fantasy football league. And with the recent release of the new Power BI Desktop, I thought what better way is there to prepare to completely annihilate my coworkers and friends in an imaginary nonsensical game than by creating some nifty Power BI dashboards based on last years player stats as recorded by Yahoo! Sports. So I thought I’d walk you through some of the steps I followed to leverage the Yahoo! Sports NFL player stats page as a data source and some of the query transformations I applied to prepare the data for reporting.

Power BI dashboard with Power BI Desktop

Click here to download my Fantasy Football Dashboards Power BI .pbix file.

If you’re completed new to Power BI Desktop I highly suggest you watch my video walkthrough of Power BI Desktop or read my blog post which walks you through each step of creating your first Power BI dashboards with Power BI Desktop. Last Friday, I also blogged about my three best practices for designing a killer Power BI solution, so take a look at that.

To create these dashboards, I simply navigated to the Yahoo! Sports NFL stats page and found the page for each position I’m interested in for this fantasy football season. I copied the URL to my clipboard. In Power BI Desktop, click Get Data and then use the Web data source option. Then all you have to do is copy and paste the URL into the text box and click OK.

Get data from web with Power BI Desktop

Then select the HTML table that contains your data and click Edit. We need to edit our query because there are some issues with the data. By clicking Edit, we can apply transformations to our query which will allow us to do things like rename columns, remove unwanted columns, modify data types, create custom columns and much more.

Get data from web with Power BI Desktop

One thing you’ll notice in the above screen grab is that the column names are in the first row, so we need to fix that.

On the Home ribbon of the Query Editor, just click the Use First Row As Headers button. Pre

Read more
12

Power BI Tip: Use the Treemap Chart as a Colorful Slicer

Power BI Desktop has been out for GA for over a week now and some of the pro’s out there have come up with some pretty cool tricks. For instance:

But if you’re looking for a way to spice up you report filtering with a little color, try using the Treemap chart as a Slicer for those fields that only contain a few unique values. At this point with Power BI, you don’t have any customization options for the Slicer visualization (although I’m sure that is coming down the pipe in a future release). This option won’t work terribly well if the field you would like to use as a slicer has more than a dozen or so unique members, but you can experiment with it and see what you can come up with. Here’s my Treemap Slicer in action.

tree map slice in action

To multi-select tiles in the Treemap slicer, just hold Cntrl as you click. To reselect

This little trick relies on the natural cross filtering between data regions in the Power BI dashboards. First I created a measure that calculates the distinct count of the field that I wish to use as my slicer. In this case the field is Genre.

Power BI Distinct Count DAX calculation

Then I added a Treemap chart to the report using the field Genre as the Group value and the measure Distinct Count Genre as the Values.

image

Then just resize the Treemap visualization so that the squares are about evenly sized. There’s a few ways you can arrange it, but just play around with it and see what you can come up with.

Power BI Dashboard with Treemap Slicer Power BI Dashboard with Treemap Slicer

Feedback?

What do you think? Leave me a comment below and let me know. Or if you’ve got a neat Power BI trick you’d like to share, let me know, as well. I love to hear new ideas! Thanks for reading!


Read more
34

Importing Excel Power View Dashboards into Power BI

If your organization is now a Power BI customer, congratulations. You’re now ready to create some very cool dashboards, integrate disparate and disconnected data sources and take advantage of Power BI’s ability to modify and transform your data, build interactive and dynamic dashboards and then share them with your team and organization. But until you create your dashboards to take advantage of the new visualization types and other improvements, you can easily import any existing Power View sheets in Excel into your Power BI site.

Power View dashboard in Excel ready for Power BI goodness

Above you’ll see an example of a Power View dashboard that I will import into my Power BI site.

Importing Power View into Power BI

To import Power View sheets into Power BI, navigate to your team’s Power BI site and click the Get Data button at the bottom left.

image

Then select where your data exists. In my case, I have my Power View reports in Excel saved in my One Drive folder so I’ll select Files.

image

image

You’ll have to log into your One Drive account. Once you’ve done that, just navigate to where your Excel file is and then click Connect.

After the import is complete, you’ll see your new Report and Dataset on the left in your My Workspace explorer. Open the new Report based on your Power View sheet. Now I can browse and interact with my Power View dashboard within my Power BI site.

image

I can also pin visualization from my Power View report to a Power BI dashboard with other visualizations.

image

And now that we have our Power View report imported and pinned to a dashboard can use Q&A to quickly generate visualizations using the imported data set.

image

Pretty cool stuff! It’s nice to know that I can easily jump from my legacy Power View dashboards to the new Power BI.

It’s important to not that in order to import Excel workbooks into your Pow

Read more
56789
10111213141516
17181920212223
24252627282930
31123456

AlwaysOn: Configuring Secondary Read-Only Access

Now that you have AlwaysOn configured and your first Availability Group (AG) created, it’s time to start leveraging those secondary replicas.  The first step is to set the connection access for the primary and secondary role, if you haven’t already done so.  You have three choices:

  1. No:  No user connections are allowed to the secondary replica.
  2. Read-intent only:  Only read-only connections are allowed
  3. Yes:  All connections are allowed, but only for read-only access

So the main difference between Read-intent-only and Yes is that the later allows all connections, regardless if it read or read/write, but only read access will be granted.  If you did not set the properties during AG creation you can use TSQL, PowerShell or Management Studio to update the configuration. 

If you have not configured your secondary replicas for read-only access open management studio, locate and expand the AlwaysOn High Availability folder.  From there expand the Availability Group folder, locate your AG and right-click it.  In the Availability Replicas section of the AG Properties screen, locate the replica you want to configure for read-only access and change the Readable Secondary Property. 

image

Click OK. 

Now, before you can actually read the secondary, in addition to setting the Readable Secondary property you will also need to perform two additional configurations.  First you will need to have a AG Listener configured. If you have not configured the AG Listener you can do so using Management Studio.

To create a listener with Management Studio:

  1. Expand the AlwaysOn High Availability folder, expand Availability Groups, locate and expand the AG that needs the Listener. 
  2. Next right-click the Availability Group Listeners folder and select Add Listener.
  3. On the Add Listener dialogue box specify the following:
    1. Listener DNS:  Network Name
    2. Port:  TPC port
    3. Network Mode:  TCP protocol (DHCP, Static IP)

Now you have a listener.  You can now connect to management studio using this Listener DNS.  Give it a try. 

The final step is to configure Read-only routing, which is the ability of SQL Server to route incoming connections to the Listener to a secondary that is configured for read-only access.  The read-only routing only works if the client connects using the AG Listener and the ApplicationIntent property is set to ReadOnly in the connection string. 

To configure the read-only routing you must first set a READ_ONLY_ROUTING_URL for each replica.  A sample script is as follows:

ALTER AVAILABILITY GROUP [PatricksAG]
MODIFY REPLICA ON
N’SQLAlwaysOn1′ WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N’TCP://SQLAlwaysOn1.domain.com:1433′));

You must set this option for each replica.  After you have done this you must set the READ_ONLY_ROUTING_LIST for each replica.  This list specifies how the routing will take place in the order the targets are specified.  A sample script is as follows:

ALTER AVAILABILITY GROUP [PatricksAG]
MODIFY REPLICA ON
N’SQLAlwaysOn1′ WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(‘SQLAlwaysOn2′,’SQLAlwaysOn3′, ‘SQLAlwaysOn1′)));

Again this must be set for each replica.  Once these steps are complete, you can now use the ApplicationIntent property in your connection strings.  This property can be used with the SQL Server Native Client OLE DB provider and ODBC driver, and ActiveX Data Objects (ADO).  The following is a sample connecting string:

Server=tcp:AGListener, 1433;Database=AlwaysOnDB1;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly

If an application connected using the above connection string it would be routed to one of the secondary replicas. 

If you want to see the affects of using Read-intent only over Yes give this a try.  First set the Readable Secondary property of one of your secondary replicas to Yes.  Using Management Studio connect to the secondary using the physical machine name not the AG Listener name.

USE
GO
SELECT * FROM INFORMATION_SCHEMA.tables

In the USE statement you should include a database that is one of your Availability Databases.  Run this query and it should yield results.  Disconnect the server (Secondary Replica) from the Object Explorer and close the query window.  Now change the property to Read-intent only.  Reconnect to the server and open a new query window and execute the same query.  Instead of results you will receive the following error:

Msg 978, Level 14, State 1, Line 1
The target database (‘AlwaysOnDB1′) is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.

To circumvent this problem, you could use the Additional Connection Parameters tab when connecting to an instance of SQL Server 2012 with Management Studio. In the text box type:  ;ApplicationIntent=ReadOnly.  Your screen will look like this(you must include the semicolon):

image

When you connect and run the query everything works great.   I will be doing a SQL Lunch soon demonstrating this and showing how it will work with a .NET application.  Stay tuned.

If you have any questions or concerns regarding this post please feel free to email me at pleblanc@sqllunch.com

Talk to you soon,

Patrick LeBlanc

www.sqllunch.com

Print
Tags:
Rate this article:
No rating

PatrickLeBlanc PatrickLeBlanc

Other posts by PatrickLeBlanc

Please login or register to post comments.