Power BI Data Management Gateway and Resources

Power BI continues to be a hot topic that many people have been asking me about.

As you may know, there was an announcement in July about the new Power BI in Office 365 that is coming. Here is more information about that announcement - http://www.astaticstate.com/2013/07/office-365-power-bi-announcement.html

A lot of people have been asking for more information.

First if you want to try out the Power BI Preview, please go here to sign-up for the preview - http://office.microsoft.com/en-us/excel/power-bi-FX104080667.aspx

Second I have had many people ask me for some good resources. If you want to learn more about Power BI with Power Query, Power Pivot, Power View and Power Map here are two really good links - http://technet.microsoft.com/en-us/library/dn198234.aspx and http://technet.microsoft.com/en-us/library/dn198234.aspx. If you follow the sub links of these articles you will find a ton of good information.

Third, the biggest question I still get is does the new Power BI support the ability to connect to line of business databases. This is probably one of the most exciting things being introduced because up till now because Excel Services could only work with the data contained in the spreadsheet. The answer is the new Data Management Gateway. It requires to you to install a gateway on-premise that will make a secure connection to SharePoint Online. It push data from on-premise (on demand or a schedule) to your spreadsheets stored in SharePoint Online to render the reports through the browser. You still have the ability to open the Excel client with the add-ins for Power Query, Power Pivot, Power View and Power Map.

Here is a really good reference for you to start reading and all of its associated articles - http://office.microsoft.com/en-us/office365-sharepoint-online-enterprise-help/power-bi-admin-center-help-HA104078330.aspx?CTT=5&origin=HA104078557. There are articles that cover what the Data Management Gateway is, how it is installed / configured, how to create data sources using OData and then how to connect them into Excel. There is even a really interesting article about how SSIS can be utilized as a data source for Power BI. As well is a good article on health reports and utilization of the Data Management Gateway.

Office 365 Power BI Announcement

Introduction

There was a really exciting new announcements made this week by Microsoft for Office 365. This week they announced Power BI for Office 365. This is a huge leap forward to self-service BI. This is something I talk with a lot of people about. Organizations know Excel, and with Excel Service you can create powerful dynamic reports and dashboards in the same tools that organizations have expertise in.

The Office 365 BI Power tools

If you know SharePoint Online, you know there is Excel Services which provide a powerful BI capability today. Additionally today you may know about Power Pivot and Power View. New additions are Power Query and Power Map.

• Power Query – is a new capability which allows people to easily search and access data that may reside publically or within their organization with Excel.

• Power Map – is a new visualization tool that good for mapping, exploring and interacting with data.

• Power Pivot – is a solution in Excel to build complex data models. This supports the ability to in-memory analytics and modeling against large volumes of data.

• Power View – is a powerful solution in Excel to build interactive charts, graphs, KPIs, etc.

Together these tools can be brought together build a BI experience right in Office 365.

But there is more…

Wait, there is more exciting stuff here.

