Articles

Jul 02 2017

Power BI – Beyond the Basics

Author: Ginger Grant • Discussion: 2 Comments

When helping clients recently with their Power BI implementations, I have noticed that when talking to people about Power BI there seems to be some areas where there continues to be a log of questions.  While it is easy to find a plethora of information about getting started with Power BI, when it comes to implementing a solution, the information is scarce.  How do you handle releases? Should an implementation contain only one data model? Is Power BI’s data secured on the cloud? Is it required to have Office 365 use Power BI? Do you have to have Power BI Premier to have the Power BI run locally?

Advanced Power BI Techniques in Norway

While I have discussed some best practice techniques on my blog, as usual new features released in Power BI have a

Norway Parliament Building in Oslo

Norway Parliament Building in Oslo

tendency to change some of the available options.  For example, App workspaces, the updated take on Content Packs released a few months ago, now offer a new method for releasing not only dashboards but the reports behind them and the ability to easily migrate sources. I am excited that I will have the opportunity to discuss the answers to the questions received by doing a full day of training at SQL Saturday Oslo. I am looking forward to visiting Oslo, which is home to the best preserved Viking Ship, an Opera House designed to be walked on and the home of the guy who painted the Scream.  If you happen to reside somewhere where it is possible to make the journey to Norway, please register to attend this full day of interactive training.  We will cover all of these items and go into detail about Power BI administration, security and new features and design techniques which will improve Power BI implementation techniques.

sqlsat667_osloFor those of you who are unable to attend, I feel obliged to answer some of the questions I posed earlier.  Implementations generally require more than one data model.  Power BI is encrypted both in transit and at rest. You do not need to have Office 365 to run Power BI.  Power BI can be run locally with Power BI Report Server, which is part of SQL Server 2016 Enterprise with Software Assurance, and you do not need to sign up with Power BI Premier to install it.

I hope to see you in Norway.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

Jan 24 2016

DIY Guide to Content Packs

Author: Ginger Grant • Discussion: 1 Comment
SQL Saturday Austin - 461 2016

As I was rehearsing my Using Power BI when Implementing Data Analytics Management Practices presentation for SQL Saturday Austin, I realized that I wasn’t going to have time to cover everything I wanted to cover. One of the important methods for implementing data management practices in Power BI is using content packs. Content packs are a method of sharing reports and data throughout your organization so everyone doesn’t need to create data model and those people who do can share them with everyone else. For this reason I highly recommend using content packs in Power BI. This guide will walk you through the steps needed to create content packs. If you don’t have Power BI Pro, you can stop reading now, as content packs are a feature only available in the Power BI Pro version. There are a number of steps which will need to be completed to use a content pack within Power BI, and I’ve listed them all in order below. Depending on where you are in the process you might want to skip to the Creating a Content Pack section, but I thought it was important to include everything that should be completed first.

Create a Data Model in Power BI Desktop

For this example, you will need to create a data model in Power BI Desktop, and for this demo, create one report too. I’m not going to review how to do that here, but Microsoft has a video guide to creating Power BI Desktop models here. Save the .pbix file. After saving the file, go to PowerBI.com and login.

Recommendation: Use a Group Workspace

The next step is optional, but if you work with other people, I recommend it. If you don’t have one already, I highly recommend that you create a group workspace. That way the ownership is shared, meaning that if you take a day off, someone else has the ability to access the information. Click on the My Workspace item on the left hand side of the Power BI page. Click on the + (plus) adjacent to the Group Workspaces item. On the right hand side of the screen a form will pop up with fields for Group Name, Privacy, and Group Members. Make sure that you complete the form and save it. Double click on the new group workspace item to open it. If you happen to have an Office 365 Exchange license, creating a group workspace will also create a Group One Drive. This is a great place to put data so that you can all share it and see the file from within Power BI. Now that I have this one drive location created, I am going to copy my newly created Power BI file to it.

Using a Desktop file on Power BI.com

To use the Power Desktop file within PowerBI.com, the next step is to upload the Power BI Desktop file to the web as a dataset. Either clicking on the + (plus) button next to the words Datasets, or click on the Get Data button on the bottom of the screen. Both options will get you to the Get Data screen. We want to Import the Power BI Desktop file, so click on the get button in the Files box. The screen will change to the file location section. Select Local file and upload the Power BI file.

