Project Botticelli

Microsoft Business Analytics with Office 2013, SharePoint 2013 and SQL Server 2012 Purchase this course

2 March 2013 · 10 comments · 6861 views


Microsoft Business Analytics with Office 2013, SharePoint Server 2013, and SQL Server 2012

Microsoft Business Analytics combines Business Intelligence with Data Warehousing and Big Data Analytics. This 1-hour 20-minute video by Rafal Lukawiecki introduces this concept by showing you how real-world analytics, created by a power user in Excel 2013, can be shared with others in a company, using SharePoint Server 2013, and subsequently scaled-up to the needs of an enterprise, by means of a SQL Server 2012 SP1 Analysis Services Tabular Model. Rafal covers the entire subject broadly, showing you some of the new Excel 2013 BI features, such as Quick Explore and the Timeline, and by switching between the technologies as necessary, in an extensive, 9-part, 55-minute, high-def demo, which forms the core of this video tutorial.

Since good BI is essential to business analytics, you should help your users by focusing it on the correct information, filtering out data that is not applicable to the user’s context, and by simplifying the presentation of the information in an attractive format. The new technologies available in Excel 2013, such as Power View, Data Model, and PowerPivot, make it possible to present complex analytics in attractive formats, easily. You will see how to present data on an interactive, user-zoomable Bing map of the world without even having to know the actual longitudes and latitudes of your data points, since Power View is capable of automatically geocoding your data from a name of a city, region, country, or a continent, by querying Bing on your behalf. Best of all, aggregations are automatically calculated at all the required levels of a geographical hierarchy.

There are two ways to share analytical workbooks, created in Excel 2013, with others in your organisation. The easiest, but not necessarily the most manageable way, is to simply upload an Excel 2013 workbook to your SharePoint 2013 server, which must be running SQL Server 2012 SP1 (or later) Analysis Services (SSAS) in SharePoint Integrated Mode (aka the PowerPivot mode). SharePoint 2013 is able to render Power View contained in an uploaded workbook as long as you use the Excel Services component, rather than the Excel Web App.

In order to secure a workbook by defining roles which specify which rows of data should be visible to certain users, or to partition it for better update performance, or to take advantage of ability to work with more than 2 billion rows of data, you need to convert your Excel data model to a tabular model residing on SSAS. This is much easier to do than it sounds: simply create a new Tabular Project using SQL Server Data Tools (SSDT), indicating that you wish to import a PowerPivot-containing (that is Excel 2013 Data Model or an Excel 2010 with PowerPivot) workbook, or use SQL Server Management Studio (SSMS) to restore from a workbook. You can see this process in the demo, showing you a realistic, 1 GB data model, containing some 32 million rows.

Once you have a tabular model on an SSAS server, using it is very easy. You can create a BI Semantic Model Connection File (BISM Connection File) in SharePoint for your users. You will see how to create a zoomable geospatial map report, and an animated bubble chart (scatter plot) in the demo, showing you how those attractive visualisations can be used to analyse data by means of simple clustering, and how to export it to PowerPoint and use it as static or fully interactive slides in a presentation, or use as a dashboard, created using the Page Viewer SharePoint web part.

Rafal summarises this tutorial by briefly discussing the underlying in-memory technologies: xVelocity for In-Memory Analytics Engine, and the upcoming “Hekaton” for transactional, in-memory processing. You will also hear a few comments about the use of data of any size, including the fashionably-termed Big Data for your analytics, by means of Microsoft HDInsight technology which implements the Apache Hadoop distributed processing system, although this will be covered in another module in more detail.

Log in or purchase access below to the premium version of this content.

Purchase A Full Access Subscription

Subscription Best Value


Access all content for 1 year.
  Purchase on Tecflix

  • Payment is instant and you will receive a tax invoice straight away.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.


hxy0135 · 6 March 2013

Dear Rafal,
In the video you only talked about Power view and SSAS Tabular Model. Can Power View in SharePoint 2013 talks to Multi-dimensional cube?


Rafal Lukawiecki · 6 March 2013

Hi Hua, thanks for asking. Power View, for now, can talk to multidimensional data, as long as you flatten the cube. What I mean by that, is that when you build a tabular model, using Excel PowerPivot or SSDT, you need to use the option to add data from Microsoft Analysis Services. After you have connected to your cube, you are presented with an MDX query entry window, and you can use the Design button to build a query visually. You need to extract the dimensions and measures that interest you from your cube using that query. This is identical to how you would get data from a cube in Report Builder 3.0. Once you have done that, your tabular model allows you to use this data, of course, in Power View, but, effectively, you have flattened the cube, as all the data is now tabular. Click here to be taken to the bit (37:06) in the video where I show that option in SSDT.

Microsoft are currently working, however, on something provisionally called DAXMD, which will enhance Data Analysis Expressions so it becomes possible to fully converse with multidimensional data. Once this work has been done, you will be able to natively connect Power View to a cube, without having to flatten it. The key advantage of that, if and when this ships, of course, will be to have a fully navigable OLAP hierarchy, plus all the performance benefits (if any) of a cube-based solution and a way to inject new life into your former investment. However, much of what is there today is already quite sufficient for visualising hierarchical data, maybe just not as convienient as it should be. There is a CTP of this technology already available.

hxy0135 · 6 March 2013

Thank you Rafal.

vavasjc · 30 September 2013

Rafal, I think that the solution of using Power View + Maps is nowadays limited to just some countries, as Bing can't locate several streets in my country (Brazil), and so it would be unsuitable to use it as a solution to, for example : Mapping the top resellers of a certain product. Can I try another approach, maybe storing at DimGeography latitude + longitude and try to use it to force Bing to locate the right spot over the map?

Project Bottice... · 10 October 2013

Apologies for the delay in answering you—I was away from office for 3 weeks. Indeed, you can force a specific geographical longitude/latitude to be used for displaying your data. You need to geocode your geolocations so that those two coordinates are available, for example, as you suggested, as attributes of a DimGeography dimension, and then use them as the values for the Longitude and Latitude Power View fields in your report.

stillspring · 30 April 2014

Hi Rafal,
Thanks for the descendant videos.
I knew the data type of Longitude and Latitude is not suitable for Multidimensional Cube, could those be processed in Tabular mode?

Hope to see more videos. May I request a section on how to process cube and dynamically partitions. Do we have forum, so all users can ask questions? Thanks in advance.

snazaire · 20 October 2014

For some reason Rafal, I created a column in the product table by using the following function:
and it returned nothing. Not even an error message.
Is there something else that I might need to do?


Rafal Lukawiecki · 15 January 2015

Stan, make sure you have the necessary relationships between the tables. If this did not work, please contact me by email and send the workbook that failed you.

Scott McKechnie · 15 January 2015

Thank you so much for this video, this is exactly what we are trying to achieve in the the work place, I am totally blown away.

Rafal Lukawiecki · 15 January 2015

Many thanks, Scott, I am glad you have found the video inspiring. I aimed to show how a modern, collaborative team workplace could function, by using bits of Office, Power BI, and SharePoint. I hope you manage to benefit from some of those ideas.