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.