Data Refresh

PersonalGatewayPowerBIConfigurationScreenEnsuring that the data set refreshed, which allows everyone to have current data, requires updating the data with a gateway. For this example I am going to use the Power BI Personal Gateway because I plan to include multiple data sources instead of just SQL Server  and Power BI web application to schedule the data refresh. Assuming I have already installed the Power BI Gateway, Click on the … (ellipse) next to the Power BI Desktop file just loaded to the data set, and a box will pop up with a list of features on the bottom of the popup box. Select Schedule Refresh, which will bring up the screen shown.

Assuming the Personal Gateway is online and the Data Source Credentials are ok, change the Schedule Refresh from the default Off to On. Set the Refresh Frequency to one of the available options. If you want to update the data more than once a day, click on the option Add another time. When you have finished adding times, click on the Apply button to save the contents.

Creating a Content Pack

To create a content pack, ideally you want to share a data model which has working appropriately scheduled updates. That way anyone who wants to create a report doesn’t have to worry about having valid working data. Instead they can work on providing meaningful visualizations to business problems.

If you want to create a content pack or use one, the step is the same. Click on the yellow Get Data button on the bottom left corner of the screen. That will change the active window to the Get Data window. On the left hand side of the screen under the words Content Pack Library, there are two options. Click on the Get button from the one on the left, My Organization. Click on the button labeled Create Content Pack. The following screen will be displayed.CreateContentPacks

There are a number of options on the Create content pack screen, starting with the Choose who will have access to this content Pack button. I have selected the option My entire organization. You may want to create different content packs for different groups of users. If you have exchange groups set up, such as Accounting@desertislesql.com which would send an email to everyone listed in the email group, you can enter that email group. If you just want to add a list of emails for people within your organization, you can do that as well.

In the sample Create Content Pack screen shown, I have filled in the blanks, selected my Power BI Desktop file I just added and uploaded a company logo. Once you click on the Publish button, the screen will close and you will get a success window which briefly appears on the top of the screen. The content pack is now ready to use. Click on the Get Data button again, and the new content pack is available to use. When I select the newly created content pack AWDW, I am provided a new window with a big Connect button in it. Click on the Connect button. The data set and any reports connected to it will have yellow stars next to them.

Every one who uses this new data set can be guaranteed a data set which updated on the same schedule, and different people can now create visualizations with one shared dat aset which can be used many different times.

Data Management within an Organization

Having helped a number of organizations implement Power BI, one of the big issues I have seen are not related to the product but related to the processes within the organization which are used to support the data needs of a variety of different users. These processes tend to be the reason a self-service business intelligent process is successful or not. Using Content packs can be a part of that solution which is why I look forward to sharing what I’ve learned at SQL Saturday Austin – 461. I hope to see you Deep in the Heart of Texas!

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Dec 31 2015

2015: Year End Wrap up for Releases and More

Author: Ginger Grant • Discussion: No Comments

As 2015 draws to a close, I started thinking back about everything that has happened this year. 2015 GraphicTechnically this has been a big year as a many new applications were released. Here are just some of them, with links included to provide more detail.

This short list could be a lot longer as it doesn’t count the number of updates released to Power BI, which occur several times a month, the CTP releases for SQL Server 2016, the new web version of BIML, or PowerShell. It’s really hard to keep up with everything that is changing. It’s a good thing that so many people are willing to help others learn how through speaking and blogs which make learning new things easier.

Community Involvement in 2015

