What is the Microsoft Power BI connector for SAP?

I have been getting some questions about it recently so I decided to write a few lines in addition to what’s now available on the Power BI site. Here is a list of some key points about the tool:

  • No middle-ware components are necessary to provide the connectivity between Excel and the Business Objects server. In order to connect, a user will only need to provide the URL to Business Objects and be able to be authenticated by it
  • Only the latest version of Business Objects is supported (4.1 SP2)
  • Only UNX type of universes are supported (must be created with the Information Design tool), the older version of universe (UNV) that typically was created using a Universe Designer, is not currently supported
  • Any universes on Business Objects 4.0 or 3.x are not supported
  • Only relational universes are supported, multidimensional universes are not supported. However, a relational universe that exposes Hana or BW data is supported
  • Universes cannot contain prompts or parameters
  • Power Pivot refresh from the cloud is not yet supported, but supposedly this feature is coming at some point (my assumption is that the generic Power Pivot refresh from Power Query will need to be supported by Power BI first before the SAP Connector can provide Data refresh in the cloud)

HTML5 and Anonymous access in Power BI… or check out the new Demo Gallery

I don’t expect that anyone will be blown away, at least initially, with the new demo gallery now available on the Microsoft Power BI site because to a business user, the content of those demos will look a little difficult to relate to if not a little nonsensical. Zany layouts aside, there is, however, a very significant piece of news to celebrate. Jen Underwood has already mentioned this in her excellent post but now we have a somewhat official, albeit not very conspicuous, evidence that two of the most requested features of the Microsoft BI stack that we have been anxiously awaiting for a very long time are finally becoming available:

  • HTML5 support for Power View
  • Support for Anonymous authentication and ability to expose Power View on a public anonymous site

Try to right click on the demos in the gallery and you will see that the familiar Silverlight menu option is no longer available, instead you will see a normal set of options that show up when you right click on any typical HTML element of the page. If you examine the source code a little bit close you will see <iframe
frameborder=”0″ allowtransparency=”true” scrolling=”no” id=”” style=”width:1024px; height:650px;” src=”https://powerbishowcase.sharepoint.com/_layouts/15/guestaccess.aspx?guestaccesstoken=sEKhHj4pZbI%2bedyTIbQ%2fywmdj7j9pVw0VsXZGwXN7ew%3d&docid=05e4c65f3966e46a9b72d2d3681955ac0&action=embedview&wdBIPreview=1″></iframe>
that the HTML5 code is generated inside an IFRAME. Looks like there
are several parameters, that will hopefully be documented soon, that are involved:

  1. Powerbishowcase.sharepoint.com – is the main URL
  2. Guestaccesstoken
  3. DocID
  4. Action (Embedview is already well documented)
  5. wdBIPreview

In fact, if you put all of the pieces together, you will be able construct this link https://powerbishowcase.sharepoint.com/_layouts/15/guestaccess.aspx?guestaccesstoken=sEKhHj4pZbI%2bedyTIbQ%2fywmdj7j9pVw0VsXZGwXN7ew%3d&docid=05e4c65f3966e46a9b72d2d3681955ac0&action=embedview&wdBIPreview=1

It is good to see the Power BI site to get regular updates, particularly of such great significance. Definitely looking forward to the hopefully soon upcoming documentation for these awesome new features.

HTML5 support In Power View and Power BI

Now that Power BI is Generally Available (GA) I thought it would be useful to do a quick analysis of how Power View visualizations are supported in Power BI across the three media – Silverlight (default), HTML5 and Windows 8 App Please find a table below for a quick reference

Feature

Silverlight

HTML5

Windows 8 App

Background Images

Yes

No

No

Images in Tiles

Yes (but not from external sites)

No

No

Background colors

Yes

No

No

Filters – charts

Yes

Yes

Yes

Filters – dashboard

Yes

Yes

Yes

Maximize Charts

Yes

No

No

Sort charts

Yes

Yes

Yes

Line Charts

Yes

Yes

Yes

Bar Charts

Yes

Yes

Yes

Scatter Plots

Yes

Yes

Yes

Cards

Yes

No

No

Table/Matrix

Yes

Yes

Yes

Matrix with KPI

Yes

Yes

Yes

Drill down on charts

Yes

Yes

Yes

Drill down on matrix

Yes

Yes

No

