Books, Conferences, Microsoft Technologies, Power BI

Let Her Finish: Voices from the Data Platform

This year I had the pleasure of contributing a chapter to a book along with some very special and talented people. That book has now been released and is available on Amazon!  Both a digital and print version are available. My chapter is about data viz in Power BI, combining platform agnostic concepts with practical applications in Power BI.

The other chapters are:

  • Azure Data Catalog by Melody Zacharias (b|t)
  • Biml for Beginners: Script and Automate SQL Server Integration Services (SSIS) Development by Cathrine Wilhelmsen (b|t)
  • Care and Feeding of a SQL Server by Jen McCown (b|t)
  • Indexing for Beginners by Kathi Kellenberger (b|t)
  • Creating a Disaster Recovery Plan by Rie Irish (b|t)
  • Using Extended Events to figure out why an application is slow by Mindy Curnutt (b|t)

Special thanks to Rie and Melody for putting this together and for being awesome and inspiring.

We may all be women authors, but this book isn’t about WIT issues. We each got to write about an area of the data platform in which we have expertise.

If you’d like to support us, or are just curious what we have to say, you can pick up a copy on Amazon or purchase a copy at PASS Summit. If you are going to be at PASS Summit, you can also stop by the WIT Happy Hour/book release party on Oct 31 or the panel session on Nov 2.

For more information, check out the website (kindly provided by SentryOne, who sponsored the book).

It’s tough to write a book chapter about a technology that changes every month, but I think the content holds up fairly well a few months after writing it. A few things changed on me (e.g., the Office Store for custom visuals is now App Source, and drillthrough actions exist now), but my approach to data viz in Power BI is still relevant and in use today with several clients. I hope you’ll give it a read and let me know what you think.

 

Microsoft Technologies, Power BI, SSAS

The Tabular Model Documenter is now a Power BI Template

A while back I created the Tabular Model Documenter Power BI model that can connect to your SSAS Tabular or Power BI model and display metadata about the model to help you see relationships, calculations, source queries, and more.  I had been meaning to turn it into a parameterized template since templates became available and just finally got around to it.

You can now download the PBIT file here. Note: This works for SSAS 2016/compatibility level 1200, but may need some adjustments for Azure AS and SSAS 2017.

When you open it, you’ll need to fill in the instance and database name, then allow some native queries to run. Then you will have your tabular model documented for you without a lot of manual effort.

tabular-doc-relationships

For more info on how to use the Tabular Model Documenter, see the original post.

Microsoft Technologies, Power BI

My Thoughts After Completing a Power BI Report Server POC

Last month I worked on a proof of concept testing Power BI Report Server for self-service BI. The client determined Power BI Report Server would work for them and considered the POC to be successful.

Here are the highlights and lessons learned during the project, in which we used the June 2017 version of Power BI Report Server.

Power BI Desktop

There is a separate version of Power BI Desktop for use with Power BI Report Server. You can tell that you have the correct version by checking that the month and year are shown in the title bar.

If you are using both PowerBI.com and Power BI Report Server, this means you will have to manage two versions of PBI Desktop.

Another confusing aspect is that the June release of Power BI Report Server only allows a live connection to SSAS, but the Get Data Button is still there in Desktop with all the data sources listed. If you happened to get data from a non-SSAS source, you might have made it through the development of the model and report. Only when you tried to save the report to the report server would you find out that you had used incompatible features. This is only a temporary issue and will be resolved in the next release, since it will allow for Power BI models that connect to a wide array of sources.

Another difference between the Cloud and Report Server versions of PBI Desktop is that you do not publish to Power BI Report Server. You Save As. The Publish button is gone from the ribbon but still present in the File menu. But what you want is Save As – Power BI Report Server.

PBIRSDesktopSaveAs

Limitation of Live SSAS Connection Data Source

In addition to managing to versions of Power BI Desktop, I also found myself mentally managing two sets of features. I was constantly asking myself “Can I do that in Power BI Report Server?”. Some of that is because PBI Desktop for Report Server is on a quarterly release cycle rather than monthly, so I had to remember if a feature I wanted to use was new (or in preview) and therefore not available in this version. The other part is trying to remember what you can and cannot do with a Live Connection. For example, you can make report measures, but you can’t use ad hoc grouping and binning.

We had several scenarios where users wanted to be able to group fields in multiple ways that changed somewhat frequently. Since we couldn’t use grouping and binning in Power BI Desktop to accomplish this, we set up an Excel data source in the SSAS Tabular model, and allowed users to change the groups there and refresh the Tabular model when finished. This could get rather unwieldy if you had lots of users who needed this kind of flexibility.

