Atlanta MS BI and Power BI Group Meeting on August 2nd

/0 Comments/in , /by

Please join us online for the next Atlanta MS BI and Power BI Group meeting on Monday, August 2nd, at 6:30 PM.  Avi Singh (LearnPowerBI.com) will discuss how you can achieve a successful Power BI career.  For more details and sign up, visit our group page.

Presentation:How to Create a Successful Power BI Career Without the Struggle (By “Niching Down”)
Date:August 2nd
Time:6:30 – 8:30 PM ET
Place:Click here to join the meeting
Overview:Your Grandma was right! You cannot please everyone. And when you go out there and wave your flag as a Power BI Professional, that’s exactly what you are trying to do. And it doesn’t work. Either you get no results or have to work really hard for every inch of progress. The problem is that most professionals either miss or mess-up the first crucial step of their Power BI Career – Niching Down! In this session we’d talk about:

– Why Niching down is the crucial first step towards a successful Power BI Career

– How you can come up with the Niche that you should focus on

– Address some of the fears you may have about niching down (a classic one is: “Oh, but I don’t want to miss out on all the other opportunities out there by niching down”)

Speaker:If you are searching for a more meaningful work life as a Power BI Consultant ➔ Where you do what you love, create an impact by helping others and create a life of freedom for yourself ➔ Then Avi is dedicated in helping you achieve those goals.

Hint: The classic wisdom of “Learning More” and “Working Hard” actually does NOT help you get to these goals. Avi leads a five-step program for creating successful Power BI Consultants; starting with the first step…you guessed it 😉 “Niching Down”

PowerBILogo

Interactive Excel Pivots in Power BI

/0 Comments/in /by

A long-standing limitation of Power BI has been that Excel pivot reports connected to external Analysis Services models can’t be interacted with when uploaded to Power BI Service. You get the cached pivot report but any attempt to interact with the report online (that is in Excel Online), such as to change a slicer or filter, would result in an error complaining that the connection can’t be refreshed and there hasn’t been a workaround. Today, Microsoft partially lifted this limitation by supporting interactive pivots connected to Power BI datasets. Unfortunately, pivots connected to external Analysis Services models (both on-prem and cloud) still don’t support interactivity.

For some reason, this feature is called “connected PivotTable refresh” although a better name would be “interactive Excel pivots finally”. It works in Power BI Pro and Premium. Unlike the initial announcement that speculated that this feature would be available by simply uploading your Excel file to Power BI Service, it appears that it requires the Excel file to be uploaded to OneDrive or SharePoint Online. Here are the high-level steps that worked for me:

  1. Create your Excel pivot using the Analyze in Excel feature or directly from Excel. Assuming you have a recent Office 365 build, the latter option allows you to open Excel on the desktop and click Insert->Pivot Table->From Power BI to connect to a Power BI dataset from within Excel Desktop.
  2. Click Save As and save your workbook to OneDrive or SharePoint Online. Recall that you can specify a SharePoint site for storing workspace files in the Power BI workspace settings if you don’t want to upload the file to your personal folder.
  3. Use the Power BI Service “Get data” feature to upload (not import) the Excel workbook in a Power BI workspace. Again, the Excel workbook must be located either in OneDrive or SharePoint online.
  4. Interact with the report in Power BI Service, such by drilling down, drilling through, or even rebinding the report to different fields! As a bonus, when the underlying dataset refreshes, your pivot report will show the latest data as it connects live to the dataset.

The “connected PivotTable refresh” is a much-needed enhancement that brings the same interactive features to Excel pivot reports that users previously had in SharePoint Server. Now users who favor Excel pivots for interactive exploration can share their reports and the interactive features would be preserved when the report is rendered online. The feature requires storing the Excel workbook in OneDrive or SharePoint Online and using an Analyze in Excel connection. Unfortunately, it doesn’t work with Analysis Services models that are external to Power BI (not deployed to Power BI Service) which is another incentive to move your semantic models to Power BI as I’ve been advocating for some time.

Cloud Inhibitors

/0 Comments/in /by

In a previous blog, I said I consider it a travesty when a vendor’s cloud offering prohibits direct access to the data in its native storage, which is typically a relational database, and therefore a perfect store for data analytics and integration, such as to run real-time analytics or export the data to load a data mart. There is nothing simpler than connecting to a relational database, which is designed to handle efficiently select and filter operations. Unfortunately, it’s becoming a norm where the move to the cloud requires going through all sorts of hoops to access the data. Some enlightened providers offer direct access to the native store by charging a premium fee. However, this is not usually an option and most vendors require you call their REST APIs.

Since, the move to the cloud shouldn’t make things more difficult, I call these vendors “cloud inhibitors”. Such vendors usually cite concerns, such as security and performance, as reasons to prevent access. But the irony is that such issues could be easier addressed by the vendor, such as by hosting the customer on a dedicated VM (think of Power BI Premium), instead of transferring the data integration burden to the customer.

The previous “Cloud Inhibitor” award was held by Microsoft Dynamics Online that has decided to prevent access to the Dynamics SQL database and recommends the customer to consume the staged data from Azure Data Lake CDM folders. One popular ERP vendor has gone even further when embracing AWS as their platform of choice. Not only did they disallow access to their SQL Server database, but they also disallow access to the S3 data lake where they stage the data. Instead, when moving their on-prem ERP solution to the cloud, a client has found that they have two choices for data integration: call the REST APIs or use a JDBC driver, with the latter option out of reach for Microsoft apps.

I’m still researching the best option to enable this client access their data with minimum integration effort although the recommended solution by the vendor was to write a client app to call REST APIs or use Java-based ETL. How ridiculous is this given that that client could simply connect to their ERP database in the past? So much about real-time BI and no-code data integration!