Scatter Plot (static)

Yes

Yes

Yes

Scatter Plot (with play axis)

Yes

No

No

Tiles

Yes

Yes

No

Maps

Yes

No

No

Drill-through

No

No

No

       

It is easy to see that there is certain parity between features available in HTML5 and Windows 8 app. Also, I just wanted to make a few general observations about the performance. Generally speaking, the fastest chart rendering and drill down experience is available in the Windows 8 app. In most cases, HTML5 version seems to be doing a little better with refreshing charts however, I have found several instances where the Silverlight version of the dashboard ran order of magnitude faster than the HTML5 option. It seems like every week, more and more Silverlight-only features become available on the HTM5 version which seems to lend some support towards the “cloud first” software release strategy from Microsoft.

Microsoft self-service BI on premise vs cloud

It appears that Satya Nadella has added an additional layer of context to the already ambitious Microsoft strategy of transforming itself into a “devices and services” company by declaring that “Going forward, it’s a mobile-first, cloud-first world”.

I believe that it is safe to assume that in the near future, the roadmap of every Microsoft product will be updated to reflect an alignment with this strategy. I am particularly interested in how this strategy will affect the Microsoft BI stack. Jen Underwood wrote a great post about “cloud first, cloud only” almost six months ago surfacing some of benefits as well as some issues arising from this strategy. I am still struggling a little bit with figuring out what the overall MS BI landscape will look like as it relates to the self-service use case vs. the enterprise deployment; therefore, I decided to put some thoughts on paper in an attempt to frame up a way of tying it together.

Here is a simple (but by no means complete) table to compare several key features between the cloud and on premise offerings (please be advised, that technically, the Power BI is still not GA, therefore, the information below may be out of date by the time you are reading it)

Feature Cloud (Self Service) On Premise (Self Services) On Premise (Enterprise)
In memory Yes Yes Yes
Data size 250MB Configurable up to SharePoint limit (2gb) Unlimited (or limited by RAM)
Data Refresh Yes, using Gateway Yes, Power Pivot Gallery Yes
Direct access to on premise data No Yes Yes
Refresh from Power Query Yes No No
Render Power Map in browser No No No
HTML5 support Yes (* not all there yet) No No
Render Power View Excel Sheets Yes Yes (SP 2013), No (SP 2010) Yes (SP 2013), No (SP 2010)
Create Power View reports No Yes Yes
Support for Power View in Power Point No Yes Yes
Natural Language query support Yes No No
Synonyms Yes No No
Windows Authentication (corporate domain) Yes (DirSync) Yes Yes

The biggest issue for me right now in terms of parity between the cloud and on premise offerings is the lack of ability to drive Power View dashboards through Power Point from the workbooks posted in the cloud. Cloud first strategy should (at least in theory) allow Microsoft to innovate at a much higher rate, however, the apparent disparity between the two flavors of the stack confuses the customer by making him/her think that now there are two forked versions of the stack that will be maintained independently from one another which may not look in Microsoft’s favor, particularly in light of the apparent “simplification” strategy manifested by some of the Microsoft’s competitors.

Getting back in the game…

I have not been posting much on the site in the last few months. This lack of activity was attributed to predominantly two things: A) My summer got really busy with my primary revenue generating activities and B) There was really not whole lot going on in the MS BI space that I felt I would have much to add to.

Well, as busy as I still am, the amount of new developments in MS BI is about to kick up a few notches in the next couple of months so I figured I had better make time and get back in the game of updating this site on a regular basis.

It seems to me that the biggest vacuum of news today is around the Power BI. The sign up page has not seen many changes in a couple of months now and I think everyone is getting a little anxious to finally get to play with the new features and see if our prayers and questions are being answered.

What are some of the questions one might ask?

Well, here is as good a list as any:

  1. What exactly is Power BI – a product? A SKU? A brand? A licensing model? A new software distribution model?
  2. Is Power BI a cloud only offering and what would an on premise solution look like? And When?
  3. Seems like there are new visualization elements that are becoming available. Does it mean that we’ll see things like:
    1. Drillthroughs
    2. Waterfall charts, butterfly charts, etc –we need more options!
    3. Formatting options for color and style
  4. Will the Power View experience be more touch friendly? (drilling into by double clicking and drilling up can be a challenge for somebody with fat fingers)
  5. And ultimately the questions that has caused me the most grief from my customers and prospects thus far is when will it run on iPad?