Keeping up with all of these events is difficult, especially given the pace of releases.  I spend a lot of time reading various blogs, watching videos and going to hear people speak. I also have been able to talk about topics of particular interest, many Power BI and Machine Learning. This year I spoke a different times at a number of different events including: Speaker Idol, two different user groups, seven webinars, five SQL Saturdays and other Tech Events. I’ve got a number of engagements on the books for next year, including PASS BA Con and SQL Saturday #461 – Austin. 2016 is shaping up to be busy too and hopefully our paths will cross.  I list all of my speaking events on my Engagement Page and I hope that you might take a look at it from time to time if you are interested in catching up in person sometime. Next year I am hoping my list of speaking engagements changes somewhat as I plan on trying harder to get accepted to speak at events where I submitted and was turned down in 2015. On a more positive note, views of my blog are up 1000%, and the number of website subscribers has more than doubled. Thank you very much for continuing to read this site and I hope you find my thoughts helpful. I posted once a week this year, which I thought was pretty good until I talked to Ken Fischer b | t who blogs twice a week. I’ll have to try harder next year. If you think of a topic you think would make a good blog post, let me know as I am always interested in feedback.

Keeping Up the Pace in 2016

Next year there will be no slowdown in the things to learn as SQL Server 2016 is going to be released. Although the exact date has not been announced, my sources tell me to look for it around May-June. The next release of SQL Server is going to be huge as it will include new tools Microsoft added to integrate Big Data and open source platforms to SQL Server. PolyBase, JSON and R are all going to be part of with SQL Server. Personally, I find the R integration most Datazen and SSRS are going to be integrated in the next release too which should really increase the implementation of mobile reporting visualizations.

 

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Mar 29 2015

Complex Data Analysis and Azure Machine Learning Presentation Wrap Up

Author: Ginger Grant • Discussion: No Comments

Thank you for all of the people who signed up for my webinar on Data Analysis with Azure Machine Learning [ML]. I hope after watching it that you find reasons to agree that the most important thing you need to know to get started in Machine Learning is not Math, but having good knowledge of the data you want to analyze. There’s no reason not to investigate as Azure Machine Learning is free.  In order to take more time with the questions after the presentation than the webinar format allowed,  I am posting my answers here, where I am able to answer them in greater detail.

How would one choose a subset of data to “train” the model? For example, would I choose a random 1000 rows from my data set?

It is important to select a subset of data which is representative of the data which wish to evaluate. Sometime a random 1000 rows will do that, and other times you will need to use other criteria, like transactions throughout a given date range to be a better representative sample. It all comes down to knowing your data well enough to know that the data used for testing is similar to what you will be ultimately using for analysis.

Do you have to rerun or does it save results?

The process of creating an experiment requires that for each run you need to re-run the data as it does not save results.

Does Azure ML use the same logic as data mining?

In a word, no. If you look at the algorithms used for data mining you will see they overlap with some of the models available in Azure ML. Azure ML provides a richer set of models, plus a greater ability to either call models created by others or write custom models.

How much does Azure ML cost?

There is no cost for Azure ML. You can sign up and use it for free.  Click here for more information on Azure ML.

If I am using Data Factory, can I use Azure ML ?

Data Factory added the ability to call Azure ML in December, providing another place to incorporate Azure ML analytics. When an Azure experiment is complete, it is published as a web service so that the experiment can be called by any program which chooses to call it. Using the Azure ML experiments from directly within Data Factory decreases the need to write custom code, while allowing the logic to be incorporated into routine data collection processes.

http://azure.microsoft.com/blog/2014/12/16/azure-data-factory-updates-integration-with-azure-machine-learning-2/

If you have more questions about Azure ML or would like to see me present on the topic live and live in Southern California, I hope you can attend SQL Saturday #389 – Huntington Beach where I will be presenting on Azure ML and Top ten SSIS tips. I hope to see you there.

 

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

Mar 11 2015

Math and Machine Learning

Author: Ginger Grant • Discussion: No Comments

MLModelsI had an interesting conversation with someone at SQL Saturday Phoenix, an event that I am happy I was able to attend, regarding knowing math and getting started in Machine Learning. As someone who had majored in Math in college, he was sure that you had to know a lot of math to do Machine Learning. While I know that having really good math skills can always be helpful when creating statistical models based on probability, a big part of Machine Learning, I do not believe that you need to know a lot of math to do Azure Machine Learning [ML].

Azure Machine Learning and Throwing Spaghetti Against the Wall

