Add New Skills!

Udemy
Showing posts with label PowerPivot. Show all posts
Showing posts with label PowerPivot. Show all posts

Thursday, June 21, 2012

Country and Region Reference Data on Azure Data Market

Let me show you a quick way to setup Country and Region for your Data Warehouse or Data Mart. Normally, you expect to find this kind of reference data within your organization in some source system or even better in a MDCP (Master Data Control Point).

If you can’t find this data anywhere, then check out the Azure Data Market.

You can sign up for free here and browse a number of interesting datasets. One of them is the World Development Indicators. Not all the data sets out there are free, but the World Development Indicator is free.

Once you signup, you can go down to Explore the Dataset

image

You will see a query panel:

image

Check out the GetCountries dataset

image

I highly recommend you install the Excel Data Market Excel addin that will make this a breeze:

image

So here is how it looks in Excel:

image

Pass parameters, if any and you are done. The parameter I used is en or EN for english.

Here is the result:

image

There are some aggregate information in there that you can clean up. Also, you may want to clean up the (all income levels).

I am not recommending this as the best way of doing it, but if you find yourself with no reference country, region data, then give Azure Data Market a try.

Saturday, March 10, 2012

What is PowerPivot for Excel 2010?

PowerPivot for Excel is an Excel 2010 add-in that allows users to pull data from multiple sources, mash them up, and then build reports using regular pivot tables. You can even share these reports with others in Microsoft SharePoint (via PowerPivot for SharePoint). In this demo, Julie Strauss, Program Manager for Microsoft SQL Server Analysis Services, shows just how easy it is to get a better view into your data.

Find a nice post

 

Monday, February 20, 2012

Extracting Netflix Data using PowerPivot

Today I played around with Netflix data after extracting it using PowerPivot for Excel addin.

Netflix OData feed: http://odata.netflix.com/v2/Catalog/

I wasn’t able to create a relationship between the entities Titles, Genres, People, TitleAudioFormats etc. The Titles data apparently contains duplicates in its TitleId column and the entities were missing the key attributes required for setting up a relationship.

Anyway, here is a shot at it:

image

Drill to detail:

image

You can download the file here. You’ll need Excel 2010 for the slicers to work. Due to the volume of the data, I only extracted Titles with release date greater than or equal to 1997.

https://skydrive.live.com/redir.aspx?cid=d3aff4f279dff93d&resid=D3AFF4F279DFF93D!704&parid=D3AFF4F279DFF93D!701&authkey=!AD-0WHa9FqcIU3U

You may be prompted to download this file as the file is ~40MB.

 

 

Sunday, February 19, 2012

Using Azure Data Marketplace and PowerPivot to Build Date Dimension

In this post, I describe how to quickly build a Date Dimension for your data warehouse by leveraging the Azure Data Marketplace and Rather than building the date table yourself, you can just download the date table from the Azure Data Marketplace using PowerPivot.

Start by signing up at the Azure Data Marketplace https://datamarket.azure.com/. Windows Data Azure Marketplace is envisioned to be the One-Stop shop for premium data and applications.

Next search for DateStream service.

image

The DateStream service provides all the dates from Year 1900 to 2100. You probably will not need all of the data, but that can be easily filtered out once you have all of the data downloaded. If you are not interested in extracting ~200 years worth of dates, you can apply filters at the source and extract only what you are interested in. Check out this post: http://www.bp-msbi.com/2011/10/range-queries-with-azure-datamarket-feeds/

The DateStream feed currently consists of a number of date tables:

  • BasicCalendarEnglish
  • BasicCalendarUS
  • BasicCalendarDanish
  • BasicCalendarHebrew
  • BasicCalendarGerman
  • BasicCalendarBulgarian
  • ExtendedCalendar

You’ll need to subscribe to this data set. Once subscribed, you will be able to explore this data set right inside the marketplace.

image

image

I will download a connection to Excel PowerPivot which will download a ServiceQuery.atomsvc file. You could choose to download just a CSV file if you want.

image

Doubleclick on the ServiceQuery.atomsvc file and that should open the table import wizard. Provide your account key and proceed importing the data.

image

After you import the data:

image