I am looking forward to having these questions answered in the next several weeks.

Stay tuned…..

A quick rant on Power BI, #confused….

There are now scores of blogs and web articles available that do a great job of introducing Power BI (a good example can be found here) so there is little value in re-hashing it in this post. I do, however, want to reflect and pontificate a bit on this announcement.

  • The most important piece of news for me is Mobile BI. Mobile BI has been a sore topic in the Microsoft BI world for a while now and I think this is the first time we have heard anything semi-official from Microsoft confirming that PowerView will be delivered using HTML5. I am not sure how I feel about the “Cloud First and Cloud Only” strategy yet. Hopefully, Microsoft will release a public roadmap for Mobile BI functionality soon. If nothing else, we now have some evidence that this functionality is coming (albeit in Office365 flavor first) which, if nothing else, gives me a little more strength to wait a little longer
  • The Power Query and Power Map are great additions to the MS BI features, with Power Query product being dramatically more significant one of the two. Both of these products have now been around for a while, although under different names. Given the fact that both are delivered as Excel add-ons (which means they run on a desktop), I am a little confused with the fact that they are being marketed with the Power BI which is a cloud Office365 offering
  • Natural Language Query Language – this will be a great Demo feature, however, I am a little skeptical with how practical it will be
  • Data Stewardship – sounds like a neat feature but I think we need some clarity with respect to where this fits in on the MDS roadmap for SQL Server
  • I am also encouraged by the news that the PowerPivot workbooks deployed in Power BI environment will be able to refresh from the on premise data sources. I can only then assume that Azure IaaS connectivity will also be supported.

There is something in this announcement that I find a little bit confusing. I would normally expect new functionality to be added to the enterprise feature set first and to the Self Service use case later. I am afraid that a lot of IT shops will find it a little discouraging that BI features they provide via the enterprise toolset are inferior to what the end users will get for a self-service use case in the cloud.

PowerView can now connect to multidimensional models

The cumulative update 4 for the SQL Server 2012 Service Pack 1 has now been released. This update enables PowerView to connect to Multidimensional models and also to have global filters across multiple views (pages) in a PowerView report.

Please follow this link for more details

http://blogs.msdn.com/b/sqlrsteamblog/archive/2013/05/31/power-view-for-multidimensional-models-released.aspx

SAP vs. Microsoft, Information Assets vs. End User Experience positioning

If your company runs SAP, chances are the picture below represents your environment pretty well

The black colors represent the existing investments/business processes and the size of the bubbles represents the effort/investment $$$. Your company has made a very large investment in the SAP back-end (ERP, BW, etc.) which is represented by a large black circle on the lower left. The information worker end-user experience in SAP is represented by a relatively smaller bubble (BobJ, BEx) and we can also see a solid line that connects these two Information Assets (one could argue that this solid line is not quite as solid in reality but that’s not the point I am trying to make here). The important point that I am trying to make however is characterized by a significantly larger bubble that represents the End User experience that the information workers in your company enjoy using Microsoft tools such as Excel, PowerPoint and SharePoint.

Many IT folks seem surprised by the size of that Information Asset but if you think about the herculean work that is done every month across various groups in your organization to dump data out of SAP (BEx, etc.), link and cleanse and scrub that data and then build countless reports in Excel using HLOOKUPs, VLOOKUPs, SUMIFs and whatever other Excel black magic necessary to get the job done, it should become clear that the size of that circle is very well justified.

Unfortunately, many companies tend to completely downplay the significance of the End User experience while pouring millions of dollars in the infrastructure and back-end.

I think that there are at least two reasons why the end-user experience gets no respect from IT:

  1. Disconnect between IT and the business audience – this is a topic that has gotten a lot of attention and coverage and is pretty universally accepted at this point and, therefore, I am not going to dwell on it too much
  2. Philosophical encampment of the SAP ideology within IT which leads to denial and rejection of anything non-SAP

The second argument is unfortunately very commonplace.

Is there a way to reframe this situation such that we can stop bickering and arguing about what vendor/technology is better and instead focus on what is the right thing to do for the company? I think there is…