For those of you who cook, you may have heard of an old school way of testing to see if the spaghetti is done. You throw the spaghetti against the wall and if it sticks, the pasta is done. If it falls right off, keep the spaghetti in the pot for a while longer. Testing machine learning models is similar, but instead of throwing the computer against the wall, you keep on testing using the large number of models available in Azure ML. Once you have determined the classification of your data, there are a number of different models for the classification which you can try without knowing all of the statistical formulas behind each model. I have listed all of the models from Azure ML here so that you can take a look at the large number of models available. By taking a representative sample of your data, and testing all of the related models, determining which one will provide a result is not terribly difficult. The reason it is not very hard is you do not have to understand the underlying math needed to run the model. Instead you need to learn how to read a ROC curve, which I included in my last blog post. While you can pick the appropriate model by having a deep understanding of the formula behind each model, you can achieve similar results by running all of the models and selecting the model based on the data.

Advanced Statistical Analysis and Azure ML

While Azure ML contains a lot of good tools to get started if you do not have a data scientist background, which recruiters lament not enough people do, why would you use Azure ML if you have coded a bunch of R Modules already to analyze your data? Because you can use Azure ML to call those modules as well and provides a framework to raise visibility and share those modules with people within your organization or the world, if you prefer.

How to Pick the Right Model

I am going to demonstrate how to pick the right model in an upcoming webinar, which is probably easier to explain in that fashion rather than in a blog post. If you want to see how to determine which model to use and not know a lot of Math, I hope you take the time to attend. Azure ML offers the ability to integrate analysis into your data environment without having to be a data scientist, while providing advanced features to accommodate those really good at math, which I will be talking about in an upcoming Preconvention event for SQL Saturday in Huntington Beach. If you happen to be in Southern California on April 10th I hope you will be able to attend that event.

 

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

Feb 24 2015

Getting Started with Machine Learning – Result Analysis

Author: Ginger Grant • Discussion: No Comments

Recently I’ve started working with Azure Machine Learning and looking at what I consider the most challenging part, picking the right analysis. For those people who haven’t ventured into Azure Machine Learning, it looks a lot like a data flow in SSIS. After that you need to train or more to the point evaluate which model works best. The answer to that question takes a while. What kind of data do you have? Are you looking to find errors? Determine whether data classified in a certain way can predict a result? Perform a regression analysis of data over time? Group data together to identify trends?

Is your Model better than a Monkey throwing Darts?

While you can analyze your variables and rank them to determine the chance that the variables indicate a result, there is another method that is also used to determine an outcome, the coin toss. This lowly method of analysis is right half the time. If you have more than two outcomes, or to speak the language of Machine Learning, the outcome is not binary, there is another method used to determine the accuracy of predictions, monkeys. I have read about the various skills of monkeys in both literature and financial analysis. Think about it for a minute and you may remember reading or hearing about monkeys typing on a keyboard who have been able to write Shakespeare, or a blog post.  This is known as the Infinite monkey theorem. Another thing monkeys have been known to do is throw darts. Various financial publications have been measuring the success of mutual funds to monkeys throwing darts at stocks since the last century. The goal is of course to create a model that has the better success as a monkey or a quarter. The question is how?

Probability of Picking the Right Model

ROC [Receiver Operating Characteristic] Curves are used to ensure the machine learning model generated is better than a monkey throwing darts. Your goal is a perfect game of golf. Chances are your ROC curve will be somewhere between the two. In looking at the ROC curve generated here, you can see 3 lines, a light grey, a red and a blue one.

RocCurve

The diagonal line represents a coin toss. If you were able to get one of your scored datasets to be a 1, meaning that you got a true positive rate every time, you would have played a perfect game of golf. Chances are you will have two lines like I do here and one, in this example the blue line, has a higher number of true positive rates than does the red line, so the results generated by that model are more accurate.

More ML More of the Time

I find myself spending more time with Azure ML, meaning that I will be devoting a lot more future blog posts on this topic. I am also speaking on Azure ML both as part of a Pre-Convention Event on the Modern Data Warehouse and at SQL Saturday in Phoenix. If you happen to be in Phoenix, I would love to meet you. SQL Saturdays are great learning events and I am happy that I was selected to participate in this one.

 

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Feb 03 2015

Tips on SSIS at SQL Saturday Albuquerque

Author: Ginger Grant • Discussion: 1 Comment