After I downloaded this data, I created a count measure as follows: cnt:=COUNTROWS(DateStream). The table contained 73,414 rows since I didn’t apply any source filters.

I also created an additional column in the table and called it: MonthName2 with the following DAX: =[MonthOfYear]& " - " & [MonthName]. This will help me create a date hierarchy and sort the months in the right order. I switched to the “Diagram View” to create a date hierarchy – simply right click and create a hierarchy and drag and drop the columns that form the hierarchy.

image

Next, I created a Pivot table in Excel:

image

You don’t have to create a pivot table if all you are interested is building a Date table in your database. You should already have the data in CSV or Excel that you can import into your relational database.

Saturday, February 18, 2012

Microsoft BI: PowerPivot hosted Demos

Today I stumbled upon a site that has hosted some really cool PowerPivot demos.

Go https://insights.hostedpowerpivot.com/sites/Demo/Pages/default.aspx and browse the sample PowerPivot apps below.

Here is one that I created using Pluralsight’s OData feed. I uploaded it to Skydrive, but Skydrive doesn’t support links to data sources, so you’ll need to download it to see it in action.

image

Thursday, February 16, 2012

Analytics using PowerPivot and OData

With PowerPivot you can quickly extract data from a wide variety of data sources and built impressive looking reports right within Excel. In this post, I will extract data from an OData feed made available by Pluralsight training website.

I am on Windows 7 platform with Excel 2010 and .

I will start with a new Excel Spreadsheet and launch PowerPivot. In PowerPivot, click on “From other data sources” and click on “Other Feeds”.

image

Click Next and enter the Data feed URL: http://www.pluralsight-training.net/odata/

image

Here is a list of all OData producers: http://www.odata.org/producers

Click Next and it should come back with the source tables that are exposed in this feed:

image

You can give new friendly names to these source tables. This looks good to me, so I will go ahead and click Finish. This should extract the data into PowerPivot tables:

image

image

Take some time to explore and get familiar with data as the next step involves building a relationship.

Click on the Design View in PowerPivot:

image

Here is the view: note that there is no relationship that currently exists. This is what we will work on next.

image

To create a relationship between Modules and Authors, right click on Modules and choose “Create Relationship” and click “Create”.

image

image

This will create the relationship between Modules and Authors using the Author/AuthorName.

image

Similarly, I can go ahead and create the relationship amongst all the entities as seen below:

image

Next we need a Date Table to be our time dimension.

The Date Table can be built easily in Excel. Use a new sheet to generate date data. By exploring the data, I realized that data goes back to 2008. So, enter 1/1/2008 and drag the cell until you cover 2008, 2009, 2010, 2011 and 2012. This should give you about 1827 rows. Next create the formulas for Year, Month, Quarter and Month Name:

Year: =YEAR(A2)

Month:=MONTH(A2)

Quarter: =IF(C2<4, "Q1", IF(C2<7, "Q2", IF(C2<10, "Q3", "Q4")))

Month Name: =IF(C2=1, "Jan", IF(C2=2, "Feb", IF(C2=3, "Mar", IF(C2=4, "Apr", IF(C2=5, "May", IF(C2=6, "Jun", IF(C2=7, "Jul", IF(C2=8, "Aug", IF(C2=9, "Sep", IF(C2=10, "Oct", IF(C2=11, "Nov", IF(C2=12, "Dec"))))))))))))

Here is a partial screenshot:

image

Now, you need this as a Date Table in PowerPivot. There are two options to do this: (1) Click “Create Link Table” under the PowerPivot ribbon item in Excel (2) Copy and Paste directly into PowerPivot.

Here is the Date table in PowerPivot:

image

Before we setup the relationship between the Date table and Modules, we need to fix the PublishDate column in Modules because it contains the Date and Time components in it. In order for our join with Date table to work, we only need PublishDate. Rename PublishDate to PublishDatetime and create a new column PublishDate in Modules table with this formula: =DATEVALUE([PublishDateTime])

image

The next step is to create the relationship which can be created as follows:

image

Another thing to do is to create a Date hierarchy as follows:

Right Click>Create Hierarchy

Give a name to the hierarchy

Drag and drop the levels of the hierarchy

image