• New Refreshing you data – This is long been a discussion topic I have had with organizations reviewing Office 365. Up to this point, Excel Services in SharePoint Online work great but it only has the ability to work with data in the Excel spreadsheet itself. There were some creative solutions out there to fresh your Excel spreadsheets with data in SharePoint Online (http://www.astaticstate.com/2013/03/sharepoint-online-bi-and-connecting-to.html). Now there will be a new capability that will use the Data Management Gateway which allows you to refresh data. This is super EXCITING! Now you can have Excel Services running in the cloud that securely access your line of business data repositories.

• New BI Sites – There have been dashboard and reporting site templates around for a while now. With this new release of Power BI, there will be a new site template available to support these activities. The really cool thing this does is allows you to centrally manage your reports and data connections. You can then re-use these data connections across reports. Remember you can still add in other SharePoint capabilities to create dashboard that incorporate social, content and business processes.

• New Natural Language Query capability – There is a new natural language capability is available that will allow every data end users to enter statements and then query the reports, without having to use a bunch of fields and controls. This is really interesting as it will allow end users to go in and discover data without having to engage a developer.

• Easy to Access – Additionally these new Power BI solutions will be available through apps and browser to allow users access to the reports regardless of the device they are using.

Commitment to the Evergreen Service

This is yet again another example of how Office 365 makes good on its commitment to being an Evergreen Service. I speak with organizations about this all the time. You do not have to wait for a major release to get new features and capabilities. With the Office 365 cloud, organizations can get new capabilities quickly and start seeing immediate returns on their cloud investment.

References

• Power BI for Office 365 – with a good video - http://office.microsoft.com/en-us/excel/power-bi-FX104080667.aspx
• Office Team Blog Announcement - http://blogs.office.com/b/office-news/archive/2013/07/08/announcing-power-bi-for-office-365.aspx
• SQL Server Team Blog Announcement - http://blogs.technet.com/b/dataplatforminsider/archive/2013/07/08/introducing-power-bi-for-office-365.aspx

SharePoint Online BI and Connecting to On-Premise Data

Refreshing Data with PowerPivot and Excel Services

I came across this really good whitepaper on how to refresh data in PowerPivot in Excel Services in SharePoint Online. The most important part is “in SharePoint Online”.

If you know much about SharePoint Online Business Intelligence, may know we provide Business Intelligences features in SharePoint Online with limitations. The most important being something like Excel Services cannot make a live connection to a database. If the data resides in the Excel spreadsheet itself no problem Excel Services works great with that data. The challenge has always been how to get the data refreshed into the spreadsheet if the data needs to be pulled from a line of business databases?

The solution described in the spreadsheet takes advantage of several new capabilities of SharePoint Online. It takes advantage of the new SharePoint Online PowerShell commands, the Sync capability of SkyDrive Pro and the new PowerPivot solution now supported in SharePoint Online. The script is very simple. All you do is schedule it, it will check out the Excel workbook, refresh the data, check it in, and then sync the file right back into SharePoint Online. Nice!

Before this new release of SharePoint Online, organizations would have to manually do this action. If an organization got crafty they could use web services to accomplish the same thing but it would be a cumbersome solution to manage and maintain.

This new solution is just so easy and straight forward.

Refreshing Excel workbooks with embedded PowerPivot models from SharePoint Online (white paper) - http://technet.microsoft.com/en-us/library/jj992650.aspx

SQL Reporting Services

If you have read the new SharePoint Online Service Description on Insights (http://technet.microsoft.com/en-us/library/jj819252.aspx) you will have noticed several new capabilities. One of these new capabilities is SQL Reporting Services reporting web parts are now supported. SSRS reports running out of a document library in the cloud can utilize data in a SharePoint lists. You could use a similar solution using the new SharePoint Online Client Side Object model to refresh data in a SharePoint list that the SSRS report would use. The only limitation to consider is the amount of data you are moving into the cloud. I am not suggesting that a SharePoint List cannot handle the data however we all know that SharePoint Lists are not meant to replace something like a SQL Server database. I would recommend that you do as much processing and aggregation of the data before sending up into SharePoint lists. It may even make sense to de-normalize the data a little.

Still you should be able to create some really nice reports that can be centrally managed and access through SharePoint Online.

Visio Services

Visio Services has always been available in SharePoint Online. I have in the past successfully used it to connect to data in SharePoint lists and then create both reports and dashboards. As usual the limitation was how to get the data on-premise.

The way I see it, you have two approaches for getting Visio Services in SharePoint Online to work with a database on-premise.

First, you can do what I just described above where you use the Client Side Object Model to get data into SharePoint Online lists.

Second is leverage BCS! This is new. If you have read what is new with Visio Services (http://msdn.microsoft.com/en-us/library/jj164027.aspx) you will see that SharePoint Online supports BCS. Additionally if you have SharePoint 2013 on-premise, it is now possible to implement a SharePoint Hybrid solution (http://technet.microsoft.com/en-us/library/jj838715.aspx). In this case you can have live on-premise data securely connected to SharePoint Online which Visio Services can build reports off of.

Conclusions

As you are seeing with the introduction of SharePoint Online PowerShell, Client Object Model, SharePoint Hybrid, SSRS, PowerView and SkyDrive Pro along with new improvements to BCS, Excel Services and Visio Services we now have some better options for BI in SharePoint Online. I see the gap starting to close and hope with time, more gaps to be closed.

SQL Server 2012 Brings New Features and Capabilities for SharePoint 2010

Introduction
If you have SharePoint customer or architect and you really should be looking at the new SQL 2012 release. As we all know SharePoint success is highly contingent on SQL Server. You need a strong SQL Server deployment to ensure there is good performance, high availability, back up/recovery, etc. Additionally Microsoft delivers their Business Intelligence (BI) stack through SharePoint and there have been several new features and capabilities added there as well.
For SQL 2012 there are three areas where there have been major capability additions:

1. Mission Critical Confidence – This is the capability to more easily deliver high-availability with lower total cost of ownership.
2. Breakthrough Insight – New and expanded capabilities for Business Intelligence.
3. Cloud on Your Terms – Additional capabilities to create SQL databases in either the private or public cloud (Office 365).

In this blog I am going to cover some of the new SQL 2010 capabilities specifically focusing on how they can improve your SharePoint 2010 implementation. Please note that there are a lot of new capabilities for SQL Server 2012 which I have not covered such as data warehousing, resource management, full text searching, auditing, Big Data (Hadoop), etc. Here is a good reference to quickly spin up on all the new capabilities (“What's New in SQL Server 2012 Whitepaper” located here).
Mission Critical Confidence
AlwaysOn
First and foremost is the new high-availability solution in SQL Server 2012 called AlwaysOn. One of the first and foremost challenges that organizations face with deploying SharePoint 2010 is providing a solution architecture that will meet the SLA’s of their business users. Business processes, technical processes and Governance need to be put in place to ensure that SharePoint 2010 will be up as much as possible.
In the past with SQL Server 2008 R2, you employ such solutions as clustering, mirroring, log shipping and replication (my previous blog on this topic). However this could require a lot of planning, configuration, and management. With SQL 2012 AlwaysOn, new configuration wizards and tuning tools are now provided that makes set-up and configuration of High-Availability extremely simple.
The concept of Availability Groups have been added which specifically makes configuration of Database Mirroring easier. Availability Groups are a logical of databases that failover together. Through the configuration wizard, you can determine if you need such things as automatic or manual failover, set-up of primary and multiple secondary instances, synchronous or asynchronous data movement, etc.
Availability Groups remove the need for shared disk storage (SAN or NAS) for deployment of a failover cluster instance. Note that AlwaysOn Failover Cluster Instances support multiple-site clustering across subnets which subsequently enables cross datacenter failover.
This feature is very useful when setting up your SharePoint 2010 (or 2007) farm because High Availability is one of the most paramount tasks that are needed when setting up a mission critical SharePoint environment.
Recovery Advisor
Database Recovery Advisor provides many new features and capability for the support of back-up and restore of databases through SQL Server Management Studio. The new Recovery Advisor streamlines the back-up process. One such solution is a visual timeline that provides the backup history and all the available points in which you can restore from.
There is also new capability called Split File Backup which allows you to split a backup into multiple files. This allows for quicker backup and restores because they can be written and restored across disks running in parallel.
This is very helpful for improving the amount of time to work with large SharePoint 2010 (or 2007) Content Databases that have grown significantly.
Breakthrough Insight
As you probably already know SharePoint 2010 is where Microsoft’s Business Intelligence (BI) stack is delivered. This is a combination of solutions such as Excel Services, PerformancePoint, Visio Services, Chart Web Parts (Dundas), PowerPivot, and SQL Reporting Services (SSRS). With the release of SQL 2012 a new solution called Power View is now provided, PowerPivot can now be done on SharePoint 2010 server side and there is significantly enhanced integration with SSRS.
Power View
Power View is a new highly interactive data exploration and reporting tool that allows business users to visually explore data, in an ad-hoc fashion. End users can create the reports/dashboards very quickly, create shapes/graphs with clicks, create animation, highlight capabilities based on rules, drill down relationships and performs very well with large datasets. The design environment is very similar to Office. It can be published through SharePoint 2010 as shown in the diagram below.
Note that Power View requires SQL Reporting Services (SSRS) to be integrated with SharePoint 2010 and there be an instance of SQL Server 2012 Analysis Services (SSAS) or PowerPivot be available.
PowerPivot
Up to this point PowerPivot was a solution that was available through Excel client only via an Add-in. This is a really powerful, end user friendly capability that allows for data analytics. There are actually several new capabilities that have been introduced that I recommend you read up on (in references below).
Specifically to SharePoint 2010, PowerPivot for SharePoint is available as an add-in and can run server side in conjunction with Excel Services. This now allows end users the ability to publish PowerPivot reports through the browser allowing end users easy access to this data.

As well, in SharePoint 2010 Central Administration there is a new PowerPivot Management Dashboard that provides several reports on performance of reports.

SQL Reporting Services (SSRS)
Personally the enhancements to SSRS are very exciting to me as I have had to do configuration of SSRS with SharePoint 2007 and 2010 in the past in production environments. If you have done it before, you may recall it was tedious task of configuration the SSRS Configuration Wizard and getting Kerberos Authentication set-up correctly. Now with SQL Server 2012, configuration of SSRS with SharePoint 2010 is completely done through Central Administration. Additionally there is a new service in SharePoint 2010 that runs SSRS, it supports WCF/Claims authentication, integrated ULS logging, built in load balancing across SharePoint servers, report performance improvements and there are PowerShell commands for management.

Additionally there is a SSRS alerting capability that allows end users to subscribe to alerts that are associated to SSRS report. Users can create conditions for any report, and when they are met a notification will be sent to them.

One more last addition change is you may know that SSRS reports can generate Word or Excel Documents. Up to now, the file formats generated were either .doc or .xls which means they are not Open XML document renditions. With SQL Server 2012 and SSRS now .docx and .xlsx file types will now be generated.
Cloud on Your Terms
With SQL Server 2012, there are a bunch of new capabilities to support even tighter integration with SQL Azure. You will have a unified database development experience between SQL 2012 on-prem and SQL Server. The reason why I bring this up is because it is very common that when creating a solution inside SharePoint, you will have complex data management requirements that can be better supported with a SQL Server database instead of a SharePoint list.
Now we have the ability to more quickly and efficiently create custom database in SQL Azure which can be utilized with SharePoint 2010. Plus this is really good for working with Office 365 SharePoint Online because we can make a cloud to cloud connection to work with advanced data structures.
References

• SQL Server 2012 Technical Whitepapers - http://www.microsoft.com/sqlserver/en/us/learning-center/resources.aspx. Specifically read “What’s New in SQL Server 2012 Whitepaper” and “SQL Server 2012 What’s New Breakthrough Insight Whitepaper”.
• SharePoint 2010 SP1 – Supports project Code Name “Denali” which is now SQL Server 2012 - http://support.microsoft.com/kb/2460045
• SQL Server 2008 High Availability with SharePoint 2010 – Previous article that I wrote - http://www.astaticstate.com/2010/12/sharepoint-2010-high-availability-with.html.