Tips for extracting, transforming and loading data for online reports.

Welcome to part 7 of BI with SharePoint and JavaScript. This is an ongoing series about building  dashboard reports in SharePoint with just a few  simple JavaScript frameworks and a Content Editor Web Part.

Here are the articles in the series.

1. Simple online column chart  (Demo)
2. Combination charts: Stacked bar chart with line. (Demo)
3. Using tabs to build a dashboard and printing reports. (Demo)
4. Visualization: Packing a lot of data into a simple chart to yield  maximum benefits (Demo)
5. Slice and Dice data while using a  page from social media  to build intelligence into your reports. (Demo)
6. Analytics  -  What If Analysis.(Demo)
7. Tips for extracting, transforming and loading data for online reports.

  The code for all reports can be found here

This post was supposed to be a set of tips detailing the things I learnt about loading data into SharePoint from other  sources using MS-Access as a bridge.  However, I came across a tool called Omniscope 

which changed the game of  ETL and reporting in the last few months for me.   The following two diagrams explain the different approaches.  

Approach I 

Use MS-Access as a bridge between the external data and SharePoint.  Use cross tab queries, lookup tables , and VBA macros to push/update data in SharePoint for reporting.





 Approach II

We still use MS-Access as a bridge between the external data and SharePoint. However, all the heavy lifting extract  and transform operations are carried out in Omniscope. Typical operations in Omniscope include aggregation, search and replace, pivot, de-pivot, merge, append, mash up data from different sources,   add new calculated fields, clean up data,  discard bad data, etc - The possibilities are endless.  For the most part, these operations can be done using the widgets provided within Omniscope without any cumbersome VBA coding.   In addition,  list data can be updated directly from Omniscope (using MS-Access as a bridge with the help of link tables).  Reports can also be generated directly from  Omniscope  and loaded into SharePoint document libraries in the form of PDF reports, PowerPoint presentations, excel files or Omniscope reports.

Analytics - What If Analysis

Welcome to part 6 of BI with SharePoint and JavaScript. This is an ongoing series about building  dashboard reports in SharePoint with just a few  simple JavaScript frameworks and a Content Editor Web Part.

Here are the articles in the series.

1. Simple online column chart  (Demo)
2. Combination charts: Stacked bar chart with line. (Demo)
3. Using tabs to build a dashboard and printing reports. (Demo)
4. Visualization: Packing a lot of data into a simple chart to yield  maximum benefits (Demo)
5. Slice and Dice data while using a  page from social media  to build intelligence into your reports. (Demo)
6. Analytics  -  What If Analysis.(Demo)
7. Tips for extracting, transforming and loading data for online reports.

  The code for all reports can be found here

In this article, let's look at  a simple example of how to  set sales targets based on What-if analysis.  




As a reminder, note that the data has been hard coded into the example to keep it simple. Typically, this data would be added and maintained in a SharePoint list (or an external list ) and retrieved using SPWebservices from SharePoint.


Let's go back to the Donut sales and trend analysis from the fourth article  in this series.  Let's say we would like to play around with the sales data for the rest of the months in the year to figure out how much sales we need to make in order to meet year end target.   For this, we can introduce a form   that gets rendered with an input text box for the rest of the months. Then, we  recalculate the sales figures  and the trend based on the user's input and then   draw the chart again.  

We have used jQuery Templates  again to render the input  text boxes.

Notice the method to clone the array data of the original sales.  This is important since  otherwise the  array gets copied as a shallow copy and would modify the original array each time the calculate button is clicked.

Here's the screenshot of the application in action. 

Here's the link to the code and demo.

You will notice that this is a very contrived example, but this should give a sense of the complex types of reports (and reporting applications) that can be built using simple JavaScript, jQuery and a couple of popular open source frameworks.