Month: October 2013

A Note about “Edit Report” button seen on Power View reports in SharePoint:

Standard

Power View reports that are hosted on SharePoint has “Edit report” which you cannot hide:

Power View Edit report buttonWe can’t hide this button but how do you give someone “read-only” access to Power View?

To do so, you’ll have to host the Power View report in a SharePoint library where a user has read-only access. When a user opens the report, he/she could click on edit report & make changes to Power View report but they won’t be able to save the report instead they’ll see an error:

Power View Access Denied

Official resource: http://office.microsoft.com/en-us/excel-help/power-view-in-sharepoint-server-create-save-and-print-reports-HA102834736.aspx

[update]:

Also, check out this MSDN forum thread, it describes a solution to hide the “toolbar”: http://social.technet.microsoft.com/Forums/sharepoint/en-US/cec0feff-1055-4b53-a5ae-00373abdabdd/how-to-disable-edit-report-option-in-powerview-report-in-sharepoint-2013?forum=sharepointdevelopment

[Notes] Atlanta Business Intelligence User Group – Mapping Data in Power View & Tableau

Standard

Here are my notes from the Atlanta BI user group that I attended today:

Topic: Head-to-Head on Maps: Mapping Twitter in Tableau and Power View

Power View

Tableau

Setup/Installation?Office 2013 [Professional Plus editions], SharePoint 2010 onwards [Enterprise Editions]Public, Online, Desktop, Server
LicensingGet applicable office/SharePoint licenseTableau Public is Free.Tableau Online is $500 per user/year.

Tableau Server has Per-User or Per-Server-Core model

*Note that if you’re using Tableau server’s per-user model the cost is $1000 per named user/consumer (min. 10 users) and $2000 seat for 1 developer.

**Contact Tableau Sales. Please don’t evaluate the product based on the information provided here as the information might change in future

Data Sources[SharePoint] Power Pivot Model, Analysis Service Cubes[Excel 2013] You can connect to data sources that excel supports and then create power view report on top of it.Tableau Public Supports Excel, Access & Text filesOther version of Tableau can connect to a variety of data sources.
Product CategoryAd-hoc reporting a.k.a Self Service Business IntelligenceAd-hoc reporting
Ease of useeasyeasy
Visually Compelling?YesYes
Custom Shape FilesNoLimited
Motion effectNoYes
Product maturityPower View is new to the gameTableau is a leader in data visualization space
Additional NotesPower Map is an add-in by Microsoft that focuses on “Mapping”. It’s part of the newly announced Power BI suite.–

How to sort measure values in an excel 2010 based ad-hoc report using SSAS cubes?

Standard

I helped a user today to sort measure values in his excel based ad-hoc report using SSAS cubes & so I’m posting the tips here for note-keeping. There are two ways to do this & here they are:

#1: Select a cell that has a measure value > Right click > Sort > sort the values in ascending or descending from here:

sorting Excel ad hoc report ssas cube#2: Alternatively, you can also do the same thing by: Select a value from the filed that needs sorting > go to Home Tab > Sort & Filter > from here you should be able to sort data in ascending for descending order:

sorting data excel analysis services cube

Conclusion:

In this post, I shared two ways you can sort the measure values in an excel based ad-hoc report using SSAS cubes. Do you allow excel based ad-hoc reporting over SSAS cubes in your organization?

 

What Problems does Power BI solve?

Standard

I’m at PASS Summit 13 this week and I’m seeing nice amount of excitement among Business Intelligence Pros about “Power BI” so I thought I would post a brief post about problems that Power BI addresses:

#1: Mobile BI:

  • The Visualizations that you’ll publish to Power BI sites would use the HTML 5 rendering & hence the support for Mobile BI.
  • There’s also a native Microsoft Power BI app for Windows 8 so you can use surface tablets for Mobile BI. IOs (apple) or Android native apps have NOT been announced yet.

#2: An end-to-end self-service suite of tools for Power Users:

  • Users will be enabled to search, analyze and visualize data using Power Query, Power Pivot & Power View. Plus it allows them a way to collaborate with each other.

#3: Easier way to search for data that’s available inside & outside for organization:

  • One of the key themes of “Power BI” has been easier discovery of data that’s available to you to analyze.
  • This is really important from an adoption standpoint because with the technologies that we have today, we can’t enable power users to search for “data-sets”. Power BI enables IT to publish Data Catalogs which I imagine would make it easier for power users to search & connect to data sets & start analyzing!

#4: cool tools that people *want* to use it.

  • Power BI has rich user experience.
  • Users can build cool visualizations & create some business value
  • Since this is a “self-service” suite, it seems to be designed as a user-friendly set of tools. This is important because if a user is “confused” or “over whelmed” then they are not going to use the tool & find something else.