While there are downsides like the above with using a centralized data source, there are some upsides as well. Having a centralized data source can help ensure calculations are tested and approved before being used in a report. It also allows the BI team to ensure that security is properly implemented in the model. This is reassuring for organizations that are just starting their self-service BI program. But it also places more burden on the BI team since they must be very responsive with new calculations and data sets. It’s also necessary for the BI team to publish a data dictionary and tabular model documentation (relationships, calculations) up front so users can understand how to use the data in the tabular model. Our users were excited to get started and immediately had questions as to how calculations were implemented.

Content Management and Sharing

When we were ready to migrate to production, we wanted to avoid moving content by hand. The RS.Exe utility ignores pbix files (I tried it to see what would happen). But works fine on any SSRS reports you want to move.

We had to do some research to understand how to set up mobile access for Power BI Report Server. Power BI Report Server uses the same app as the Power BI web service, but it requires a way for users to access the report server within the network. It requires either a VPN on your mobile device or use of Oauth with a web application proxy and ADFS running on Windows Server 2016. This is all fairly standard, but not usually something a BI team would handle, so make sure to involve your networking/security team early on to help plan.

PBIRSMobileArchAnother important aspect to plan is the folder structure on the Power BI Report Server. In a self-service environment, content owners will manage the content and permissions, so you want to keep things as simple and painless as possible. You’ll want to decide if you want folders for each department, each audience, or each subject area (or some combination thereof). You’ll also want to identify the power users and data owners who will act as content managers and decide if you will have a review/certification process before new reports can be published. This may be different for each department. It doesn’t have to be one size fits all, but you probably want to start with some process and loosen up as you get the hang of things rather than beginning with a free-for-all and then trying to implement some rigor in the deployment process. Permissions in Power BI Report Server work the same as SSRS native mode, so you’ll probably have to teach users how that works.

One area that seemed to have some gaps was the lack of subscription and alerting capabilities in Power BI reports. The normal subscription capabilities for Reporting Services reports are present, but there is nothing for Power BI reports. We used this opportunity to discuss how we might not need much push reporting if users have access to reports on their phones and can easily get to the information that way.

Looking Forward

A lot of the issues noted above should be resolved in the next version of Power BI Report Server. An August 2017 preview was released with the ability to use Power BI models (as opposed to being limited to SSAS Live Connections), which removes a lot of the limitations. Right now the August Preview doesn’t provide a way to refresh a Power BI model, but there should be something in place when the next GA version hits in Q4.

If you would like to use Power BI, but your organization can’t/won’t move to the cloud, Power BI Report Server can be a viable alternative, especially if you get a license for free because you had SQL Server Enterprise with active Software Assurance.

 

 

 

Data Visualization, Microsoft Technologies, Power BI

You Can Now Put Values On Rows In Power BI

Back in January 2016, I wrote a blog post explaining a DAX workaround that allows you to put measures on rows in a matrix in a Power BI report. I’m happy to say that you no longer need my workaround because you can now natively put measures on rows in a matrix in both Power BI Desktop and PowerBI.com.

This is accomplished via a new formatting option for the matrix.

As a quick example, I made a table with years on columns and measures in my values (and nothing on rows). I added three measures: Sales Amount, Total Cost, Gross Margin.

Initially, my matrix puts the measures across the columns.

But I can change that in the formatting options.

Find the Values section on the formatting pane and look for Show On rows. Toggle that switch to On.

And that gives you the three values on rows.

 

 

Excel, Microsoft Technologies, Power BI, SSIS

Installing the Microsoft.ACE.OLEDB.12.0 Provider for Both 64-bit and 32-bit Processing

I recently got a new laptop and had to go through the ritual of reinstalling all my programs and drivers. I sometimes work with SSIS locally to import data from Excel and occasionally do demos with Power BI where I read from an Access database so I needed to install the ACE OLE DB provider.  If you try to do either of those things without the ACE OLE DB provider you will get a message like the one below.

The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine.

I needed both the 32-bit and the 64-bit adapters to cover my various use cases. I started by installing the 64-bit Microsoft Access Database Engine 2010 Redistributable.  If you have ever attempted this, you know there is a secret. You cannot install the 2010 Access database engine with a different processing architecture (bittedness) from your Office install (or install both versions of the 2010 Access database engine) without a workaround.  If you try, you will get an error message.

ACE engine bittedness error

There are two options for getting the other version installed.

Option A: Install the 2007 Office System Driver: Data Connectivity Components

The 2007 Access database engine can read the same format, but didn’t have a 64-bit install. It doesn’t have the check to see what other products are on your machine and can co-exist peacefully with the 64-bit 2010 Access engine.

