Deep Dive into Data Visualization Techniques

What is Data Visualization?

Data visualization is terminology that helps us to understand importance of data by visual/graphical/tabular representation.

Data is time variant and as time passes, data from different sources is collected and processed/analyzed. And, this processed/analyzed data enables decision makers at different levels to gain better visibility on various business aspects such as market trends, organization’s revenue, profit percentage over past years etc.

As common human mentality, people concentrates more on data represented in charts and graphs compare to long pages, bunch of papers etc.
 

Why Data Visualization is Important?

Visualization helps people to analyze data in pictorial or graphical manner. Even the data volumes are very large, patterns and design can be spotted quickly. Visualization convey information easily and it makes it easy to understand.

For example, it is difficult to extract information from normal spreadsheet. It is also time consuming process. Data visualization presents data in a way that, it becomes easy for the user to interpret and analyze data.

Main reason behind the data visualization is that, it provides the past and current trend of data. In a way, it becomes easy for decision makers to take decisions with the help of visualization of historical data.

Selecting Solution for Visualization

As we all know that, if any organization is able to produce and visualize data in effective and correct manner, and if they are able to produce the accurate information then it is the key for doing successful business and enable decision makers to take accurate and right decision.

There are three questions that should be answered before selecting visualization solution:
    1.  What do we want to do with our data?
    2.  Which devices are users consuming the data on?
    3.  Where is our data located?
 

Microsoft Technologies Supporting Tool for Visualization

There are several tools provided by Microsoft that can be helpful in visualizing data in correct manner. All these tools have their own advantages and targeted for specific user’s skill set.

Selection of visualization tool also depends on the organization requirements and the targeted user to whom data will be delivered.

    -  Excel Services
    -  PowerPivot
    -  SSRS
    -  Power View
    -  Power BI
    -  Performance Point
    -  Visio Services

How to Select Right Tool?

There are several use cases that will help organization in selection of tool.

Use Case 1: If user is an Excel Pro. And organization have lot of data and have SharePoint On-Premise and need to provide and share information to many users on intranet.

Solution is Power Pivot:
    -  It is part of the Excel family (Add-in).
    -  We can build a Pivot table from multiple table (multiple sources).
    -  We can build relationships between these tables in GUI.
    -  Enables the ability to quickly process millions of record.

Use Case 2: Organization have SharePoint On-Premise and they want users to do data analysis and discovery on the intranet on their own.

Solution is Power View:
    -  Power View can be used to explore data, to analyze, sort and to filter the data.
    -  It can also be used to discover relationships and to spot any trends.
    -  It provides interactive data.

As we have seen above use cases, but those are just for example. Selection of tool is based on business requirements and needs of users.

Performance Point vs Power Pivot vs Power View vs Excel vs SSRS

1.  Performance Point:
With Performance Point, we have advantage of advanced drill down on complex cube.

It's better to use Performance Point, if our report is based on certain measures and indicator using Key Performance Indicator.

Performance Point reports are supported on iPad, we just need to hold our finger on any chart and our report will be drill down easily.

2.  Power View:
Power View report runs on Silverlight and this can be considered as disadvantage due to it's incompatibility on iPad.

Power View enables self service report creation for end user. So, user can create report based on Tabular Model Cube and Power View will manage connection to model automatically.

If you would like to merely get a grid of information from the source (cube), Power View is that the only one that will do it. Whereas Excel Services provides view into cubes, that needs to be viewed in Pivot Table.

3.  Excel Services:
Excel services provide many rich feature to visualize our data using Pivot Table, Charts, Slicers etc.

The graphing motor of Excel Services is the best out of all the others. It gives the most choices for designing, diagram sorts, 2D/3D graphs, information names, and colors.

4. Power Pivot:
In Power Pivot, we can pull database up to 4 GB into Excel Sheet by creating data model and then, we can use that model to create reports.

So, basically it will allow users to view data in disconnected mode, in other words, user can carry data with them.

5. Reporting Services (SSRS):
In the event that we require a report to be printed to paper, we might need to have tight control over how the report looks and this is the place where SSRS is truly sparkles, it has capacity to make pixel flawless reports and control things like page edges and widths.

On the off chance that we have reports that should be emailed on a timetable, this should be possible utilizing SSRS. This is helpful in the event that we need a day by day or week after week report to be sent via email.

With SSRS,we can export report to DOCX, XLSX, PDF, MHTML formats.

If you have any questions you can reach out our SharePoint Consulting team here.

Sharepoint 2010: Excel Services workbook larger than maximum size allowed to be opened in browser.

Issue: Recently, when I was trying to open Excel workbook from one of SharePoint document library in browser, getting this error: "This workbook is larger than the maximum workbook size allowed to be opened in the browser".

Resolution:
By default, SharePoint as out-of-the-box allows workbook size to be opened in browser is 10 MB. If we try to open it with more than 10 MB size, then will get this error.

As part of solution, we can change the workbook size setting from central administration by following below steps:

1. Login Central Admin as a Farm Administrator.
2. Click on “Application Management” in the left navigation.

3. Click on “Manage service applications” under Service Applications.

4. Click on “Excel Services Application”.

5. Click the “Trusted File Locations” link.

6. Select the Reports library or Document library where the files are (i.e. https://www.binaryrepublik.com/work)

7. Scroll down to “Workbook Properties” and change the “Maximum Workbook Size” as per requirement. (i.e. 50 MB).

8. Save the changes.
9. Now, any workbook up to 50 MB in size from the location can be opened in browser.

If you have any questions you can reach out our SharePoint Consulting team here.

Excel Services Fix – "The workbook cannot be opened".

Error:

When I tried to open any excel file in SharePoint, I was facing below issue: Excel Services – "The workbook cannot be opened".

Cause:

After spending some time on checking configuration settings of Excel Service Application, I found root cause of the issue was permission to Service Account under which Excel Service is running. It does not have enough permission to Content Database of Web Application.

Resolution:

To fix the issue, follow below steps and execute PowerShell Commands.

1. Click Start, click All Programs.

2. Click Microsoft SharePoint 2010/2013 Products.

3. Click SharePoint 2010/2013 Management Shell.

4. At the Windows PowerShell command prompt (PS C:\>), type the following command, and then press ENTER:

$webApp = Get-SPWebApplication –Identity <URL of the Web application>

$webApp.GrantAccessToProcessIdentity(“<insert service account>”)

When you run these commands SharePoint does following actions:

1. Add Service Account user to Content Database of the Web Application. 

(Security > Users).

2. Assign "SPDataAccess" membership role to Service Account user. (Account > Membership)

3. Assign full control permission to the Service Account user on the Web Application.

You can get the “Service Account” details for Excel Service Application from Central Administration. Please follow below step:
1. Go to Central Administration > Security > Configure Service Accounts >
2. Select the application pool “Excel Services Application”.
3. You will find associated Service Account with application in other dropdown.

Reference: http://support.microsoft.com/kb/981293

If you have any questions you can reach out our SharePoint Consulting team here.