#5: Gateway to the future:

  • The Human-computer interaction is evolving. Over the past couple of years, we’ve seen tools like Siri (apple audio powered personal assistant) which allows users to use “Natural Language” to interact with computers
  • Power BI has a tools called “Q&A” that allows users to do business analysis using “Natural Language”. I don’t know the maturity of the current offering but I’m excited about the possibilities that this could offer in future!
  • Imagine a computer (in some amazing futuristic form) and you say to it “sales trend in north america region during past 12 months” and it gives a you nice trend chart that you can use to start analysis.

Cost-benefit analysis of this cloud-powered suite of tools*

  • (Book Mark for future editing: The cost of the tool & its general availability is not announced, so I didn’t talk about the cost-benefits that we might see so I’ll defer this analysis until after the details are announced)

What do you think? What are the problems and pain-points that Power BI is trying to solve?

How to Flatten Data in Excel Pivot Table?

Standard

How to flatten data in Excel Pivot Table?

By default, its hierarchical but I want the data in a Tabular/Flattened format. How do I do that? I am going to show how to do that with Excel 2010.

Default View (Note the Hierarchical view of Pivot Tables)

Before Pivot Table in Compact Form

BEFORE

After I Flattening it, It should look this:

After Pivot Table Tabular Form Flattened

AFTER

Note: it also depends on data, not every data can be represented in the flattened view. In above example, we’re analyzing each product (evident by product id) and so it supports the flattened view.

here are the steps:

1. Select Pivot Table

2. From the Toolbar, Turn off Subtotals. Go to Pivot Table Options > Design > Sub Totals > Do not show Subtotals

3. Go to Pivot Table options > Design > Report Layout > Show in Tabular Form.

Show in Tabular Form Pivot Table

That’s about it. once you do that, you should see data in flattened format.

What is the purpose of creating Tables & Graphs?

Standard

Knowing why we do what we do is important. Stephen Few lists four reason for creating Tables & Graphs in his book “Show me the number”. I really liked them so I am posting it here for your reference:

  1. it helps us communicate. It helps present information to others.
  2. it helps us analyze data. it helps us find the insights in the data.
  3. It helps us Monitor Performance. It helps us keep track information about performance e.g. Sales Performance, Speed of Manufacturing, etc.
  4. It helps us Plan. It helps us predict and prepare for the future.

How to add calculated measures to Pivot Tables using OLAP Pivot Table Extensions add-in?

Standard

Situation:

Power users in your organization/company are developing Excel 2010 reports against OLAP Cubes. They want to add their own calculations for analysis.

BUT

Excel 2010 does not allow end-users to add their own private MDX calculations.

Solution:

A Free Community Excel add-in helps in this case. It’s called “OLAP Pivot Table Extensions”. Here are the steps to download, Install and use it:

1) Check Excel Version (32-bit/64-Bit)

Open Excel > File > Help> About Microsoft Excel

excel version 32 bit or 64 bit

2) Download OLAP Pivot Table Extensions and Install it

Download URL: http://olappivottableextend.codeplex.com/ > Navigate to Downloads Tab & install the right version based on your local excel version.

In my case, I had 64-bit excel so I’ll download the 64-bit version of OLAP Pivot Table extensions.

Excel Pivot Table Extensions

Close Excel and Install add-in

3) Add simple measure

Open Excel.

Connect to Cube.

Let’s add a simple measure that calculates the difference between two measures.

So I created a simple Pivot Table that looks like:

Excel Simple Pivot Table OLAPNow let’s add a calculated measure:

Select the Pivot Table > Right Click > OLAP Pivot Table Extensions

OLAP PIVOT TABLE EXCEL EXTENTSIONS

On the next screen, please enter the name Difference. and the simple formula:

[Measures].[Meausre1]-[Measures].[Measure2]

(please replace measure1 & measure2 with the measure names from your cube)

And click on Add to Pivot Table

Simple Formula OLAP excel calculated memberYou can now see that the calculated measure Difference got added to the Pivot Table!

Excel olap pivot table with calculated memberNote

1) OLAP Pivot Table extensions is not supported by Microsoft. It’s a community software.

2) To maintain the single version of truth (after all that’s why you create cubes/Data-warehouse’s!), it’s recommended that calculated measures that end users want in the cube.

That’s about it for this post! Your feedback is most welcome!

 

How to Change Data Source of an Excel 2010 Pivot Table?

Standard

Problem:

There’s an excel 2010 pivot table that’s getting its data from TestServer. How do I change the data source so that the Pivot Table get’s it data from ProdServer?

Note: This is a common scenario among Business Intelligence Developers who might want to switch between different servers (Test/Prod) to for comparing data.

Solution:

1. Establish the connection to a NEW data source via Data Tab in Excel.

2. Select the Pivot Table whose connection needs to be changed.

3. From the Excel Toolbar. Go to Pivot Table Tools > Options > Change Data Source

Pivot Table Change Data Source4. Choose the NEW connection from here. (you can use the connection that you created in step #1)

Choose the new data source Pivot Table5. Click OK and that should have successfully changed the data source for you. Please test the column names/values that might have been affected since you changed the data source.

That’s about it for this post. Your comments are very welcome!