sqlsat358_ABQOn February 7, I was fortunate enough to be selected to speak at SQL Saturday in Albuquerque, New Mexico on Top 10 SSIS Tuning Tricks. Having worked with SSIS for a number of years, I’ve needed to research what was the best methods to employ to ensure my SSIS ETL was running optimally. I’ve compiled the most valuable items, with examples of course, into this presentation. I’m assuming that everyone attending already has been using SSIS for a while, so I will skip straight into more in-depth ways of tuning SSIS. One of the questions that I know I have heard most often is “When should I do X in SQL or SSIS?” If you are able to attend this session, you will have the answer to that question.

I really enjoy the opportunity to speak on data related topics and meeting people who may have come upon my blog in the past. Having spoken at this event last year, I know what a good job Keith, Chris and Meredith and friends do organizing this event. I want to take the time to say thank you for all of your hard work as I really appreciate it. These events are a great place to learn and keep up with a lot of the changes going on in the industry. I anticipate there will be many lively discussions both before and after the event. That reminds me. If you get a chance, on Friday there are two great precons scheduled on Friday, February 6th , Powershell Basics with Mike Fal and Query Tuning, Troubleshooting and Execution Plans with Jason Kassay. Having been fortunate enough to meet both of them, I know that they are both extremely knowledgeable in their respective topics, and if you are in Albuquerque I encourage you to sign up for either of them as I am sure both will be excellent.

I hope that you will be able to attend as I know I will enjoy seeing you there.

 

Yours Always
Ginger Grant
Data aficionado et SQL Raconteur

Dec 28 2014

Upcoming and Up and Coming Topics

Author: Ginger Grant • Discussion: No Comments

It’s funny the different meanings words have when you put them in different order, a point which anyone who has imitated the dialectic of Yoda can tell you. I find words fascinating as they are not static but have meanings which change over time. For example the Iron Maiden meant something totally different before there were electric guitars. Thinking of works and things changing, as one year closes and another year begins, I start to evaluate past and future topics. Earlier this year, I held an informal poll on twitter to find out how long people tend to talk on the same topic. The answers were quite varied. Some people keep on talking about the same topic as long as there seems to be interest in hearing about it. That way you can get to be a really good speaker on that topic. Another feels obligated to create a new topic each time out to provide him a challenge. The answer that personally I related to, was keep on talking about the topic until you are tired of hearing about it, which takes about a year.

SQL Saturday Albuquerque

sqlsat358_ABQMy first upcoming engagement for 2015 will be as SQL Server Albuquerque where I will be talking about SSIS. I generally talk about things I am interested in or presently working on, and having working on a lot of ETL recently, I thought that it would be an interesting topic which I think most people would find helpful. As a consultant, I see a lot of code and wonder why parts of it were written that way. One big reason is someone thought the design was a good one. Since that is an objective decision, I thought it might be helpful to clarify design decisions with facts so that that people would be able to employ good logic for their design decisions.

Technology changes and their Impact on Data Development

Another topic which really interests me is the changes that new technologies are having on the database world. With the increased implementation of Hadoop and cloud things are really changing in the way data is being both stored and used. Predictive Analytics, Machine Learning, Cloud implementations, Interactive Data visualizations are changing what people are expecting from the way their data is stored and used. Expectations for data professionals are increasing as the business is looking away from HIPPO and towards the knowledge that they have gathered or integrated data from public sources.

Modern Data Warehouse

I have the pleasure of assisting in a day-long session to talk about Architecting the Modern Data Warehouse . During this one day session we will be showing how to use new technology such as HD Insight and Machine Learning to implement a modern data warehouse. Instead of just talking about new technologies we will be putting them to use to show how they can be used today. I’m really looking forward to it.

If you are able to attend any of these or any upcoming sessions, please stop by and introduce yourself as I would love to meet readers of my blog in person.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

Dec 23 2014

When the Process matters nearly as much as Results

Author: Ginger Grant • Discussion: No Comments

There are times when the results aren’t nearly as important as the process of doing as people learn things from figuring out what not to do. This applies if you are troubleshooting why your project isn’t working right or when your database crashes in the middle of the night. Knowing what not to waste your time with is the key to getting things back up and running. It’s the knowledge that you have gained doing the little things that add up to being the person who can fix things quickly. Often times, the little things add up to something big. It appears this has happened to me.