I think that it is very clear that both vendors, SAP and Microsoft, are very strategic for an organization and therefore we should not think about who is better or worse but rather we should think about how do we leverage and maximize the existing investments with both vendors. It, therefore, seems pretty natural that some Microsoft Office infrastructure must be deployed to create a solid line (i.e. set up the plumbing) between the SAP back-end and front end-user tools (represented by red color on the diagram). Typically, a large organization that has an enterprise agreement with Microsoft will have most of these components already licensed and, frankly speaking, most organizations will have these components already deployed (I am talking about SQL Server, SharePoint, etc.) although not necessarily configured to enable the flow data from SAP into the hands of end users.

Having this Microsoft infrastructure does not facilitate any more replication of data than what is already taking place as the outputs (manually built Excel reports and PowerPoint decks) remain the same, so the information is already replicated from SAP and sourced into these visualizations, albeit manually. So the volume of data movement will remain the same, but all of the non-value adding work will be reduced to the bare minimum. The following is a list of some of the benefits of this approach:

  • Automation of manual data extracts
  • Governance and controllership of the information flow
  • Security and oversight
  • Build Excel reports once and refresh them from the reporting model, no manual updates necessary
  • Same for PowerPoint (build one deck that will refresh from the model with up to date information rendered for the individual user credentials)
  • Empowering end users to own content creation therefore significantly reducing the IT reporting backlog
  • Ultimately – giving the End Users what they want, like and expect

SAP Visual Intelligence vs. Microsoft PowerPivot and PowerView

In this post I will review the latest version of SAP Visual Intelligence – 1.08 – and see how it stacks up against the Microsoft self-service BI tools, Excel, PowerPivot and PowerView. In the initial release, Visual Intelligence only supported SAP Hana, but in its latest iteration few other data sources are supported:

  1. CSV File
  2. SAP Hana
  3. SAPBW as exposed as a view in SAP Hana
  4. MS Excel
  5. Freehand SQL (basically ODBC)
  6. SAP Business Objects Universe

Just to clear some confusion that this list may generate, Visual Intelligence does not access BW objects directly, instead, Hana has some connectivity to connect to BW models, DSOs and Query Snapshots and then expose those as a Hana view, in short, Visual Intelligence knows how to plug into Hana but not BW.

Data import

Since I don’t have Hana running on my laptop, I decided to use a CSV file to load in both Visual Intelligence and PowerPivot. I used a 173MB file that contained about 1.2M records in it. It took just under 30 seconds to import the file:

After that I was able to bring the data into the pivot table and start slicing and dicing it. Loading data in Visual Intelligence wasn’t quite as smooth. Having selected the same file, I had to wait for almost 40 seconds looking at the frozen screen. Then, after the meta data have been read, I clicked on Acquire. After about a minute of looking at the spinning cursor, I was rewarded with a warning message: I was surprised to find out that the acquisition will fetch more than 30 million cells, but having been known to boldly go where no man has gone before, I took my chances and bravely hit the Yes button. Unfortunately, that was as far as this adventure would take me with respect to that data file, as the following message ended my hopes of being able to analyze a meekly 1.2M of rows of data. I have not been able to find the theoretical cell limit for Visual Intelligence, all I know is that my data file had 1.2M of rows and fewer than 30 columns, but it was clearly too much.

Lesson #1: Do not plan to use Visual Intelligence on datasets larger than 1M rows.

I therefore had to resort to a much smaller file for my testing, so I used some baseball stats CSV file with 95K rows. I was able to finally load that file into Visual Intelligence after about 20 second wait… a similar task in Excel took 7 seconds.

Data Exploration

Before I jump into data exploration, we have to take a quick pause and think about the audience for it. I think it makes sense to partition the audience up by technical vs non-technical users (IT vs the rest of the world) and I am going to ignore the technical audience altogether in this review because I think that the primary market for data exploration is the non-technical audience. Also, I have to divide that non-technical audience in two groups, Power User and Novice. I realize that most people are actually somewhere in between, but I think that reviewing these tools in the light of those two outliers will give us the best context for forming an opinion. Another thing I have to say that I am probably a PowerUser outlier within the PowerUser group and therefore it is very difficult for me sometime to just see this tool from the complete Novice perspective, but I will definitely try to do my best. So, here is what I think about my data exploration experience. Visual Intelligence does a decent job of inferring measures, attributes and hierarchies out of the data. When I say decent I mean that to somebody who has completely no clue about data, does not know how to sum data up in excel, it does a job good enough to get one going. For me, however, it was a very frustrating experience as the tool tried to do everything backwards from the way I am used to. For fairness sake, I am sure that if I spent more time using the tool, I am pretty sure I would be able to get comfortable with it. But being as it was, I was able to create a Line chart and a Bar chart that looked like this Visual Intelligence has a number of different chart types available, some of which are not available in excel:

The chart I was excited the most was the Waterfall chart as it is not offered in excel, although it is possible to create one with a trick that I will have to write about in another post. But, this is where I ran into my second biggest disappointment with the product, you can only create one chart at a time. In other words, one cannot create a dashboard that would show a trend chart and a donut chart on the same page.

Lesson #2: Visualizations are limited to only one chart at a time.

Modeling

This is my biggest disappointment with the product which, frankly speaking renders it virtually useless for a power user. Modeling is practically not existent in Visual Intelligence. Although the tool does a good enough job for a Novice level user with some basic calculations and hierarchies, it is impossible to add additional data to the model. In my case, I was able to load the batting data into Visual Intelligence, but I was not able to load any team related information as a dimension so I could slice the batting data by team attributes. Essentially, the only way to do this would have been to add all of the team attributes to the batting file, but that would dramatically increase the number of cells in the data set and we already know that the tool does not like that very much.

Lesson #3: Visual Intelligence offers virtually no modeling capabilities outside of basic calculations and hierarchies.

Collaboration

After one creates a particularly good looking chart (we already know that anything beyond that is not supported in VI) one has find a way to share one’s work with the world. In the Microsoft world, an Excel file that contains both a PowerPivot model (data) and traditional excel charts or PowerView dashboards/reports, that file can be simply saved into SharePoint that then will render all of the visualization to and end user as a web page. No such luck with Visual Intelligence as its visualizations are saved as just in image in to the Streamwork portal and the end user will not be able to interact with it online. Lesson #4: Visual Intelligence is a fat client solutions, no collaboration or online viewing is available.

Random tidbits

  • Calculations in VI are very simple, nothing like PowerPivot with respect to time intelligence, financial intelligence, balance sheet logic, etc.
  • Charts in VI are not drillable, they are in PowerView
  • No drilling to detail in VI, there is one in Excel
  • No dimensional modeling in Visual Intelligence, there is on in PowerPivot
  • Tree map is available in VI, not in Excel, heat maps are available in both, although more intuitive in VI than excel (conditional formatting)

…. More things to come later tonight…

Customizing PowerView in PowerPoint

Although “customizing” may be too strong of a word, there are few things that we can tweak to improve the look and feel of PowerView slides that had just been exported from SharePoint. The first thing to do after the fresh export is to adjust the size of the exported image. For some reason as you can see in the image below, the PowerView image does not take up the entire slide, leaving some white edges around which would not be a problem if the PowerView image has a white background, but in my case, I have used a grey background in PowerView and it does not look good on a white PowerPoint background.

Luckily, this can be easily remedied by just clicking on the image and resizing it to fit the entire slide. That helps while presenting the static slide, however, after one clicks the “Interact” button the, unsightly white lines come back again around the edges of the slide.

The only way that I found to make that white space go away is to do the following:

  1. Right click on the PowerView image and click on Property Sheet
  2. A property screen will come up with the following parameters: ViewMode=CachedPreview,ReportSection=ReportSection2,AllowSectionNavigation=False,Fit=True,PreviewBar=False,BackgroundColor=White,Border=True,AllowEditViewMode=False,AllowFullScreenViewMode=False,Trace_HostApplication=Powerpoint
  3. Note that the BackgroundColor is set to White which is why we are see those white lines on the edges of our visualization
  4. Change the background to black or any other color that matches the background of your PowerView slide
  5. Don’t forget to hit save :)

That should be all you need to do to make sure that your PowerView looks nice in PowerPoint if its background is anything but white.

The rest of the parameters are interesting as well…

For example, by default, each PowerView view is exported into a separate slide, so one needs to physically move from one slide to another and click on “Interact” again and again. Setting the AllowSectionNavigation to True allows us to navigate from one PowerView view to another from the same slide, which is similar to the end-user experience in SharePoint. Both, Thumbnail button, Font Selection as well as the Navigation Arrows now become available