SharePoint has become a de-facto platform for storing and sharing business data in lists, document libraries etc. But unlike the ease of reporting and running statistics on relational database, SharePoint on its own does not provide such flexibility when it comes to extracting business intelligence. Due to this limitation, often, managers might find themselves spending a lot of time and resource in exporting data to spreadsheet, and performing manual computation and parsing. Some organizations even use specialized data professionals and a dozen different software packages, just to produce simple reports. Worst, if the report doesn’t have the required information, you will have to start over, wasting precious time.

As there is a time and expense involved in getting answers from SharePoint lists, a lot of business intelligence information often goes unused, due to that fact that, SharePoint is designed to store data, and not to help you analyze it.

Mining intelligence and insights from data stored in SharePoint

The AssistMyTeam OLAP tool for SharePoint, is designed to give you a simple and yet powerful tool to configure OLAP cube from your SharePoint lists, and then analyze and create reports straightaway, out of the box. It is perfectly suited to spot new trends and discover unknown problems from your data that are stored in SharePoint lists.

In the OLAP SharePoint Manager tool, you can enter the URL of your SharePoint site (e.g.. http://companyweb/) to show up all the available lists in a hierarchical tree from which you can select a particular SharePoint list to configure for OLAP Cube. Alternatively, you can also enter the URL of the particular SharePoint list (e.g.. http://companyweb/Lists/Tasks/AllItems.aspx) to directly load the available fields in the grid for cube configuration. With either techniques, you can choose which SharePoint fields to include in the cube. You can also set aggregate function for the measure fields or define new derived fields such as a hierarchy or a calculated field.

You can add your own derived OLAP fields such as a composite hierarchical field (e.g.. Project-Task) or a calculated field (e.g.. Total Project Budget), based on existing SharePoint fields. For easy recognition, derived fields are distinctly colored and highlighted (in green).

New OLAP composite dimension – E.g. Project – Task

New OLAP calculated member – E.g. Total Project Budget

Once you have chosen which fields or dimensions to include in the statistic, you can select aggregate functions such as count, distinct count, sum, average, maximum or minimum for those numeric or currency fields, so as to set them as ‘measures’ in the OLAP cube.

When you click the ‘Run Statistics‘ button, it would generate a cube, which will be loaded in the OLAP Statistics tool that consists of the Grid and Chart Views as shown below. This OLAP Client tool, consists of the Grid and Chart Views. On the left is the Cube structure – measures and hierarchies as a tree. The measures are grouped in the set, displayed in the branch. All the rest of the tree nodes are the dimensions that contain hierarchies. You can then drag dimensions (fields) from the cube structure to the pivot areas (Columns and Rows areas), and then select a measure or two from the cube, and drag it to the values area to generate the statistics.

OLAP Grid

The OLAP Client Grid view (above) is comprised of a multi-dimensional table with expandable nodes. These nodes group and display data according to the hierarchies used to define the measures and dimensions upon which the underlying data has been organized. In tandem with the OLAP Chart view, the OLAP Grid view provides an ideal means of clearly conveying data to the user. It is highly navigable and quickly provides detailed information to the user. The speed with which data recall occurs and the strong formatting the grid enforces ensure data is always presentable and easily understandable. Consequently, OLAP Grid views can easily be ported to spreadsheet applications for report compilation purposes.

You can navigate through these dimensions by drilling down, rolling up, or drilling across. You can drill down to access the detailed level of data, or roll up to see the summarized data. You can roll up through the hierarchy levels of dimensions or to specific characteristics or data elements (columns) of the dimensions. You can also drill across dimensions to access the data of interrelated dimensions. In addition, you can set one of these powerful computational functions such as sum, averages, distinct count, maximum, minimum etc. for the measure field.

OLAP Chart

While the Grid view (below) allows working with numbers, chart view allows representing your data graphically. This gives you and your team a unique opportunity to analyze data visually, dealing with charts rather than numbers, which is much easier to perceive. Now it’s easier than ever to spot new trends and discover unknown problems in your data flow. The statistical tool will help you gain an insight into your data and make new discoveries.

Exporting to file

After a particular snapshot of the statistics is achieved, If you need to share or publish the statistical findings, export it to image, PDF, Excel etc, or print it.

 
You also have the options to save the pivot settings to file, for accessing the same snapshot in future. If your database is located on a network, you can also save the cube data to file such that you can work offline with the cube, even when the database is not available, or when the network is disconnected.

Benefits

AssistMyTeam OLAP tool for SharePoint provides managers with the unique ability to explore large complex data from your SharePoint and allows displaying in grids, charts and graphs and support most common operations such as pivoting, drill down/slice and dice, filtering etc. With such arrays of information, you and your management team can re-engineer your business processes, reinforce resources and forecast problem areas and exploit all these factors for competitive advantage. With the capability of complex calculations, trend analysis and sophisticated data modeling, and reporting, OLAP tool helps you to identify critical information on your not so obvious data and extract mission critical information and intelligence that will enable better decision – in your business.

To get more info about the tool, you can visit – https://www.assistmyteam.net/OLAPSharePoint/

 

Managers often find it difficult to get the correct insights from their emails, appointments or tasks that are tagged with projects or some business data. Though Outlook views are excellent way of presenting information in a more obvious way in folder, it does not give you the summarized data that one needs often, to see broader trends based on aggregation, and to see these trends broken down by any number of variables. Given that the generalization of information using Outlook views are constrained by a few fields (or dimensions) at a time, it is hard to fully evaluate a complex set of answers without the ability to inspect each dimension in detail, while at the same time, preserving context eliminating all guesswork.

Mining intelligence and insights from Outlook emails, appointments or tasks

The AssistMyTeam OLAP tool for Outlook is designed to give you a simple and yet powerful tool to configure OLAP cube from your mailbox, and then analyze and create reports straightaway, out of the box. It is perfectly suited to spot new trends and discover unknown problems from your Inbox, Calendar or Task folders. The tool allows you to define any field as the measure with different function – sum, count, distinct count, maximum, minimum etc, against which statistics is to be executed. Real strength of this tool is its ability to examine and view data in ways not ordinarily possible. By allowing varying levels of granularity during data inspection and visualization, a lot of information can be revealed that would otherwise be hard to attain. Now it is easier than ever to spot new trends and easily answer many of the perspective information which otherwise are hidden.

AssistMyTeam OLAP tool for Outlook

On every outlook folder, you will find the OLAP toolbar, containing two buttons – first one enables you to choose which fields or dimensions (including user defined ones) to include in the statistic. It also lets you define which fields will act as ‘measures‘ in the function column. Selected fields and other settings are saved for that specific folder so that when you come back to this folder next time, it will show the same selected fields.

AssistMyTeam OLAP Client tool

As seen in the screenshot above, user-defined fields are distinctly highlighted for easy recognition. For example, States, TotalCost, RegionWise are user-defined fields in this task folder. You can also add new OLAP fields – a composite (also known as hierarchy) or a calculated field, based on the existing member fields of Outlook. OLAP fields are also colored differently for easy recognition (e.g., green)

New OLAP composite dimension – E.g., Projects per Country

New OLAP calculated member – E.g., Projects Total Cost

Then you can run the statistics tool from the toolbar or from the fields selection dialog. Depending on the number of folder items, it might take a few seconds to generate the cube from the Outlook folder, before loading the OLAP Statistics tool, as shown below.

When you click the ‘Run Statistics‘ button, it would generate a cube from the particular Outlook folder (e.g., Inbox or Calendar), which will be loaded in the OLAP Statistics tool. This tool consists of the Grid and Chart Views. On the left is the Cube structure – measures and hierarchies as a tree. The measures are grouped in the set, displayed in the branch. All the rest of the tree nodes are the dimensions that contain hierarchies. You can then drag dimensions (fields) from the cube structure to the pivot areas (Columns and Rows areas), and then select a measure or two from the cube, and drag it to the values area to generate the statistics.

OLAP Grid view

The OLAP Client Grid view (above) is comprised of a multi-dimensional table with expandable nodes. These nodes group and display data according to the hierarchies used to define the measures and dimensions upon which the underlying data has been organized. In tandem with the OLAP Chart view, the OLAP Grid view provides an ideal means of clearly conveying data to the user. It is highly navigable and quickly provides detailed information to the user. The speed with which data recall occurs and the strong formatting the grid enforces ensure data is always presentable and easily understandable. Consequently, OLAP Grid views can easily be ported to spreadsheet applications for report compilation purposes.

You can navigate through these dimensions by drilling down, rolling up, or drilling across. You can drill down to access the detailed level of data, or roll up to see the summarized data. You can roll up through the hierarchy levels of dimensions or to specific characteristics or data elements (columns) of the dimensions. You can also drill across dimensions to access the data of interrelated dimensions. In addition, you can set one of these powerful computational functions such as sum, averages, distinct count, maximum, minimum etc. for the measure field.

OLAP Chart view

While the Grid view (below) allows working with numbers, chart view allows representing your data graphically. This gives you and your team a unique opportunity to analyze data visually, dealing with charts rather than numbers, which is much easier to perceive. Now it’s easier than ever to spot new trends and discover unknown problems in your data flow. The statistical tool will help you gain an insight into your data and make new discoveries.

Exporting to file

After a particular snapshot of the statistics is achieved, If you need to share or publish the statistical findings, export it to image, PDF, Excel etc, or print it.

You also have the options to save the pivot settings to file, for accessing the same snapshot in future. If your database is located on a network, you can also save the cube data to file such that you can work offline with the cube, even when the database is not available, or when the network is disconnected.

Benefits

AssistMyTeam OLAP tool for Microsoft Outlook provides managers with the unique ability to explore metadata and project information stored in emails or appointments in multidimensional view in the form of grids, charts and graphs. With support most common operations such as pivoting, drill down/slice and dice, filtering etc., you and your management team can re-engineer your business processes, reinforce resources and forecast problem areas and exploit all these factors for competitive advantage.

To put in perspective, this OLAP tool can:

  • Represent Outlook fields (including custom fields) as OLAP dimensions, which are naturally interrelated through functional subject areas.
  • Provide an OLAP Grid view with support for a multi-dimensional table with expandable nodes. Unique features of control allows for building the OLAP-reports of the exceptional level of complexity.
  • Provide an OLAP Chart view that allows representing the data graphically. This gives you a unique opportunity to analyze your business data visually, dealing with charts rather than numbers, which is much easier to perceive.
  • Enable you to navigate through these dimensions by drilling down, rolling up, or drilling across – drill down to access the detailed level of data, or roll up to see the summarized data. Roll up through the hierarchy levels of dimensions or to specific characteristics or data elements (columns) of the dimensions. Drill across dimensions to access the data of interrelated dimensions.
  • Provide powerful computational functions such as sum, averages, distinct count, maximum, minimum etc. for the measure field (when creating the cube).
  • Allow you to save the current state of the statistics in the Grid/Chart working area to a report view file, with specific member fields on the pivot panels (Rows and Column areas) and measure fields in the values area. You can use this report view file in future, to retain back the same state of statistics.
  • Support exporting reports to a wide range of formats including Excel, Adobe PDF, XML, bitmaps or web pages.

To get more info about the tool, you can visit – https://www.assistmyteam.net/OLAPOutlook/

 

I have been working on Business Intelligence and OLAP technologies for more than 3 years, extensively. Initially the very sound of it, gives jitter or boredom, and it was a less favorite subject during my academic year. When I enter the workforce after my masters’, never did I expect to work on OLAP extensively as part of my new job. But with time, I soon find myself digging more into OLAP technologies and eventually, became a love affair, and till today resonates in my entrepreneurial pursuit. In fact, every enterprise products that I have developed on Microsoft Office, Exchange and SharePoint platforms integrate an inbuilt OLAP solution to analyze business data and generate reports, that fits every level of management and accounting requirements.

Okay, now to the purpose of this blog, the new release of ‘OLAP Statistics and Reporting tool for SharePoint list‘. For those of you, who are not aware of my products portfolio, I also have two other OLAP tools – one for Microsoft Outlook and the other for Microsoft Access. Each of this OLAP tool is designed to serve a purpose, which is to extract business intelligence out of the Outlook or Access data.

Before digging down more on the new OLAP tool for SharePoint, let me put forward the reasons on why these specific OLAP tools were developed in the first place.

For Outlook, there is hardly any specific OLAP tool that one can use to analyze Outlook data. To some extent, one can use Outlook views of presenting information in a more obvious way in folder, but it does not give you the summarized data that one needs often, to see broader trends based on aggregation, and to see these trends broken down by any number of variables. This leads to the development of what is now known as ‘OLAP Statistics and Reporting for Outlook‘.

For Access database, there is no inbuilt OLAP tool in Access that one can use to analyze aggregated and summarized data. Traditional query (Or OLTP) is slow in aggregation task, provides limited interactivity, and reporting is well suited to handle textual information mostly. Moreover, complex calculation are oftem difficult to implement. In short, there are major drawbacks with regards to answering, analysis and reporting with Access database. One can use Microsoft Excel to create an OLAP cube, and analyze it. But the process is cumbersome, and present a learning curve, for average workers and managers. The absence of a simple, and yet productive OLAP tool for Microsoft Access database leads to the development of what is now known as ‘OLAP Statistics and Reporting for Access‘.

OK, now coming to the new OLAP tool for SharePoint list. SharePoint can store business data in lists, such as meetings, time-sheets, contacts, tasks, announcements, sales transactions etc. The main purpose of storing it on SharePoint list is to enable sharing  among team members in organizations, which is its selling point. However, one of its weak point is the absence of any inbuilt OLAP tool to analyze data in the list. Due to this limitation, often managers find themselves spending a lot of time and resource in exporting data to spreadsheet, and performing manual computation and parsing. Some even use specialized data professionals, web parts and a dozen different software packages, just to produce simple reports. Worst, if the report doesn’t have the required information, you will have to start over, wasting precious time.

As there is a time and expense involved in getting answers from SharePoint lists, a lot of business intelligence information often goes unused, due to the fact that, SharePoint is designed to store data, and not to help you analyze it. This leads to the development of ‘OLAP Statistics and Reporting for SharePoint‘, to let you configure OLAP cube from your SharePoint lists, and then analyze and create reports straightaway, out of the box.

Summary:
Product Title: OLAP Statistics and Reporting for SharePoint
Home Page: http://www.assistmyteam.net/OLAPStatisticsSP/
Requirements: OLAP Statistics and Reporting for SharePoint works with WSS 2.0, 3.0, MOSS 2007 and the latest SharePoint 2010 Foundation and SharePoint 2010 Server. And yes, you need to have .NET framework 2.0 installed on the system to be able to play with my OLAP tool.

I have also put up a 9 minutes video demonstration to give you a brief walk-through on how to use OLAP Statistics and Reporting for SharePoint.

© 2018 bahrurBlog
AssistMyTeam