Power BI can Load Data from Multiple Data Sources

This week I’ll show you how to use Power BI to pull together data from multiple data sources that you can then use to create visualizations.

The first step is to open your copy of Power BI Desktop. Then click on the Get Data button. I’m going to begin with some data from SQL Server so I do not have to click the More… button to see all the available data sources, SQL Server is right on the main dropdown.

To work with a SQL Server database, I must first enter the name of the SQL Server that I want to connect to. Note that while the dialog shown below prompts for the name of the database, this information is optional. You might think, ‘How can the database name be optional?’ In a moment, I will show you that Power Bi first needs to connect to the server, but then it can retrieve the database names that you have access to and display them for you to select. This protects you from the possible misspelling of the database name in this dialog. Since I am running everything on a single machine, my SQL Server name is LocalHost.

Of course before you can connect to the server, you must provide credentials. I am running everything on a Surface Pro 3 so I will use my Windows credentials. However, if you need to connect to another server on your network, you may need separate credentials.

The following dialog may or may not appear depending on the database you are connecting to and your version of SQL Server. However, in most cases, clicking the OK button allows you to connect to your data as long as you have rights to the SQL Server box.

When Power BI connects to the server, it first begins with a list of the possible databases along the left side of the screen. By opening a database (clicking on the arrow to the left of the database symbol, I can see the tables in the database. I can select multiple tables from this database. I can even select multiple tables from multiple databases. With each table I select, I will see a sample of the data in the right panel. Note that unlike PowerPivot, there is no way to limit the columns or rows from the table at this point. Maybe in the next major release.

You’ll notice that this week I return to my favorite sample dataset, Contoso.

If I select multiple tables to load, they all appear in the following dialog that shows me the progress of loading my data. In the image below, I can see that I’ve selected five tables from the database. As the data load progresses, Power BI shows me the number of records loaded in the line beneath the table name.

After the data loads, I notice that the table names appears in the Fields column on the far right of the desktop. But you say that these are table names? If I click the arrow to the left of each table name, the table expands to show the names of the fields essentially grouped by table.

Next I can click on one of the icons on the left side of the desktop. The first icon is the report surface. I typically work there after I have loaded all my data and made any changes to the columns and/or rows. So some might think that this icon should be on the bottom. The second icon is the data icon. I can use this icon to edit the column definitions, add new columns, or remove columns that I no long need. I showed some of these techniques in prior weeks of this blog.

However, I usually start with the bottom icon, Relationships. Relationships are the heart of any multi-table analysis. If I do not define the relationships between the tables first, I really cannot do any analysis. I might not be able to create some column either. So let’s start there.

The diagram that Power BI creates shows the tables that have been loaded into the model and if relationships were defined in the source data, it tries to create those relationships for me automatically. Notice that the diagram clearly identifies the one side of the relations with a ‘1’ and the many side of the relationship with an asterisk.

Unlike the diagram view in Power BI, there is no ability to edit, delete, or create new relationships from this dialog. Nor can I delete columns, define hierarchies, or rename columns here. I can hide individual columns or entire tables from the report view of the model and I can maximize the view of the individual table diagram, but that’s it. Again, maybe in the next major release of Power BI these additional features will appear so that the functionality matches that of PowerPivot diagram views.

Next I’m going to load some additional data into my model beginning with some data from an Access database and then data from Excel. I’m not going to take the space here to show screen images of these processes because between the above description and prior week blog discussions, I believe that you can figure out how to load data from different data sources. The only problem you may encounter is the need for new or updated connectivity tools to access your other data sources.

Once I have loaded all my data, my first action is to go to the Relationships view. You can see in the following figure that Power BI automatically shows my additional tables that I’ve loaded, but they show no connection to the rest of the tables in the data model. I need to fix this before going forward.

Find the Manage Relationships button in the Relationships group in the ribbon and click on it.

A dialog appears that displays the current relationships between the tables. This view is similar to the manage relationships dialog in Power Pivot. I can add, edit and even delete relationships here. However, the interesting button is the one labelled Autodetect

When I click Autodetect, Power BI attempts to find and create relationships between the tables that are not currently connected to the data model. It does this by analyzing the field names in the new tables with names in the tables of the data model to find other fields with the same name and the same data type definition. Of course, one table must serve as the parent (one-side) and the other table must serve as the child (many-side). In most cases, you will start with a fact table and create branching child tables so they usually serve as the one side in each of the new relationships. If Power IB is successful, it will attempt to create that relationship and will first tell you in a dialog box such as the one shown below how many relationships it detected.

After closing the information dialog reporting the number of relationships found, the relationships are created and the results for this model appear in the updated Manage Relationships dialog shown here.

Note at this point, I could edit any of the relationships that were not correctly created, I could also add relationships between tables that perhaps had different field names that could not be automatically detected. It is a good practice to check the Relationships diagram to insure that every table is connected to the model through at least one relationship.

I am now ready to go to the Reports desktop to create some simple charts.

Creating a report is as simple. First click in a blank area of the Reports desktop. Then click in the checkbox to the left of the value field you want to count, sum, or average such as Sales Amount. Then click on the field or fields by which you want to report Sales Amount (or any other value field). For example, I might want to see sales by year or by Product category or by Channel. You can create as many visualization as you want on the desktop although you should keep in mind that they need to be large enough to be readable. Let’s start with the three shown below.

Not a bad start. Here is a fun feature that Power BI supports that Power Chart does not. Just click on the Year 2009 column in the chart in the upper left. Immediately, all charts on the page change to shows the sales related to the year 2009 in a darker color and sales from other years in a light color.

Note that I did not have to do anything special to get this to work. I did not have to create a filter, a slicer, or anything. Similarly, I could click on columns in other charts to slice and dice the data different ways.

Filtering the data on the page is actually done entirely differently. To filter the data that appears on the page, you have to define a page filter by dragging the field or fields that you want to filter by into the area beneath Page Level Filters found in the column to the immediate left of the column with all the table fields. Filter fields typically are string values and typically, there are not a large number of unique value. To filter the page, just click in the checkbox to the left of each value that you want to include on the page. You can filter on one or many values at a single time.

After selecting a page level filter, the visualizations on the page change automatically to represent only the filtered data rather the entire dataset. The following desktop image represents only the data from the year 2009.

Well, that’s all for this week, but if you have been following along, save your data from this week because I’ll pick up this sample data from this point next time.



Formatting Power BI Visualizations

Being able to mash together data from multiple sources to create meaning full analysis in Power BI or even PowerPivot can be lots of fun, at least to my data geek friends, but presenting visualizations of that data to management without the ability to customize the look would fall flat on its face at your next executive presentation. Therefore, I am taking a side trip this week to show you a little about formatting your visualization, enough to get you started exploring how to make individual visualizations look good.

I also want to introduce you to a set of simple data table that you can use for free while learning how to use Power BI. A team from the Paris Technical college put together some data and perform some basic clean-up of the data. The team includes: Petra Isenberg, Pierre Dragicevic and Yvonne Jansen and the data can be found at: http://www.perso.telecom-paristech.fr/~eagan/class/as2013/inf229/labs/datasets.

For today’s blog, I am going to use their CARS dataset. While it has over 400 cars with 8 characteristics, it does not have the new Tesla X model L, but it is a good dataset to show some basic visualization formatting technics.

Thus I start by importing the CARS.CSV file by using the Get Data option.

After loading the dataset (shown below), you can see a preview of the data in which the first two data rows are not cars. In fact, the first row looks like it could serve as column headers or in terms of our data set, column names. While the second row looks like it defines the preferred data type for that column. To begin loading the data, click the Load button in the bottom right of the screen.

After loading the data, the first thing I need to do is to make some quick changes to the dataset by clicking the Edit Queries button.

In the Transform group of the Home ribbon of the Query Editor, I can click on the option: Use First Row As Headers. This is similar to the PowerPivot load technique in which you could define whether your data included headers in the first row.

Next I want to get rid of the row with the data type (which is now the first row). But before going further, I take note of the data type of each column. I will need that information in just a moment. In the Reduce Rows option group, there is an option to Remove Rows.

Click the bottom half of the Remove Rows button. This action opens a dropdown letting you specify how you want to remove rows. I am going with Remove Top Rows because the data type row is now the first row in the dataset. This option now opens a dialog that lets me specify how many rows I want to remove from the top of the dataset (Do you see why I had to deal with the header row first?). In this case, I only need to remove 1 row, but you may encounter other datasets that require you to remove more than a single row.

Next I noticed the car column appears to be a concatenation of the car make and model.

Looking through the data, the car model appears to always be a single word. Therefore, there is an easy way to split this column by click on the Split Column button and then select By Delimiter.

The obvious delimiter here is the left-most space character.

After splitting this column and renaming the columns as Make and Model, my data table looks like the following image.

Next, I noticed that all the numeric data was loaded as text. This is a direct result of reading data from the CSV file. Remember the second data row which told us the preferred data type? We can use that information to change the data type (in the Formatting group of the Modeling ribbon) of each column as shown in the following image.

I also want to change the year column to a four digit year. Because this data does not include cars from the 21st century, I can get away with simply concatenating all the values in the YEAR column with a prefix of ’19’.

After completing all my data preparation, I can click on Close and Apply so I can begin creating reports (visualizations) with this data.

Let’s begin with a simple table of average MPG for each year. On a new report page, I can drag the columns MPG and YEAR from my list of columns for the table. Note that by default, numeric values are summed. To change MPG to calculate the average MPG, open the dropdown for MPG in the Values section of the Visualizations column on the right and click on Average. If YEAR is being treated as a number, you can select ‘Do Not Summarize‘ from its dropdown. My table now looks like the following image.

I would rather see the year first, then the MPG so I need to change the order of the columns. You can do this by clicking on and dragging the field name in the Values section of the Visualizations (where I changed the aggregation types) and dragging the field up or down as appropriate to place the columns in the order I want.

While this table is interesting and shows a general increase in the average MPG of cars in the dataset, it is not as clear to see how the average rises and falls from one year to the next. Therefore, I might want to change to a column report.

While this is a nice basic chart, it needs labels to explain to someone viewing it for the first time what they are seeing. Let’s begin by clicking on the Format icon just below the Visualization selection grid.

Now instead of seeing the Values and Filters groups under the Visualizations, we see various chart components. Each of these components have a dropdown arrow on their left which will open that section to expose additional properties for that chart element. They also include a simple sliding switch to turn that element on or off within the chart. Note that currently the X-Axis and Y-Axis are turned on, but Data Labels, Title, and Background are not.

If we open the properties for the X-Axis, we see formatting options for this chart element. Although not needed here, I can change the axis type, the axis scale (linear or logarithmic), a start and end value for the axis, and a Title, Style, and Color. For the X-Axis, I only want to turn the Title property on by moving its slider to the On position.

Next I open the Y-Axis properties. I change my axis scale to begin at 10 and default to automatic for the end value. Often when data values are very similar, plotting their values with a y-axis that starts at zero de-emphasizes the variations from one value to the next. Setting the starting point of the y-axis to a value closer to the smallest y-axis value will emphasize those differences. Think of a stock that trades in the lower $200s range with a daily fluctuation of a single dollar or less. Those fluctuations are masked if the chart has a y-axis data range of 0 to $250. On the other hand, changing the y-axis to begin span only the range 200 to 250 emphasizes the daily fluctuations.

As you can see below, my chart is starting to look a little better.

Next I open the data colors group. I can change the default color which is an aqua shade to any of the other colors by selecting either from the Theme Colors provided in the Default Color dropdown by going to the Custom Color option and defining my own unique color for the chart.

However, this changes the color of all the columns to that one custom color. What if I want to display different columns using different colors? If I slide the Show All slider to On, the property dialog now shows a separate color selection for each column in my chart as shown below.

Did you notice that the years are not displayed in chronological order? Did you wonder why? The years are actually displayed in order by the size of the Average MPG so that you could, if you want, define a progressive shift in color shade as the value of Average MPG increases. That being said, I decided to customize the colors by year as shown in the following image.

Finally I went back to turn on the Y-Axis title to arrive at the visualization shown below.

To add detail to the chart, I also turned on the Data Labels group which causes the data value to display at the top of each bar.

Now my chart shows both the actual data values as well as the relative change from year to year.

Finally, I turn the title element on. Here, unlike the x-axis or y-axis, I can specify the text to display as the title. For the x-axis and y-axis labels, the text is taken directly from the column names. I can also specify the font color, a background color and the alignment of the title across the top of the chart.

My chart is starting to look pretty good but it is still missing something.

I open the last chart element group, Background, and turn on the background giving it a custom color.

Now I have a final version of the chart that I might want to publish and add to a dashboard (which I will talk about in a future blog.)

Okay, while I did not show everything that formatting can do, I did want to give you a start. Please note that if you use other visualizations, they have their own formatting options as well which work the same or similar to these. Have fun playing with visualizations until next time.


Update to Power BI Desktop

On September 23rd 2015, Microsoft has released an upgrade to the Power BI Desktop that includes 44 new features. If you are following along with my blog entries on Power BI, you might want to download this release before reading my next posting. You can get the new version (it’s free so what are you waiting for) from: http://blogs.msdn.com/b/powerbi/archive/2015/09/23/44-new-features-in-the-power-bi-desktop-september-update.aspx. The new version number is: 2.27.4163.351.

This article includes details on all of the new features along with helpful images that help explain how to use them.


Analyzing SharePoint Survey Results with Power BI

The last two times, I introduced some of the basics of using Power BI with web site data from a third party. This time, I am going to show you how you can retrieve data from SharePoint and analyze it with Power BI.

To get started, I am going to open a new Power BI desktop instance and select More from the Get Data button to define a new data source, a data source within my SharePoint farm.

When the Get Data dialog appears, I can scroll down through the available data sources until I encounter SharePoint List.

When I want to pull data from a SharePoint list, I need to enter the URL of the SharePoint site, not the URL for the list itself as shown in the following figure.

I can navigate to my SharePoint site and capture the URL from the address bar in the top of the browser.

Note however that I do not need the entire URL, I only need the portion of the URL that defines the site. Therefore, I can remove the /Pages/default.aspx which references the home page of the site.

When I click OK, Power BI will go out the SharePoint site and look for the lists defined in the site. But before I get to that, I want to make a point that Power BI did not ask me for my credentials when I went to this collaboration site. The reason is that particular farm uses Kerberos authentication so my Windows authentication was passed through to SharePoint which allows me to see the contents of the site. If I did not have rights to the site, Power BI would not authenticate me or provide a list of the lists. A little later, I show you what you may see if you don’t use Kerberos to pass through your Windows credentials.

The next thing I noticed when I saw the Power BI Navigator showed me the names of the available lists is that some of the items were not lists, at least not strictly speaking. When I looked at the names, I noticed that some of them were from the Discussion Board area and some were from the Surveys area. Interestingly, the Navigator does not treat libraries as lists. Therefore, if I use a form tool like InfoPath which writes the metadata back to the form library, I do not think at this time that I can access that data from Power BI.

On the other hand, Power BI could provide an interesting way to access and analyze survey data. I just happen to have a survey result list available to take a look at, so I begin by entering the URL of the survey site.

This instance of SharePoint is not currently setup to work with Kerberos. Therefore, I get the Access SharePoint dialog shown below by clicking Windows authentication in the left side menu. In this case, I need to switch from using my current credentials to using alternate credentials and then supplying the appropriate username and password. I still want the full address to the survey site as before. Then I can click Connect.

Now Power BI can successfully interrogate my site to look for lists and display them in the Navigator as shown below. For this demo, I only need the single list Senior Exit Survey. (Note that all blanks are removed from the list names.) In a future blog, I will demo how to work with multiple lists/tables from a single source, so be patient.

After selecting a list, the Navigator displays a preview of the data in the right side panel. Note that the column names, like the list names, have had all blanks removed. Actually Navigator removes other non-alpha characters as well. In fact, column names cannot even begin with a number. If you have column names that begin with a number, Power BI will automatically precede the names with “c_”.

Another thing I noticed is that the preview only shows the first several lines of data and then displays the text: ‘The data in the preview has been truncated due to size limits.’ Don’t worry about this. The data is all there.

After I click the Load button, Power BI starts to load the data into its data model. Depending on the number of column and rows, this process can take from a few seconds to a minute or more. However, eventually, the load completes and I can see the table by clicking on the Data icon along the left side of the screen and then clicking on the table I want to view (if I had more than one table).

Note that I could click on the Edit Queries button at this point to clean up some of the data getting rid of columns that I will not need, but I’ll skip that step for the sake of keeping this blog a little shorter.

Next, I click the Report icon in the left margin of the desktop. Here I can see the columns in my table. If there were multiple tables, I could collapse and expand the column list for any table by clicking the arrow to the left of the table name.

Let’s say that I want to plot the number of each response to the question: ‘I clearly understood the requirements for graduation.’ I can click in the check box to the left of that column to identify the question and then click the check box to the left of the ID value which I can use to count the responses.

Note that by default, Power BI may try to sum the ID value. Remember from last week I should you how to change the aggregation of a value from SUM to COUNT.

I’m also going to initially default the visualization of this data to table. Therefore, my two right columns for Visualizations and Fields should look like the following figure.

My table should look like the following figure. If the columns are not in the correct order, you can simply click and drag the column names in the Values section of Visualizations to get the order you want.

Instead of seeing my data as a table however, I might want to display the data using the Funnel visualization and sort by the count of ID as shown in the following figure. Changing the sort can be accomplished by clicking the ellipsis in the upper right corner and selecting Sort By to change the sort field.

In a similar fashion, I can click in a blank area of the desktop and create a column graph visualization for the responses to the question: “This high school has prepared me for my career choices.” The chart shows the degree to which the respondents agreed or disagreed with that statement. Note that in this case neither sorting by the count of the IDs or by the text of the answers provides a satisfactory ordering of the data. I’ll return to this at a later date.

Clicking in another blank area, I selected the count of ID along with the date the record was created or when the survey was taken. As you can see in the following figure, the data appears to be a little odd in that there is no date with more than 4 surveys. I did not believe this because with over 9500 records spread out over less than 150 days, that low count per day just does not make mathematical sense. Remember last time when I said that Power BI can create good charts and bad charts. The user must know their data to determine when the data is not being displayed correctly. In this case, the reason for the unusual result is that the create date is not just a date, but a date and time. Therefore, most surveys, even when taken by over 9500 students, do not occur within the same minute although statistically, some will.

So how can I fix this? I need to return to the data table and make a few changes. I really don’t want to lose the original data, so I create a duplicate of the Create column that I can modify.

In my new column, I can change the column name to: Day Created and then by right clicking on the header, select Change Type and then Date to change the field to a simple date with no time associated with it.

When I go back and change my chart to display the count of IDs by Day Created rather than the Created column which included time, I get a more realistic chart as shown below.

If I were to zoom out, you can see that I have all four of these visualization on the desktop at one time. In fact, other than the amount of screen space, there is no limit to the number of visualizations you put on a single screen.

Now let’s try something interesting. Suppose I select one of the days in the last chart that I create, specifically, Friday, January 23, 2015. I can see that the popup information box tells me that were 697 surveys taken on that day.

But more interestingly, you will notice that the rest of the visualization on the page automatically changed to emphasize the counts representing that one day with a darker shading and for the table, the numbers in the first column represent the count for each answer for only that one day.

That’s quite a bit for this week. Next time I take a further look into working with Power BI and SharePoint Survey data.

C’ya then.

The Best Year for Television Was 2010 says Power BI and imDB.

So last week I started by showing you how to get Power BI Desktop downloaded and installed on your computer. Hope you were able to do that because I’m going to focus on it for the next couple of months.

In addition, I took you through the steps of how to reference data from the movie and television web site: www.imdb.com. I specifically suggested that you download the top 250 television shows by going to a specific page URL that I included in the blog. (Go back to last week’s blog to see it if you are just joining us now. Really! I’ll wait right here.) I specifically left you in the Navigator dialog which showed a preview of the first 20+ records in the table. Now I must say that there is really nothing special about this page or the data. Any data displayed within a table structure on a web page can be referenced from any site. While I am not going to show it this week, this includes referencing data from a SharePoint list displayed as a web part on a page within SharePoint. (You were wondering if I would tie this all back to SharePoint. Admit it.)

After clicking the load button on the Navigator, Power BI begins to load the data into its data model. While this step acts much like the data load step in PowerPivot when getting data from an external source, there is at least one major difference. Power BI does not currently have a preview capability like PowerPivot to select columns and filter rows. While I will show how to do some editing of the loaded data in a moment, the thing that bothers me about this is that it inflates the size of the data model during load. When I talk about pulling data from a SharePoint list in a future week, remember that I can customize the list view to show only the data that I need to load into Power BI.

So here is the view of the data table loaded from the top 250 television shows.

Notice that the table has some columns that I do not need and it has a column which concatenates three different pieces of information: Rank, Show Title, and Year. I need to edit the table and I can do that by clicking on the Edit Queries button in the Home ribbon.

Now the first thing I want to do is to get rid of columns that I don’t want. In this case, the first column appears empty. The Your Rating column and the column with a heading value of ‘2‘ are not needed either. My first thought might be to select each column and then select Remove Columns from the Home ribbon as shown the prior image here. But I can also select the columns I want to keep and then select the Remove Other Columns option shown in the following figure. Note, I can press and hold the CTRL key while I click on each column header I want to select or I can click on the first column header I want and then while pressing the SHIFT key, click the last column I want and all columns between these two columns will also be selected. Which method I use to select the columns I want or don’t want is up to the individual situation as one method may be easier than the other.

I now have two columns, the Rank & Title column and the IMDb Rating column. Next, select the Rank & Title column and click Split Column in the Transform group of the Home ribbon. I want to split the column into multiple columns. In some cases, I may have a data structure which lends itself to be split at a specific number of columns (characters). But that is not the case here. I want to split the data at the first space character in the field which may be the third, fourth or fifth character from the left. Therefore. I select the By Delimited option.

In the dialog that appears, I can choose from one of 6 common delimiters. If the data includes these delimiters within their values, I may need to create a data set that uses a different, custom delimiter. The interesting feature of the custom delimiter is that it is not limited to a single character. However, in this case, I can use the Space character delimiter. Using the Custom delimiter (period-space) might also be a good choice in this case so that Rank can be displayed as an integer without a decimal point.

After selecting the delimiter, I can choose whether to apply this rule at the left-most occurrence of the delimiter, the right-most occurrence or at every occurrence. In this case, I only want to apply it to the first occurrence from the left.

You should also note that when Power BI Query Editor splits the column it creates one column from all the characters to the left of the delimiter and a second column with all the characters to the right of the delimiter. The delimiter itself is thrown away.

After applying the command to split the column, I may want to rename the new column. I can do this by right clicking the column header and selecting Rename from the drop-down menu.

In a similar fashion, I can split the year of the TV show into its own column. (I’ll leave you to figure out the steps you need to do this based on my description above.) After renaming the column with the year and renaming the Rank & Title column, my table now looks like the following.

Hint: Use the Split Column function to remove the right parenthesis and then deleted the resulting created column.

I am now ready to start using my data to perform some analysis. To do this, I click on the Close and Apply button on the far left side of the Home ribbon.

This returns me to the working desktop as shown in the following figure. Note the three icons along the left side. The top icon opens the visualization desktop where I can display different charts and tables of my data. The second icon shows me my data tables. If I have more than one table in my data model, I can switch between the tables using the list of tables on the right side of the screen. I will not need to do that here since I only have a single table. Finally the last icon lets me see and work with the relationships between multiple tables. Again, for this week, I do not have to worry about relationships between tables because I only have a single table. I will cover working with multiple tables at a future date.

Switching to the visualization page, I can see the fields in my table along the right side of the screen. To begin a visualization, I can simply click on a table field and drag it into any blank area on the desktop. For example, supple I drag over the fields ID and TV Show and then change the visualization to a vertical column chart as shown below.

While Power BI did create a chart, the chart does not make much sense. I can see the names of the TV Shows across the bottom of the chart, but plotting the value of the show ID field as the vertical value of each column does not make any sense. Maybe if I had the total number of people who watched the show, it would be a better chart.

However, my point in making this chart is to emphasize the need to know and understand your data before you just go off and create visualizations. While Power BI can make analyzing your data easier, it can also make creating meaningless charts easier as well.

So what can I do? Suppose I plot year across the horizontal axis instead of the TV Show name. Now Power BI plots the vertical column as the sum of the show ID values. Again, not very useful. However, if I open the dropdown for the ID field in the second column on the right side of the screen under Value, I can change the default action from Sum to Count.

Now I have something useful, the number of top TV shows by year as shown below.

You can see that 2010 had the most top TV shows. I wonder what they are? Next, I drag the TV Show field by itself to an empty area of the desktop and choose the Table visualization. (Just hover over the visualization icons and you will see their names.) But I don’t want to see all the TV shows, just those in the best year for TV, 2010. So I add the field Year to the Visual Level Filters in the second column on the right. Clicking on the down arrow to open the properties, I unselect (All) and select 2010.

Now my list only displays TV shows in 2010.

Finally, I want to see the rating of each show so I can click on the checkbox for Ratings or I can click on Ratings and drag it over to my table. To see the TV Shows from the highest rating to the lowest, I can click on the Ratings header. Repeated clicking on the header changes the sort from ascending to descending. Once I sort the shows by descending order, my list of top TV shows from 2010 looks like the following:

My point in doing this exercise was not really to get a list of the top TV shows for the year with the most top TV shows (although that is what I did), my point was to show you how easy it is to grab data from a web page that you did not create and perform analysis on the data found there. That is HUGE.

In future blogs, I will cover additional features of Power BI.

Hope you found this blog interesting and want to learn more about Power BI. Be sure to come back regularly.

C’ya next time.

Power BI Desktop – Your First Hour

For those of you who have been reading my blog for a while, you know my interest in PowerPivot, Power Chart, and Power View go back several years. (If you don’t, you have a lot of old blog entries to catch up on.) Anyway, I’ve been spending some time recently looking for what the next big thing is for data analysis. Several others have indicated that Power BI might fill that requirement but I spent some time looking around first before agreeing and picking up Power BI to see what it could do. I’m still getting acquainted with it, but I decided that I like it enough to take you along on my journey. So first I need to help you catch up a little.

The first thing you will need to do is to get a copy of Power BI Desktop installed on your local computer. Note that I said local computer, not your server. In fact, I’ve been running it quite successfully on a Surface Pro 3 with Windows 10 and SQL Server 2014. If you do not have SQL Server on your desktop but have it on a server that you can access, that will work just fine. For today though, you will not even have to worry about SQL at all.

To download and install a copy of Power BI Desktop, go to https://powerbi.microsoft.com/desktop.

You can also go directly to the download page at: http://www.microsoft.com/en-us/download/details.aspx?id=45331. You will see the system requirements on this page and you may notice that Windows 10 and SQL Server 2014 are not listed. That was all the more reason to try it and I can tell you that it appears to work just fine.

However you get to the download page, start the download and then install the application on your desktop.

Download and install the desktop. After the desktop is installed, it should start automatically. If it does not, go to your Start menu an entry that says Power BI Desktop. On Windows 10, this appears in a separate section along the left side under Recently Added. It may also create an icon on your desktop and even possible get added to your taskbar across the bottom of the screen in Windows 8 through 10. In some way, start it and you should see the following dialog at appears on top of the desktop application which fills in the background:

Along the left side are options to get data. We will see and explore some of the many ways you can get data from different sources over the coming weeks. You can also return to recent sources that you used. Next is a menu item that says, ‘Open Other Reports’. Desktops with their various charts, tables, and other visualization are referred to as Reports and are stored with a .pbix extension. If you had previous reports that you worked on and saved, they would also appear in this section allowing you to click on them to directly open them.

There are also several videos and tutorials listed in this dialog to help you get started. I strongly recommend that you watch these videos and perhaps read some of the tutorials. In fact, there are many more videos and tutorials for Power BI Desktop. Another good page to help you get started is the Microsoft Power BI Support page at: https://support.powerbi.com/knowledgebase/articles/471664.

Microsoft provides several sample databases and in future weeks, I will probably use the ContosoDW databases that I previously used for many of the PowerPivot blogs. But for today, let’s just try something to amaze your co-workers.

Almost everyone knows the site www.imDB.com. Using the new Edge browser, I went to the site and discovered that there are some new pages available. Most people use it to look for information about movies and the actors in them. The site also has information on television shows. In fact, it has two new pages of interest. One of the new additions is a page that displays the top 250 movies and another page shows the top 250 TV shows. Just because everyone does movies, I went to the top 250 TV shows page just to find out which year had the most highly rated shows. The following image shows a little of that page.

But what I’m really interested in is the URL. I can select the page URL from the Address box at the top of my browser. It should look something like the following:

Next I go back to my Power BI Desktop and click the Get Data icon in the External Data group of the home ribbon. Like many buttons, this one has a top portion and a lower portion. The bottom portion will open a dropdown of common data sources as shown below:

However, I can also click on the top portion of the button to display the following dialog box. This box shows categories of external data sources on the left and the names of specific data source types on the right. Since I want to get data from a web page, I need to specify a Web source. I could check each category just to see what was available and eventually find Web in the Other grouping as shown in the following figure or I could have selected Web from the previous figure. Either way, takes me to the same place. (But it is interesting to see all the possible data sources, isn’t it?)

After selecting Web, I am prompted to enter the URL of the web page that holds my data. This is where I past the URL that I captured previously when I displayed the page of top 250 TV shows.

When I click OK, Power BI analyzes the page and displays objects that might be something I’m interested in.

Of course the names like Table 0 and Tab1 have little meaning. However by selecting any of the objects, Power BI Desktop displays a preview of the contents of the object on the right side. This is how I ‘discovered’ that Table 0 contains the data representing the top 250 TV shows as shown below.

When I click load, Power BI Desktop loads the data into its local data model. In many cases, that model may contain columns that we do not need. It may also contain columns of concatenated data such as in this case in which the show ranking, show name, and show year appear concatenated together. We will need to fix this. Ultimately, I want to get to a table that looks something like this:

However, that will be next time. Have a great Labor Day weekend and get Power BI Desktop installed on your machines because next time we are going to start manipulating the data and creating visualizations.


SharePoint Governance and The Balance

For many, SharePoint Governance is a document, a contract, between the people who support SharePoint and the rest of the organization that sets forth:

  •  policies,
  •  rules,
  • roles,
  • and responsibilities

of the system. Failure to manage these four areas could jeopardize the success of SharePoint in the organization.

 These four areas, like the four legs to a chair, provide a stable platform on which to build an organization’s SharePoint environment. SharePoint Governance can group these four areas into the following four groups within the organization.

  • Operational Management: This group defines the roles and responsibilities of those who are ultimately responsible for the SharePoint portals within the organization. This group can consist of a governance committee or simply a few of the organization’s top executives. They identify the overall features of SharePoint that will be used within the organization. Effectively, this group defines the policies related to SharePoint.
  • Technical Operations: This group defines the technical structures of how SharePoint will be deployed, any software and hardware requirements, specific features to activate, uptime availability, backups, authentication, and which classes of users can access different elements of the portal effectively defining internal and external sites. These activities largely define the rules around the SharePoint implementation.
  • Site and Security Administration: This group is responsible for the creation and destruction of sites as needed along with defining site ownership and the corresponding responsibilities of different user groups within each site or class of sites. They define best practices on defining permissions and provide support on how best to organize site collections. Security within SharePoint is established by the individual’s role within the site.
  • Content Administration: This final group defines the nitty-gritty details of how to load and display content within the site. It is responsible for creating guidelines for the use of content types, workflows, metadata, and various web parts to achieve content goals. They may also help determine life-cycles for content retention policies and policies used to enforce the archiving and deleting of older content. This group identifies and assists users with their responsibilities for building and maintaining sites.

However, failure of SharePoint to succeed because one or more of the legs of that governance chair are not stable should not be indicative of an inherent problem with SharePoint. In fact, failure to create and then follow the governance policies, rules, or recommendations is more of an indication of the failure of the organization. If an organization cannot create a governance document that manages SharePoint usage, that is indicative of a greater potential problem, one in which top management may not support the use of the tool or understand its needs or benefits in the first place. This lack of support could be an early warning sign that the project may not be valued within the organization.

Even with governance for SharePoint or for any other product in place, it remains only a paper (or electronic) document unless management establishes an infrastructure to enforce it. Once policies, rules, roles, or responsibilities start to be bent or ignored in small ways, it is a slippery slope to the point where everyone ignores the governance document and chaos begins to take over. It may not be long before top management begins looking for a new solution, one that will magically cure all the current perceived problems. It may not occur to them that simply enforcing the original governance would alleviate most if not all of the current problems. On the other hand, enforcing strict standards in the name of governance is like putting blinders on a horse that could prevent the organization for discovering that there are better tools and better ways of doing things.

Furthermore, do not interpret governance to limit when or if an organization can switch tools or processes. Switching tools should always be possible, especially if another product with significantly better features or improved functionality becomes available. Governance does not address the issues of when a tool or process becomes obsolete. It merely addresses how to use that tool while it is in use.

At the same time that governance should define specific actions or activities, it should be a living document that can change over time to satisfy new demands. If those demands can be met by making small changes to the product or the way it is used, the overall costs of meeting the organizations needs will be minimized. Thought of in another way, governance is nothing more than a roadmap in which the organization can achieve the maximum benefits from a process or tool while minimizing the costs. It keeps everyone moving in the same direction rather than letting everyone to go off in different directions doing their own thing. Governance that is too strict can strangle an organizations ability to adapt and create new solutions to problems. Governance that is too loose will prevent directed and organized progress toward a goal.

A balanced governance approach can be in everyone’s best interest but can be difficult to obtain.

A Table of Contents for Your Site

Continuing on the theme from the last two weeks, I am going to take a look at another web part that is infrequently used, at least on the web sites that I work with. We use the Site Navigation feature out of the box for navigating between pages on a site and even to add custom links to pages and sites outside of our site. We also find the Site Navigation feature lets us build pseudo-hierarchies by adding header entries and links that serve as a fly-out to the header. But there is another way to provide navigation around a site. That is with the Table of Contents Web Part.

The Table of Contents web part lets me display all of the pages and sites that branch off the current site. The key advantage to this web part is that I can display up to three site levels (the current level and two nested sub-site levels) on a single page. It also gives me some built-in formatting capabilities to change the way the Table of Contents is rendered on the page ranging from vertical orientation to a horizontal orientation and several hybrid types in between. So let’s take a look at this web part.

Like other SharePoint web parts, I have to add the Table of Contents web part to a page on my site. This may mean that I have to first create the page where I want to place the Table of Contents. Then I can edit the page and going to the Insert ribbon and select the Web Part button from the Web Parts group. In SharePoint 2010, the Table of Contents web part can be found in the Default category as shown in the figure below. However, in SharePoint 2013, it was moved to the Content Rollup category which actually makes some sense.

After select the Table of Contents web part, click the Add button on the bottom right of the dialog. After adding the web part to the page, SharePoint provides a default view of the site. This view displays pages and sub-sites off the current site as shown below or it may only display sub-sites and their pages. In either case, I can easily modify the properties to control where the Table of Contents begins.

As with all web parts, I can edit the properties by hovering over the header to display the down-pointing caret on the right side of the header. This is the web part menu. I click on it to open the menu and select: Edit Web Part. The properties panel for the web part appears to the right of the page. You may need to scroll to the right and up the page to find it depending on the size of your page. The most interesting properties and the ones I will focus on appear in the top section of the panel. There are three sections: Content, Presentation, and Organization as shown in the following figure. Each has a box with a plus sign in it in. To open each section to view and modify its properties, click on the plus sign in the box to the left of the section name.

The following figure shows the properties found in the Content section. The first property identifies the site where the Table of Contents will begin. Note that you can either enter the URL or you can select it by clicking on the Browse button.

The second property identifies how many sub-site levels you want the Table of Contents to include. The maximum is three levels. There is also a check box where you can identify whether the site where you are beginning the Table of Contents should be included or whether to only include sub-sites under the current site and their pages.

Finally, this section has three check boxes where you can select whether you want to show pages (or just the sub-sites in the Table of Contents. You can also choose whether you want to display hidden pages or hidden sites. I suppose if you need to see everything in a site, you might check these two boxes, but most times I would guess that they were hidden for a reason.

The second section lets me modify some of the presentation features of the web part beginning with a header for the web part. Note that this is not the same as the web part Title that appears in the chrome of the web part. In fact, you may turn off the chrome and use this property to place a header at the top of the table of contents.

You can also modify the style of the header. The figure below shows the pre-defined styles available for the header.

You can also define the number of columns to divide the content area into. You can also modify the styles for the other levels. This will affect the sub-site entries in the Table of Contents.

The third section deals with organization of the items in the Table of Contents. By default, SharePoint orders the items in the Table of Contents to match the navigation. However, for large sites with many pages or many sub-sites, this may make it harder to find what you are looking for. Therefore, SharePoint provides a way to provide a custom ordering of sites and the pages within the sites.

If you select the custom sort option, you can sort the sites by Title, Creation Date, or Last Modified Date. You can also choose the direction of the sort as either ascending or descending. For example a descending sort on Creating Date will list the most recently created sites or pages near the top of the list making it easier to find what is new on the site.

After you have set all the properties, you can apply your changes and click OK to view your Table of Contents page.

That’s all for this week. C’ya next time.

Pulling It All Together with the Site Aggregator Web Part

We saw last time how I can easily see all the documents in the current site that I last modified or created with the Relevant Documents web part. However, what do I do if I want to see all of my documents in any one of several different sites? Do I have to navigate to each of these sites and open a page with a Relevant Documents web part?

Fortunately, there is an easier way! The Site Aggregator web part allows me to view my documents stored in any number of sites from a single place, sort of.

After reading my last blog article, I’m going to assume you know how to add a web part onto a page in your site. (If you skipped that blog, you can always go back to it at: https://sharepointmike.wordpress.com/2015/06/27/) As with the Relevant Documents web part, the Site Aggregator can be found in the Content Rollup category for both SharePoint 2010 and SharePoint 2013. After adding the web part to the page, it looks something like the following:

At first, I may be puzzled by the text telling me to click on the “Add New Tab” icon. The first thing I should know is that each site that I want to pull documents from will be displayed separately and that I must choose the site I want to view by clicking on a tab/link across the top of the web part. To add a new tab, I need to click on the icon that appears in the top right of the Site Aggregator that looks like a drive icon with a yellow asterisk in its upper right corner.

This button displays a dialog that lets me enter the name of the site that I want to view. For example, the following figure shows a reference to a demonstration school site. Note that the URL does not point to a specific page. Rather it is the URL of the site only. Also notice that the URL must end with a slash ‘/’. The second property in this dialog is the name that will appear in the tab/link across the top of the Site Aggregator.

When I click the Create button, the Site Aggregator shows the contents of all libraries in the selected site and lets me click on the document name to open the document directly or by clicking on the location, to go to the document’s library.

So far, that works pretty much like the relevant documents web part. The feature that makes this web part different is that I can click on the Add New Tab to add another URL to a different site. In fact, I can add several new tabs as shown in the image below which includes separate tabs to view the documents found in each of the individual grade sub-sites for this virtual school.

Notice how the tabs/links can actually require more horizontal space than the size of the page. When this occurs, double angle brackets appear at the beginning and end of the row to allow me to horizontally scroll through the tabs. I can also use the down pointing arrow to the right of the Add New Tab button to open a dropdown menu of the available tabs.

If I open the web part properties as described for the Relevant Documents web part, I will see the properties that I can modify for this web part. As before, I may want to change the title for the web part that appears at the top of the web part.

Two additional unique properties to this web part are in the View and URL groups. The View group has a single property that lets me control the number of characters that appear in the tab/link before ellipses replace the balance of the characters. According to the documentation, this feature can be used to control the number of characters used in the label. I believe that in SharePoint 2010, I must allocate 2 characters of this number to the ellipses to determine the actual number of characters displayed. For example, a value of 10 allows for 8 characters plus the ellipses. In SharePoint 2013, this property seems to be ignored in my test site. But that may just be my site. What do you get?

The URL group prompts me for a character string that it will add to the URL provided when I define a new tab to specify exactly what is returned by the web part. The default string: _layouts/MyInfo.aspx uses a predefined view that displays content from the site library that shows documents that I modified or created.

However, it also appears possible to replace this string with others. For example I could enter the string: _layouts/SiteManager.aspx.

This string opens the Site Content and Structure view which displays all the documents in all the libraries for the site.

Note that I can navigate to other sites as well as the current site by using the leftmost panel and then by selecting different views, quickly determine which documents I have checked out, have modified, are pending approval, or are still in draft mode.

In future months, I may examine some of the other lesser used web parts and explore their use.


Lesser Used Web Parts of SharePoint

This week I’m beginning my summer break from BI and returning to SharePoint to look at some of what I like to call the lesser used web parts. Some of these web parts I will discuss over the next several weeks did not exist in the original SharePoint 2007 (Original for me because that is when I started using SharePoint). Some of the web parts might not have even existed in SharePoint 2010, but only appeared in SharePoint 2013. So depending on which version of SharePoint your site is current on, you may or may not see some of the web parts I will describe. However, I will try to tell you whether the web part existing at least in SharePoint 2010 and/or SharePoint 2013. Some might even appear within different categories of web parts because Microsoft chose to regroup some web parts between 2010 and 2013. I will try to let you know that too. With that in mind, let’s begin with a web part that did exist in both SharePoint 2010 and SharePoint 2013:

Relevant Documents Web Part

Often the number of documents in a site becomes overwhelmingly large and finding the documents I worked on can be quite a challenge. This is especially true of collaboration sites. The Relevant Documents web part, which exists in both SharePoint 2010 and SharePoint 2013, helps me find what I want. Furthermore, my site does not need a custom view or custom page to display the documents relevant to each person who has edit rights to the site. This web part automatically detects the currently logged in user and filters the documents returned by that user. I don’t even need to know in which library to search in because this web part searches across all libraries in the current site (but not subsites). That means that it returns not only documents from the document libraries in the site regardless of the library names, but also items from image libraries and page libraries. Let’s see an example.

The Relevant Documents web part, like all web parts, must be hosted within a page. Therefore, I must first either create the page I want to use or navigate to an existing page.

Next I edit the page. Depending on the version of SharePoint, the Edit this page option may either appear in the Site Actions dropdown menu (2010), the Actions icon (2013), or the Edit button (2013).

I then find a place on the page where I want to add the web part and from the Insert tab, click on Web Part in the Parts group as shown in the following image.

SharePoint then displays three boxes across the top of the page beginning with Categories on the left. Select Content Rollup from the Categories list.

I now see the Relevant Documents web part in the Parts box. Select this web part by clicking on it.

Additional information about the selected web part then appears in the About the part box. To add the web part to my page, I simply click the Add button in the bottom right side of this area.

The following figure shows how this dialog looks in SharePoint 2013. However, the changes in SharePoint 2010 are minimal.

After I add the web part to the page, it automatically displays any documents in the current site that I last modified by default. The theory of this default is that documents I recently modified would be the most likely files I would want to return to edit further.

This web part does have some properties that I may want to tweak. To open the web part properties, hover over the web part title until the dropdown arrow appears on the right side of the header.

Select: Edit Web Part from the dropdown menu. I then need to scroll to the right and possibly up to see the properties panel. This dialog consists of several property groups. The first group: Appearance, is open by default. Here I can change the Title property to change the web part’s displayed title.

The other properties I may want to change can be found the Data group as shown below:

Note that there are separate options to let me see all documents that I created, even after someone else modifies it and documents which I may have checked out that others created and modified. The checkbox to include a link to the folder or list allows me to open the library rather than just opening the document. Finally, I can adjust the number of items shown in the list. However, my recommendation is that for most users, a number from 1 to 100 makes the most sense.

In conclusion, I could create a page on my site with the name My Relevant Documents. Then by using this web part, every contributor to my site can go to that one page to see only the documents that they have added or have been working on.

That’s all for this week. Hope you are having a good summer and next week I will continue with a related web part: Site Aggregator.