Option B: Install from the Command Line and Use the Passive Switch

  1. Open the command prompt.
  2. Type the file path and file name for your Access Database Engine install file.
  3. Follow it with a space and then “/passive” and then hit Enter.
  4. Delete or rename the mso.dll registry value in the following registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Common\FilesPaths

ACE install cmd

I wrote up a document for this a while back for a client, but I can never remember where I put it. So I’m posting this on my blog to make this info easier to find for me as well as you.

Azure, Microsoft Technologies, Power BI, SSAS

Please Lend Me Your Vote for Documentation of TMSCHEMA DMVs

I spent a good bit of time looking for the definitions/descriptions of the TMSCHEMA DMVs that allow us to view metadata and monitor the health of SSAS 2016 tabular models. As far as I can tell there are no details about them on any Microsoft site. Many of the columns are obvious, but there are a few fields that show IDs rather than descriptions (e.g., ExplicitDataType in TMSCHEMA_COLUMNS, Type in TMSCHEMA_DATA_SOURCES). It would be great to get the DMVs documented similar to the MDSCHEMA DMVs as they are quite useful for tasks like documenting your tabular model.  Since the TMSCHEMA DMVs work in Azure Analysis Services as well, I have logged this request on the Azure AS User Voice for that. Please lend me a vote so we can make this information more easily available.

https://feedback.azure.com/forums/556165-azure-analysis-services/suggestions/16981015-document-tmschema-dmvs

Conferences, PASS Summit, Power BI

Update On My PASS Summit Feedback

Back in June, I posted the feedback I received on the abstracts I submitted to PASS Summit 2016. I wasn’t originally selected to speak, but I did have one talk that was selected as an alternate. It turns out that a couple of speakers had to cancel , and I am now speaking at PASS Summit.  PASS contacted me two weeks ago to ask if I could fill in. Luckily, my session that was chosen as an alternate was one that I give frequently at SQL Saturdays so it was feasible to get it ready to present at PASS Summit.

I am excited and honored to be speaking at PASS Summit, but I’m not stressed about it. Part of it is that I’ve only had two weeks to stress, and part of it is that I have quite a bit of experience speaking and I feel confident that I can deliver a decent (if not downright good) session. I’m still updating my presentation and practicing the delivery in preparation for this week, but I feel comfortable with the process of preparing. I’ve spoken at PASS Summit and many other conferences and user groups over the last five years. Over that time I have developed a process for preparing to speak, which includes building and editing the presentation as well as some disaster recovery measures. Once I have checked all the boxes I can feel that I have sufficiently prepared. It doesn’t mean everything will go perfectly, but it helps make sure I haven’t forgotten anything.

There are a couple of things I hope you take away from this.

  1. If you are selected as an alternate, it is a real possibility that you will be added to the schedule. This worked out nicely for me because I was already scheduled to be at PASS Summit and my presentation was already in a fairly finished state. You will definitely never be chosen if you never submit. So if you are interested, throw your hat in the ring next year.
  2. Giving a good presentation (and feeling confident about it) takes practice. If you are just beginning your speaking efforts in the SQL community and you are nervous about it, know that it gets easier (for most people) as you gain more experience. If you enjoy it, keep putting yourself out there. Speaking is something I thoroughly enjoy these days, and I have (mostly) conquered the nerves. Some of that is because I have experienced failures in presentations and the world did not end. Some of that is the result of my presentation prep checklist, which helps me feel prepared.

The downside of the last-minute addition is that I haven’t had as much time to market my session. So if you like Power BI, data visualization, or Kansas City BBQ, and you are attending PASS Summit, I invite you to join me on Friday morning at 8 am in room 3AB. I’m discussing how to apply good data visualization practices in Power BI using data on BBQ restaurants.

Here’s the description for the session I’m presenting:

Overcoming Chartaphobia with Power BI

Do reports in your organization consist mostly of giant tables of data? Perhaps you have gotten as far as adding KPIs or conditional highlighting to the tables. Maybe you have charts, but they are hideous and distracting. Although tables of data presented as reports are commonly found in many organizations, they may be doing you and your users a disservice. We’ll discuss why cognitive psychological studies tell us that graphs and pictures are more effective at communicating trends and comparisons and how to prepare to create good data visualizations. Then we’ll explore how to employ purposeful data viz designs to help users achieve their goal of making informed decisions, using a fun and useful Power BI dashboard. You’ll leave with guidance on how to take boring or unreadable tables of data and turn them into useful and visually appealing reports. 

I hope to see you in my session on Friday!