Almost there with the data model, we just need to create a couple of new measures: (1) Total Modules (2) Total Courses. You could possibly create more measures with the data we have, but we are good for now.

Switch to Data View in PowerPivot and go to Modules table. Create a new measure as follows: Total Modules:=COUNTROWS(Modules)

image

Next, In the Courses table: Create Total Courses:=COUNTROWS(Courses)

With that we are now ready to create our reports.

Create a new PivotTable>Chart and Table (Horizontal)

image

You get a blank slate. You can now start to drag and drop Objects from the Pivot Field List:

Drag and Drop the Course attributes as follows:

Table:

Row Labels: Category, Title

Slicer Vertical: Category

Values: Total Courses

Chart:

Axis Fields: Category

Values: Total Courses

Here is the view I get:

image

Let’s take a few minutes to beautify the table and the chart:

Table: Highlight the table and choose the table format you like:

image

Chart: Highlight the chart, go to Design and apply the template you like:

image

You can hide the Axis Field Buttons on the Chart since we already have a slicer setup which is a much better way of slicing-dicing the data.

image

That’s it. Feel free to explore with Pivot Tables and Chart and create reports for the other Entities in PowerPivot database.

Here is the final Spreadsheet that I ended up creating: https://skydrive.live.com/redir.aspx?cid=d3aff4f279dff93d&resid=D3AFF4F279DFF93D!703&parid=D3AFF4F279DFF93D!701

Good Luck!

Saturday, January 14, 2012

Microsoft BI: Defining DAX Calculations with Excel 2010 PowerPivot

DAX stands for Data Analysis Expressions is designed to deliver easy-to-use constructs that allow extending the base PowerPivot model with calculations. With DAX you can define two types of calculations: (1) Calculated columns in PowerPivot Tables (2) Measures that define new fields in PowerPivot task pane.
If you’d like to follow a step-by-step tutorial on DAX, be sure to check out the labs > Defining DAX Calculations with Microsoft PowerPivot for Excel 2010.

Let’s create some DAX calculations in this existing Excel 2010 report:

image

Go to PowerPivot>PowerPivot Window and note that we have the following tables with already extracted data from AdventureWorks SQL database:
[US Sales], [Product], [Category], [Subcategory], [Date], [US Geography], [US Population]



image

Add a new calculation DAX:=[SalesAmount] - [TotalProductCost]
Note support for intellisense in PowerPivot:

image

Create StateCode2 DAX as: =[StateCode]

image

Create a Relationship with US Population table:

image


image

Note: that PowerPivot does not support creating more than one relationship for a column. That’s why we are created a State2 DAX Calculation.
You can hide columns from showing in the Pivot Table by right clicking on the column and choosing Hide Columns>From PowerPivot:

image

Go to US Population table and create a new DAX Calculation:=SUMX(RELATEDTABLE('US Sales'), 'US Sales'[SalesAmount]) / [Population]

image 

Note: RELATEDTABLE function to retrieve all related rows based on the relationship created in the previous task. The SUMX function is then used to aggregate the SalesAmount column of the related rows. The aggregate result is then divided by the state’s population in order to compute the sales per capita.

To switch back to Excel:
image

Note that you need to click on the Refresh Button to see the new calculations:
image

Add GrossProfit to the report. In order to change the name and the formatting, right click on any cell under GrossProfit and choose “Value Field Settings”:

image

Change the name of the measure to Profit:

image

Apply a Number Format to Gross Profit to Comma Separated with Zero decimal places:

image

Lets apply some conditional formatting:

image

image

image

Now let’s add a new Measure to the PivotTable by going to PowerPivot ribbon and selecting “New Measure” and add the below DAX:=SUM('US Sales'[GrossProfit]) / SUM('US Sales'[SalesAmount]) . Note that Intellisense works inside the Measure Settings Dialog.

image

Rename Measure to Profit% and apply Percent (%) formatting:

image

Apply conditional formatting as we did earlier:

image

Looking for some hands on practice without installing the tools? Check out SQL Server Virtual Labs: http://www.microsoft.com/sqlserver/en/us/learning-center/virtual-labs.aspx
Wondering what is PowerPivot, check out: http://www.microsoft.com/en-us/bi/powerpivot.aspx