Using Power BI without an Power BI Account or Active Directory Entries

Believe it or not there is a way to use Power BI without signing up or having an account.  With an embedded application, you can use one Power BI ID and have all users use this account to log in. Now before you judge this is concept as a horrible security idea, keep on reading as you may change your mind. In this embedded application all users to login using a unique id and password and restrict the data seen in Power BI data based upon that id. How do you accomplish this seemingly incongruent task and why would you ever do such a thing? Well it turns out there is a logical reason for wanting to implement a Power BI application this way.

Creating a way to Securely Access Power BI Data for Customers

There are many companies which would like to provide Power BI reports which would allow customers to interactively work with their data, but they don’t want to create Power BI accounts for customers as that can be a lot of work from an administrative standpoint.  For the same reason, these customers are not added to the corporate network which means they are not added Active Directory.  For example, if Desert Isle SQL contracts with Acme Corporation to create a custom conference display, Acme might want to show me a report showing when the components were purchased, when they were modified and when the order is in process and when the order is completed.  How do I show a Power BI report containing information? From an application design perspective data from all of the customers should be stored in the same place and Desert Isle SQL should only see their orders when logging in to Acme’s site.  Here is the workflow that I want to implement.

 

Passing Login information to Power BI

When creating an embedded application, connecting requires a connection string.  It is possible to pass additional information to the connection string buy modifying the gateway to use effective identity and then pass the role information you want to use.  There are two configuration steps you need to complete to make this work.  The Power BI gateway needs to be configured to use CustomData through the Map User name screen. Also SSAS needs to be configured to use Roles as the role will restrict the data that users can access. The CustomData can contain a comma delimited list of values, which can include the data I need to have to access the role.  In the DAC for the role, the CUSTOMDATA field as performs as if it was a table. The DAX in the role would provide permissions based on the value of that table DimTerritory[TerritoryName] = IF(CUSTOMDATA() = “username”.“territory” .  This will restrict the data that a customer can see based on the territory they have assigned.  The Id can then be used to implement Row level security in Power BI with either the embedded data model or with Analysis Services Tabular. By using this method, you have the ability to restrict the data for each user and use one Power BI account all at the same time.

 

Costs for Implementation Multi-User Power BI Systems

Unfortunately, this solution means that you are going to be purchasing the embedded version of Power BI as this functionality is not covered with a Pro License. Embedded applications require that you purchase an embedded license or have a premium account.  The pricing for embedded has changed quite a bit from 2017 when it was introduced.  Pricing is all about capacity, unless you use a Premium account.

Power BI can be implemented in a number of different ways, and this implementation is one that you may see more of in the future.  There are a lot of different things that you can implement Power BI and it is hard to keep up with all of the changes. If you are interested in learning more about some of Advanced Power BI topics, join me in person in Boston for a full day of Advanced Power BI Training on Friday, September 21.  I look forward to meeting you here or anywhere else we might meet up.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

 

Applying Data Science to SQL Server

Data has been getting a lot of attention in the business world for a while now.  First there was big data, which was another way to store data so that later the data could be analyzed.  Recently the talk has been all about analyzing the data with new tools such as R and Python.  The reality is that people who have been working with databases doing work in business intelligence have been analyzing data for a while.  Learning a different toolset for analyzing data is not such a big leap, but an expansion of what they know.  As the field is rapidly expanding now, and demand is huge, now is a great time to learn the tools.

Traditional Data Science Development

Data scientist have created analysis solutions with data for a number of years.  The data is analyzed, cleaned, processed with various algorithms, and results are created.  When the process is complete, code has been created to provide meaning from a portion of the data and is ready to be migrated to production. Traditionally there has been a big gap between creating a solution and implementing the solution to be run against data on a regular basis.  Data Scientists traditionally are not part of the IT organization, they are actuaries or analysts, not the people who have anything to do with system processing. Recently I did some work for a company and after the data scientists were done creating a solution, they turned over all of their code to the Java team.  Six weeks later the code was released into production. This solution made no one happy.  Management thought it took too long.  The data scientist didn’t believe that the code that they created was what was implemented into production, and the java developers were tired of people blaming them for wrong code which required a long time to implement.

SQL Server Implementation of Data Science

Since SQL Server 2016 incorporates R and SQL Server 2017 has added the ability to include Python code into SQL Server, data science solutions can be incorporated as part of a scheduled process with SQL Server.  There is now a dev ops solution for incorporating R and Python into SQL Server.  One way of learning about the technology is through blogs and other online training which can help you get up to speed.  Many times though there is no substitute for hands on learning.  If you are attending PASS Summit 2017, and want to learn not only about data science, but how to incorporate it into SQL Server, I hope you can sign up for my all day training session on Applied Data Science for the SQL Server Professional.  I hope to see you there.

I have recently created a You Tube channel where I plan on sharing more data related content where I have included my first video about this conference.

If you are at PASS Summit, please introduce yourself as I would love to meet people who read my blog personally.

 

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Database Table Design

http://michaeljswart.com/2016/06/t-sql-tuesday-079-its-2016/comment-page-1/#comment-186750There are a number of different ways that you could decide to organize your data in a database. If you are creating a database to be used in a transactional system, your table design should follow a normalized design as much as possible.  Data should be grouped in logical groups, such as customers, products, sales, orders, quotes, tickets etc.  Redundantly repeating data in multiple places will cause problems in the future.  Your design may include hundreds of tables, and that is perfectly fine.

If the purpose of the database is for Power BI or for a data warehouse, dimensional modeling techniques should be deployed.  In this type of database design, generally speaking there are a number of tables containing descriptive data, such as product and customer and few fact tables which contain the actions which happened.  The actions include things like Sales.  The database design will look much like a star with the fact tables in the center and the dimension tables connected to it like satellites.  If you have one dimensional model connected to another dimension, that design is called a snowflake and some applications, like Analysis Services Multi-dimensional will not process it well.  Power BI and Analysis Services Tabular work very well with snowflake dimensions.

SnowflakeModel

Snowflake Data Model from Power BI

 

Table Design Gone Wrong

To paraphrase Ron White, the reason that I described database modeling is so that even people who know nothing about database design could appreciate my interview story. When I was working at a previous location, I assisted in providing technical reviews for database developer jobs.  We asked a number of typical questions about indexing and stored procedures, but I always tried to come up with at least one question which the candidate could not readily answer by cramming interview questions found on the internet.  I decided to ask one candidate, who did correctly answer the previous stock questions something that would let us know what kind of work he had really done.  I asked him “What do you do to determine how to design a table?” I was interested to find out what his thought process was, see if he would mention normal form or describe something he had done in the past.  I was completely surprised by his answer.

“Well, you can only have 256 columns in a table. After that you have to create a new one.”  This answer was a complete surprise.  I was really curious to find out where he had developed this completely warped view of how to determine what fields should go in a table.  It turned out that he learned all of his database skills from a co-worker, who had recently retired.  His co-worker had worked at the same location for a very long time and when he started used mainframes without any databases.  He had migrated some of the applications to databases and they wrote them this way because it “made sense”.   After that the interview was over, and we hired someone else.

I challenge anyone who is learning databases to please look up what people tell you to do on the internet.  This is useful for two reasons, the first being that it will probably help you learn the concept better to read about it another way.   The other reason is that you can find out if the person teaching you really knows what they are doing, so you will learn the correct way to do something.

 

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

 

Power BI – Beyond the Basics

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

 

Data Platform MVP

I am very excited to be able to announce that Microsoft has made me a Data Platform MVP. This is a big thrill. The right words escape me, I will have to make do with these.

If I Only had a Brain

I love this song from the Wizard of Oz.  Unfortunately, the scarecrow never gets a brain, instead he gets a honorary degree.  I wish having an MVP award would make me smarter, but unfortunately, it does not do that.  Frankly it means I am in very intimidating mental company as when I wrote this, there were only 370 Data Platform MVPs. Most likely I need to learn a lot more and maybe write a book so I can keep up.

mvp_horizontal_fullcolor

One thing I do try to do is share what I know by blogging and speaking, if for no other reason than I don’t want to be a hypocrite.  When I was learning SSIS, the person leading the project was tuning SSIS and he would not show me how.  He obfuscated, and made SSIS tuning out to be wizardry. I thought to myself at the time, that he should tell me what he knows as I would do that.  Later I found out the rules, and gave a few talks about SSIS, including one for the PASS Data Warehousing and Business Intelligence Virtual Chapter which was recorded here.  If I learn something, I want to tell other people, which is why I blog and speak.  I think this is the greatest profession in the world and I feel bad for people who have chosen to do something else as the data platform stuff and they are missing out.

Keeping Up

There are a ton of new technology things to learn coming up all the time. I keep up as much as I can and when I do learn something, I tend to blog or speak about it.  If you subscribe to this blog or follow me on twitter, hopefully keeping up will be easier.  I don’t want Microsoft to think that they made a mistake, so I plan on trying to increase the number of blog posts and speak when I am afforded the chance.

SQL Saturday Phoenix

I wanted to make sure to talk about the next place I will be speaking, SQL Saturday Phoenix, the largest data related sqlsat492_Phoenixtechnology event in the state of Arizona.  I know it is going to be a great event thanks to Joe Barth and the rest of us on the organizing committee who have volunteered to make this a great event.  The Arizona SQL Server Users Group was where I learned about the SQL Server Community and was where I started to really get motivated to start learning and I am happy to be a part of it. I hope to see you there.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

2016 Releases of SQL Server and Excel

It’s been 2016 for over a month now, so hopefully you are still not changing 2016your 5 into a 6 still. Chances are you haven’t changed your software to reflect the new year yet. Office 2016 came out in September of 2015 and SQL Server 2016 hasn’t been released yet. It’s hard to keep up with all of the version changes that have come out, especially when you throw in Power BI which is has something new every month. If you know you are going to be upgrading to one or both of these versions, or want to learn more about SQL Server or Excel so you can decide if  is worth the upgrade effort, this week I might be able to help out. I am going to be talking about the 2016 version of SQL Server on Wednesday, February 10 at the Arizona SQL Server User Group meeting and then talk about the 2016 version of Excel on Thursday at the Excel BI SQL Pass Virtual Chapter. If you are not in Arizona right now, you are missing out as we are having Department of Tourism weather of 80 degrees. You can get back to me in August when I am melting in the 115 degree heat.

Polybase in SQL Server 2016

Since there are many new features to talk about in SQL Server 2016, I picked Polybase. As big data matures many places are looking to keep their structured data right where it is and create an HDFS cluster to store other data. Polybase allows SQL Server 2016 users to look at both all in one place.

Excel 2016

It’s been a while since September 22, 2015, the date Office 2016 was released , but I still know very few people who have upgraded. I’ve been to a few clients that hope to upgrade to Excel 2012 this year. In this session, I will show where things got moved and renamed, what’s new and what is on the deprecated list. If you don’t have 2016 installed yet, or if you do and wonder where Power Query went, please join me to hear all about it. Generally speaking, the Virtual Chapters are posted on Youtube, and when they are I will have a link available. Unfortunately for those who attended my last Excel BI Virtual Chapter Meeting, due to technical difficulties that recording is not available, but hopefully this time everything will work.  When the recording is available I will make sure a link it is available on my blog for those who can’t make it.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Lessons Learned About Speaking

As an attendee at PASS Summit, I had the opportunity to learn about a wide variety of topics, including public speaking. I’ll be devoting other blogs to the great technical things I learned, but I thought I would start by talking about the sessions in general. I saw a number of presentations, some which went well, others RimmaNimmeDavidDeWittwhich were beset by technical difficulties. By far the best talk I saw was the keynote with Rimma Nehme and David DeWitt of Microsoft. The presentation was well rehearsed without sounding canned, and the slides were absolutely amazing. You can check out the slides here as they are publically available. I am going to remember what made this talk work, and hope to incorporate what I saw here when I speak next. If you are interested in where that will be, check out my Engagement page as maybe we can meet sometime.

Speaking Techniques

I saw a number of different speaking techniques employed at Speaker Idol. People were really creative. Todd Kleinhans navigated through a game interface. Wes Springbob did an homage to Hitchhiker’s Guide to the Galaxy. By the way, if you haven’t read the series, I think you should as they are great books. I was surprised that all of the judges hadn’t read the books, but even those who didn’t thought he gave a great talk. I demonstrated that I had never used a microphone before, which was not positive. Bill Wolf worked to engage the audience throughout his talk. Ed Watson videotaped his demo. This technique is something that often I have heard that you should do in case your demo crashes, but this was the first time I have seen anyone who actually did record the demo. William Durkin brought great stage presence, which I noticed was a common theme among all of the talks I liked. Effective presenters know their topic so well that the talk should appear effortless and fun, without appearing that a script has been memorized that you are working to run through. Also, remember to have a point to follow during the presentation so I remember what the talk was about midway through. Everyone who did this I thought did a great job.

Speaker Idol Results

The finalist for Speaker Idol were William Durkin b | t , Theresea Iserman t, and David Maxwell b | t. My name was not there, due to my issues with the microphone, which put me off my game. Also despite my goal of not adding useless words, I threw in many “um” and “so” into my talk. In my round, William did the best job, so it was logical that he went forward. I talked to David and Theresa about their respective talks, and I know they put a lot of work and practice making them really good. David was the winner, so I look forward to seeing him at PASS Summit 2016 giving the talk of his choice. As for me, I hope to follow the pattern of fellow Speaker Idol 2014 non-winner Reeves Smith b | t who spoke at PASS Summit for the first time this year the old-fashioned way, by picking a good topic and writing a good abstract for it.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Talking about the latest Power BI Update

On August 20, Microsoft released the latest update to Power BI Desktop. There are some great new features, some of them little but handy like the ability to resize columns in the data view, and others are a bigger deal like the ability to import Power Pivot models. The data modeling feature which has me most intrigued is the ability to move columns from one table to another. That is something which is not a feature found in most other data mashup tools. Moving columns I think provides for a more forgiving environment, which may encourage people new to the topic to play around with it.

When to use Power BI Desktop or Excel

I’ve been writing a lot about Power BI recently and have received some questions on my blog, but I am thinking there may be some more, especially about items in the latest release. There are a lot of interesting tools in Power BI and Excel. Having two tools means there are reasons, based on your unique environments, you may wish to use one or the other. If you have questions about this decision process, or other Power BI questions, I hope that you can make time to ask them on August 31, 10:00 EDT when I’m going to be speaking at the next SQL PASS – Excel Business Intelligence Virtual Chapter meeting, when I will be giving a talk Power Pivot – the Gateway to Power BI. The SQL PASS organization, which I am a member of and I hope you are too, has a number of virtual chapters around all things data, including Excel Business Intelligence, which provide the SQL Community with great information and training resources on a number of different topics. Registration for this talk is free, and I hope you will be able to attend and ask any Power BI related questions you may have.

 

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

DIY Machine Learning – Supervised Learning

When I first heard about supervised learning I had a picture in my head of a kindergarten class with a teacher trying to get the small humans to read. And perhaps that isn’t a bad analogy when talking about Machine Learning in general as it is based on the same principles as school, repetition and trial. After that the analogy falls apart though when you get to the specific criteria needed for Supervised Learning. There are two broad categories for types of machine learning which have the binary descriptions of supervised learning, which fall into the binary categories of Supervised and Unsupervised. This means you only have to know the one set of criteria for supervised learning, to determine which type you need.

Training Data

A problem solved with supervised learning will have a well-defined set of variables for its sample data and a known outcome choice. Unsupervised learning has an undefined set of variables as the task is to find the structure from data where it is not apparent nor is the type of outcome known. An example of Supervised learning would be determining if email was spam or not. You have a set of emails, which you can evaluate by examining a set of training data and you can determine using the elements of the email such as recipient, sender, IP, topic, number of recipient, field masking and other criteria to determine whether or not the email should be placed in the spam folder. Supervised learning is very dependent upon the training data to determine a result, as it uses training data to determine the results. Too much training and your experiment starts to memorize the answers, rather than developing a technique to derive solutions from them.

When Supervised Learning Should be employed in a Machine Learning Experiment

As the field of data science continues to proliferate, more people start are becoming interested in Machine Learning. Having the ability to learn with a free tool like Azure Machine Learning helps too. Like many tools while there are many things you can do, so knowing when you should do something is a big step in the right direction. While unsupervised learning provides a wide canvas for making a decision, creating a successful experiment can take more time as there are so many concepts to explore. If you have a good set of test data and a limited amount of time to come up with an answer, the better solution is to create a supervised learning experiment. The next step in the plan is to figure out what category the problem uses, a topic I plan to explore in depth in a later post.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

Configuring Power BI Data Refresh on a Local SQL Server Database

Recently I have been tasked with setting up and assisting other in setting up Office 365 Power BI Automatic Data Refresh. If you scroll through this post, you’ll see that there are detailed steps required to accomplish a successful Power BI Data Refresh with an on site version of SQL Server. There are a lot of people who are very frustrated trying to configure Automated Data Refresh. Considering what is involved, I understand why. Follow these steps, and Automated Data Refresh for Power BI Office 365 with a SQL Server located locally will work.

TL;DR Steps for Power BI Automated Data Refresh

This is a long document because I wanted to make sure that all confusion regarding how to make this work took a lot of words. If you don’t want to read them all, here are the steps needed to complete a successful data refresh. I have a feeling this level of detail may not be enough, some people may figure it out after reading this far.

  • Install the Data Management Gateway application on your SQL Server box.
  • Add a gateway Power BI Admin Center and configure the Data Management Gateway.
  • Create a connection string and a Power BI workbook which uses the connection string
  • Create a Data Source in Power BI Admin Center using the connection string created in the previous step
  • Schedule Data Refresh using the Power BI Workbook using a matching connection string.

 

Power BI Office 365 Automated Data Refresh Steps

Here in detail is how I can get Automated Data Refresh to work every time. If you have figured out another way to make it work not using these steps for an on-premise SQL Server data refresh, please let me know how you did it. I know these steps work every time, but I am hoping that there is an easier way.
 

  1. Install the Microsoft Data Management Gateway on your SQL Server Database Server. Here is more information on the Data Management Gateway.Technically, you don’t have to have it installed here as Microsoft says it is supported installed in other places. If you want to make sure it does work, you will install it on your database server, which must have internet access available on the server. Here is the link for the software, and here are the instructions from Microsoft on installing it.

 

  1. The next step is to configure the gateway for the Power BI site and for your local server. Go to your Power BI for Office 365 site, and navigate to the Power BI Admin Center. On the Left Menu, you will see an option for Gateway. Click on the plus and add a gateway.  You will generate a key as part of this process and you will need to copy this key and provide the key to the application installed on the database server. If you have more than one database server, you will need more than one key. Here’s a link to the instructions for creating a gateway in the Power BI Admin Center which provide more detail. When you are done, look on Power BI Admin screen your gateway name will have this Running next to the gateway name. If you don’t see the green check, wait for about 10 minutes to see if the status changes.If the status does not show as Running, there are two things you might want to do: Review the instructions or Look on the Office 365 Admin Center. The Office 365 Admin Center can be accessed via the grid button on the top left hand side of the screen under the Admin icon. Select menu option on the left hand side of the screen for Service Health, open it and select service health. I have included a copy of the screen here, which shows that there are no issues today. If you are experiencing issues, this screen may not reflect a problem until sometime later, so continue to review this if the steps just don’t work for some reason.Office365Admin
    1. Create a local data connection within Excel to be used to create a Power BI data source. Start a new worksheet in Excel. Click on the data tab (not the Power Pivot tab) and click on the left most icon on the ribbon Get External Data->From Other Sources->From SQL Server. Enter the server name. Either Windows Authentication or SQL Server Authentication work, so pick either one and enter the Id and password. Click on the Next Select the database and then the tables you want. There is no need to select more than one table, as you can always go back and add tables once this is complete, and it will save loading time. Click on the Next button.
      DataConnectionWizardThis is the most important screen of the entire process. The Friendly Name listed here will be used on the Power BI site to create a Data Source. The Friendly Name must exactly match the name of the Data Source in Power BI, which may mean that you will want to edit the name from the default listed here. Do not check always attempt to use this file to refresh data. After you are satisfied with the Friendly Name click on the Browse… button.In a later step, the connection string listed in this file will be used to create the connection string in Power BI. As we will be opening the file listed here in Notepad in a later step, it is important to remember this file path.DataConnectionWizardFileSaveClick on the Save button in the File Save window.Click on the Finish button in the Data Connection Wizard Menu.ImportData

      Generally speaking, you will want your database information to be loaded to a Pivot Table Report, so click on the OK button. If you do this, you have the added benefit of having a report to test. When your pivot table is completed, it might be a good idea to now go to the Power Pivot tab and take a look at the model. Feel free to modify it as much as you like, but it is not necessary to do so to test the data refresh. If you need to add additional tables, make sure that you do so by using the existing data connection created earlier. Save and close your new excel workbook.

     

    1. Using the information from the previous step, we can now create a data source in Power BI. Go back to Power BI Admin Center on the Web, and from the left hand menu select Data Sources. On that screen, click on the +(plus sign) to add a new data source, and select SQL Server. Enable Cloud Access must be completed. If you want to make items from the database searchable from Power Query, you will need to check Searchable in Power Query and Enable Odata Feed. If you select Enable Odata Feed you will be prompted later to select a series of tables to expose for viewing Click next to get to the connection info screen.PowerBIConnectionInfoCompleting the connection info screen correctly will allow you to do a data refresh. The information needed to complete the screen can be found in the *.odc file you created earlier. In notepad, open up the *.odc file using path you remembered from the previous step. Look for the text <meta name=Catalog content= The name listed on the other side of the equal sign MUST be the name you list in the name in the Power BI Connection Info Name. Put a description in if you wish. The dropdown box for the Gateway will list the gateway you created in the previous step as well as any others you have created previously. Select the appropriate gateway.By default the Connection Properties option will be selected.ConnectUsing Unfortunately if you use this option you will never be able to perform an Automatic Data Refresh in Power BI. Select connection String. When you do, the connection provider will change to .NET Framework Data Provider OLE DB. This is supposed to happen. For the connection String information, go back to your *.odc file and look for the text <odc:ConnectionString> Copy everything between that text and this </odc:ConnectionString> into the connection string window. Wait until the set credentials button turns blue. If it does not turn blue, there is something wrong with the Power BI and you will want to look at the Office 365 Admin Service Health site referenced earlier.Click on the Set Credentials button. A little window will show up indicating it is loading. A pop up window will appear prompting you to add your user id and password. Make sure you test the connection before clicking on the OK button.

      The last step here is to click the Save button.

     

    1. Upon the successful completion of the previous steps, Scheduling Data Refresh can now be configured. Upload the excel workbook created earlier in step 3. Once it is loaded, click on … (the ellipse) and select Scheduled Data Refresh. When the AddToFeaturedReportsscreen loads, the first thing you will need to do is click on the button at the top of the screen to turn the feature on as it defaults to off. All of the connection information included inside your Power BI report will be listed. The connection name(s) listed in the report should exactly match the connection(s) in the Power BI Admin Data Connections. If there is not a match, you will not be able to Schedule an automatic data refresh. It is not possible to change the names from this screen, as it lists the connections within the Power BI document. You may need to fix the connections within your Power BI worksheet. If the connections are not valid, you will see this status iconStatusIcon and a message about this status.
      The Configure Refresh Schedule is default selected to a daily frequency.   Select the time and the zone for the data refresh. Unfortunately, you can only schedule refreshes for 3 months, so the schedule will need to be updated every 3 months as there is no way to make it continue in perpetuity. Send Notifications is defaulted to the email address of the admin. Note you will be notified of errors, not of completions of the data refresh. Click on the Save and Refresh button to test the data refresh process, which refreshes the report immediately. Once the data refresh schedule is completed, the top of the screen will have two menu items History and Settings, which you can review at any time.

     

    Data Refresh Conclusion

    If you think that there should be a better way, I would love to hear about it. Looking online, there are a lot of people struggling with this topic. If in any way you find it helpful, or you want to tell me there is an easier way, I would love to hear about it. I look forward to hearing what you have to say about this topic.

    Yours Always

    Ginger Grant

    Data aficionado et SQL Raconteur