Showing posts with label Microsoft BI. Show all posts
Showing posts with label Microsoft BI. Show all posts

Friday, March 1, 2013

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


image
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:
image
Online Search lets you Search, Preview and Import Data into Excel.
image
Here is a preview of the List of States and School Superintendent
image
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.
image
You are presented with the Query Panel Window as seen below:
image
image
image
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.
image
With Data Explorer, you can import data from databases such as SQL Server, SQL Database on Windows Azure, Access, Oracle, IBM DB2 or MYSQL.
image
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!
image
Let’s try importing some data from Facebook into Excel.
image
image
Here it is, my facebook posts right within Excel
image
image
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! Smile
image
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:
image

Saturday, January 21, 2012

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

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:

image

Go to Application Management>Create site collection

image

Title: TailspinToysBI

URL:TailspinToysBI

Template>Enterprise: Business Intelligence Center

Primary Site Collection Administrator: administrator

The site is created:

image

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

image

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

image

Create a PowerPivot Gallery:

image

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

image

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:

image

This will take us to the PowerView designer:

image

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

image

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.

image

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.

image

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

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

image

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

image

image

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

image

image

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

image

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.

image

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

image

Under the play axis, drag and drop the month.

Expanding the Chart shows as follows:

image

Note you can animate the chart

image

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

image

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

image

Under Product>Image>select Add as Tile By.

image

image

Save the report and we’re done.

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

image

 

 

Sunday, January 15, 2012

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:

image

Go to Items>

image

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

image

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

image

image

Dashboard Designer should launch:

image

Choose Analysis Services Data Source:

image

image

Specify your analysis services connection:

image

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

image

Go to Properties Time and choose:

image

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

image

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

image

image

Save the workspace by clicking the save as button:

image

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

Now lets create a Sales Performance KPI

image

Right click on PerformancePoint Content and add a new KPI:

image

image

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

image

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

Configure the Sales metric data column:

image

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

image

Adjust Threshold 1 to 85%

image

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.

image

image

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

image

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

image

Configure Profit% Target to 0.03

image

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

image

Click Set Scoring Pattern and Indicator in the Thresholds section/

image

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

Choose Stoplight A – Small and click Next.

image

Observe default worst value and click finish.

image

Save the workspace.

Now lets create a Sales Performance Scorecard.

image

Choose a Blank Scorecard:

image

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.

image

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

image

image

Right-Click Select all descendants.

image

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

image

image

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:

image

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

Here is the final Scorecard:

image

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.