Microsoft launches “Data Explorer" Preview bringing Big Data to Excel

Microsoft announces the availability of Data Explorer for Excel 2013 that enhances the Self Service BI using Excel by simplifying data discovery and access to a broad range of data sources including Private or Public data. “"Data Explorer" provides an intuitive and consistent experience for discovering, combining, and refining data across a wide variety of sources including relational, structured and semi-structured, OData, Web, Hadoop, Azure Marketplace, and more. Data Explorer also provides you with the ability to search for public data from sources such as Wikipedia.”

Get Data from the Web:

Online Search lets you Search, Preview and Import Data into Excel.

Here is a preview of the List of States and School Superintendent

If you have a Web Page that contains a table of information, you can get directly from that web page. Below, I am importing data from the wikipedia for a list of countries by population.

You are presented with the Query Panel Window as seen below:

With the data Explorer, you can import data from other files such as Excel, CSV, XML, Text and from a folder. I like the new import files from a folder.

With Data Explorer, you can import data from databases such as SQL Server, SQL Database on Windows Azure, Access, Oracle, IBM DB2 or MYSQL.

With Data Explorer, you can import data from non-traditional data sources such as SharePoint List, OData Feed, Hadoop (HDFS), Windows Azure Marketplace, Active Directory and Facebook!

Let’s try importing some data from Facebook into Excel.

Here it is, my facebook posts right within Excel

You can also manage Credentials from right within Data Explorer, which was my most desired feature in PowerPivot. I remember tweeting about this many times in frustration of having to repeat providing credentials every time I connect to a data source. Thank you Microsoft Data Explorer team for implementing this!

In summary, Excel 2013 brings more than ever self-service BI capabilities to end users. From the Forrester Research Report from Q2 2012 - “We maintain that in an ideal BI environment, 80% of all BI requirements should be carried out by the business users themselves.”

If you are looking to quickly build out a self-service BI solution that tells the story of your data, you can now use Excel to rapidly mash-up, explore, analyze and visualize any data ranging from a few rows to hundreds of millions of rows.

Excel 2013 comes built in with the all new xVelocity in-memory analytics engine that let’s you analyze data ranging from a few rows to hundreds of millions of rows instantly on your desktop using Excel data models.

What about data visualizations? PowerView provides stunning data visualization where in you can discover new insights with a highly interactive and familiar data exploration, visualization, and presentation experience right within Excel.

Use Excel 2013 Quick Analysis to preview and apply conditional formatting, suggest and create charts, PivotTables, and tables; and using Quick Explore to easily navigate multidimensional and tabular data models.

With PowerPivot you can access, mash-up and analyze data from virtually any source and rapidly create compelling analytical models with PowerPivot. I do see some overlapping features between Data Explorer and PowerPivot as far as data acquisition is concerned, but I consider it as a good thing.

Being a data geek that I am, I feel satisfied with what Excel 2013 has to offer to the large Self Service BI community out there and specifically happy with where Data Explorer is headed.

You can download Data Explorer from the below location:

Microsoft BI: Hands-on with SQL Server 2012 Power View

Tweet

Power View is a Web browser-based report authoring tool targeted at everyone to enable them to create compelling, interactive and rich data visualizations based on Tabular BI Semantic Models. Steps below are based on the SQL Server 2012 hands-on labs.

Start by going to the SharePoint 2010 Central Administration:

Go to Application Management>Create site collection

Title: TailspinToysBI

URL:TailspinToysBI

Template>Enterprise: Business Intelligence Center

Primary Site Collection Administrator: administrator

The site is created:

Under Centeral Administration>Site Settings>Features, ensure PowerPivot Administrative feature is enabled. If not ‘Activate’ it.

To create a new PowerPivot Gallery, Under Site Actions, select More Actions and select

Create a PowerPivot Gallery:

Now it is time to explore the Power View Design Environment.  Let’s start by uploading a PowerPivot workbook to the gallery.

On the Library Tools Documents tab, inside the New group, click Upload Document. Choose Tailspin Toys Sales Model.xlsx from C:\SQL2012DEVKITRCO\Labs\PowerView.

Now it is time to explore the PowerView designer:

This will take us to the PowerView designer:

The first thing we will do is save the report. File>Save as

A Power View report is saved to a SharePoint library using a variation of Report Definition Language (RDL). It is saved as a document with an .rdlx extension.

Back under the Home Ribbon, note that this is a WYSIWYG (What-You-See-Is-What-You-Get) designer that supports an interactive design and data exploration experience. To the right of the report designer is the Field List that it exposes the tabular BI Semantic Model that initially consists of a list of tables.

The icons you see next to the SKU and Product Name fields indicate that they represent values at the lowest granularity of the table. Model developers can mark fields as label fields to help Power View users understand that these fields can be used to see all entries of the table, and to ensure they group appropriately.

Let’s start creating a report by adding a title.

Click on Product and note that the table is added to the report:

Under the Design tab, change the view to Card type and note the change to the report.

Resize the table so that as shown below to make vertical space in the page.

Now let’s add another table for Demographic and Revenue. Click anywhere on the page and choose Product>Demographic and Sales>Revenue.

Now let’s add a Bubble Chart with Sum of Quantity and Product Category. Click anywhere in the designer and choose Product>ProductCategory and Sales>Quantity.

Goto Design>Chart and select Scatter. Fill out the Scatter Chart attributes as follows:

Under the play axis, drag and drop the month.

Expanding the Chart shows as follows:

Note you can animate the chart

Now, let’s add a Tile View to the first table we added to the PowerView report:

On the Card Tools Design ribbon tab, select Tiles. On the Design Ribbon, select Cover Flow under the Tile Visualizations section

Under Product>Image>select Add as Tile By.

Save the report and we’re done.

Here is the final view of the report that shows what is possible with PowerView:

 

 

Microsoft BI: Developing KPIs and Scorecards with Microsoft SharePoint 2010 PerformancePoint Services

PerformancePoint Services 2010 is the next generation performance management application from Microsoft. Delivered as a Microsoft SharePoint 2010 shared application, it provides rich Key Performance Indicator (KPI) and scorecard definitions.

Let’s create a PerformancePoint data source that will connect to Adventure Works DW 2008R2 Analysis Services Cube.

Go to PerformancePoint Content:

Go to Items>

Dashboard library is designed to store dashboards.

Data Connections stores various connection files: PerformancePoint Data Sources, Office Data Connection files and Universal Data Connection files.

Under Data Connections, Go to PerformancePoint Data Source

If Dashboard Designer is not already installed, then you’ll be prompted to run the install:

Dashboard Designer should launch:

Choose Analysis Services Data Source:

Specify your analysis services connection:

The Unattended Service Account means that the credentials configured for the PerformancePoint Services application will be used.

Go to Properties Time and choose:

In the time member association set the time aggregation as shown:

When you save, the connection is saved and visible in the Data Connections library:

Save the workspace by clicking the save as button:

The workspace is an XML document that defines the PerformancePoint item definitions for a particular project.

Now lets create a Sales Performance KPI

Right click on PerformancePoint Content and add a new KPI:

Go to Properties and enter the Name: Sales Performance, Display Folder: KPIs

Change the default Actual and Target to: Sales and Sales Target

Configure the Sales metric data column:

Assign “Reseller Amount” to Sales and “Sales Amount Quota” to Sales Target.

Adjust Threshold 1 to 85%

Now lets add Profit% and Profit% Target by clicking the buttons “New Actual” and “New Target” respectively. In PerformancePoint Services 2010, metrics can be based on existing data sources, existing metrics for the current KPI, or calculations. Calculations allow multiple values to be sourced and combined into a single formula. The Calculated Metric tab lists samples and common formulas.

In the Select a Data Source window, click the Calculated Metric tab and select Gross Profit Margin.

Map GrossProfit to AdventureWorks>Reseller Gross Profit and NetSales to Reseller Sales Amount

To adjust the formula, in the Formula box, remove the * 100, and then click OK

Configure Profit% Target to 0.03

In the Format Number window, select Percentage in the Format list and apply Percentage format (2 decimals) to Profit% and Profit% Target.

Click Set Scoring Pattern and Indicator in the Thresholds section/

In the Edit Banding Setting window, review the default scoring pattern and method, and then click Next.

Choose Stoplight A – Small and click Next.

Observe default worst value and click finish.

Save the workspace.

Now lets create a Sales Performance Scorecard.

Choose a Blank Scorecard:

Change the Scorecard name to “Sales Performance Scorecard”.

From the Details pane on the right, drag and drop the KPI we created into the Scorecard.

From the Dimensions, drag and drop Sales Territory>Sales Territory into the Scorecard:

Right-Click Select all descendants.

To view the data, go to Edit>View>Update

To remove the Profit% metric, right-click the Profit% cell, and then click Delete. To modify the Profit% Target metric, right-click the Profit% Target cell, and then click Metric Settings. Change as shown below:

Change the font color of Territory Dimension Members to blue and Save the Score Card in the Workspace.

Here is the final Scorecard:

Summary:

We learned how PerformancePoint items are catalogued and managed in SharePoint Server 2010. We created and configured data sources. We created and configured Key Performance Indicators (KPIs) and we created and configured a scorecard.