Finalist in Tribal Awards

I was most honored to be included in SQLServerCentral/Simple Talk Tribal Awards Finalist in the Best New Community Voice category. To be recognized among all the other people who speak and write about SQL Server is quite an honor. The only way that this could have happened is a myriad of people that I’ve met while speaking at various community events and reading this blog remembered my name when looking at a blank line on the Tribal Award. I was so surprised when I saw that my name was listed on the award I nearly fell out of my chair. To everyone who thought enough of me to enter my name in the Best New Community Voice thank you. I really appreciate it.

Nomination and Winning

Regardless of the outcome, especially given the other nominees in the category, I feel that I already won.  Being a finalist is a real honor. After all there are so many other people who are also really involved in the community. I’ve been fortunate to visit with many of you who I’ve met either online or at various events. Thank you so much for to the people who thought of me, as I really appreciate your kind thoughts.  This process mattered as much as the result, what ever it turns out to be.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

Sep 16 2014

Power Map – No longer only BI

Author: Ginger Grant • Discussion: No Comments

Microsoft did something interesting with the licensing of Power Map, they changed their mind. Power Map was original released as part of Power BI a preview tool. Shortly after Power BI went live on February 10, Microsoft made this announcement. “On May 30, 2014 if you have the Power Map Preview installed, it will no longer work in any non-Office 365 subscription version of Excel.” You won’t see that text on their site, but I had it on a slide for a previous presentation I did on Power BI.  Time to update the slide as things have changed since SQL Saturday Detroit, and I have updated my presentation for Power BI SQL Server Saturday Denver.

Do you have a License for That?

The Preview for PowerMap was originally available for Excel 2013 and Excel 2013 for Office 365. Microsoft’s message that came out after PowerBI was officially released is translatable to unless you have Office 265 and pay for a Power BI license, no Power Map for you. Well, that folded like a bad poker hand. If you look now at the Power Map website it says this (I’ll quote it here to make sure I have the text if it changes again)

“If you have any subscription for Microsoft Office 365, you have access to Power Map for Excel as part of the self-service business intelligence tools.” Yea!  But what if you have Excel 2013 on prem? You can use the preview forever. What does that mean? No new features but Power Map won’t stop working. Here’s what the website says exactly “Although feature and performance enhancements for Power Map will continue to be released for the Office 365 subscription plans, there are currently no future plans to update the downloadable Power Map Preview.” Now you don’t have to have purchased the separate license for Power BI to get Power Maps. You can be all Powerful with Office 365 or Office 2013. (Note to Microsoft:Does everything have to have Power in Excel? I am starting to channel the Wizard of OZ)

New Power Map Features

On September 9th, 2014, Microsoft just released some new features for Power Map which they do about once a month. One of the cool new features is the ability to add your own maps for things like the inside of buildings. If you want that to work, you might need to check your configuration. I’ve included a copy of my Com Add-In Screen. If you can’t remember how to pull this up, check my previous post https://www.desertislesql.com/wordpress1/?p=178

PowerMapAddIn

You will notice there are two checkboxes here for Power Map on the COM Add-In Window because I was using the preview and Office 365 automatically gave me the non-Preview version of Power Map when I got an automatic Office 365 update. To get the cool new stuff, I must have the Microsoft Power Map for Excel checked, not the Preview one. If you didn’t pay for Power BI with Office 365, you won’t be able to use the new features Microsoft adds in every month.

Now if you have Excel 2013 or Office 365 and didn’t download the preview yet, you still can right here. http://www.microsoft.com/en-us/download/details.aspx?id=38395. Microsoft now calls the Power Map Preview an Unsupported Add-In, but all of the original features still work.

Mapping in Excel

If you have Excel 2013 or Office 365 and you don’t have Power Map Preview installed, you can still use mapping tools for Excel reports. How is this possible? By inserting maps into Power View.  The maps inside of Power View are very interactive, and unlike Power Maps, you can encode these reports as HTML 5 and display them on your phone or tablet.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur