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. 

Slice and Dice data while using a page from social media to build intelligence into your reports

Welcome to part 5 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.
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 contrived example of how to slice and dice the data and also use  SharePoint lists  to display and edit comments about report data.   

This report is going to be a tabular report displaying Sales data by region.  We will start off with sales data for the East and West regions.  Then, we drill down into each region and view sales data by city for the selected region. 

 Also, for each region or city, we have a Notes column which displays comments that can be entered by a regional or city sales manager for example.  Each note itself can be clicked on by the person viewing the report  and edited if required.   One of the advantages to this approach is that it's easy for anybody(especially upper management) viewing the report  to  get the story behind the numbers quickly.  This could potentially save a lot of time in terms of potential phone calls and chasing after people to get the same story. 

In this report we introduce  jQuery templates to render the report in an HTML table.  Look at the functions getSales and getSalesByRegion to see how the data is bound to the tables using jQuery templates.   Note that we could quickly add a few more levels to this report and drill down starting from a larger region like North America, EMEA, etc and then drill down by country, city, etc.   This will depend on the structure of the data being returned from SharePoint though.   For the sake of simplicity we stick to just two regions and city sales data.

Here's the link to the code and the demo.

 This report is best suited for data that will be entered by users directly in SharePoint lists  to take advantage of the rich features that SP lists provide (Easy to create an SP list and add columns, datasheet edit view, built in CRUD, export to Excel, easy to use, No code solution, etc. )

Note that if you have a large amount of data ( thousands of rows of data), and you want to drill down from the top all the way to a line item,  it might be better to consider an alternate solution (Performance Point or just pivot reports using Excel are some options).  

Visualization: Packing a lot of data into a simple chart to yield maximum benefits

Welcome to part 4 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. 
6. Analytics  -  What If Analysis.
7. Tips for extracting, transforming and loading data for online reports.
 

  The code for all reports can be found here

In this post, we will  try and pack as much data into a single chart as possible (and I'll try not to make it too cluttered).

Let's revisit the Donut sales chart from and add a few more features.

Here's the link to the code.  Running this should  display the following chart: 

Here are the new features added to the chart.

1. A  set of targets have been added - Low range and high range.   Let's imagine that these targets were set at the beginning of the year and it was decided that  if sales fall at or below the red range (8000 units), then it is to be interpreted as cause for panic. If sales fall above the green range (1000 units), then  sales have exceeded target and  measures have to be taken to  meet demand.    

2.You will also notice that a trend line has been added. This helps us in predicting a year end sales figure and plan accordingly. This trend is  calculated based on a simple linear regression.  

The code in lines 92-112  are fairly straightforward in explaining how this chart is rendered.  

Here's the link to the demo.

One thing to look out for in this chart is  the order in which the data is  added  and the parameter called "fill" in lines 100 and 101 as well as the colors chosen to represent the various data points.    The fill parameter specifies the opacity.  Yo will notice that the areas (low range and high range) are overlaid on top of each other, and the lines (sales and trend) are overlaid on top of these, so changing the order of these might make one of these data points disappear. 

Using tabs to build a dashboard and print reports

Welcome to part 3 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.
5. Slice and Dice data while using a  page from social media  to build intelligence into your reports. 
6. Analytics  -  What If Analysis.
7. Tips for extracting, transforming and loading data for online reports.
 

  The code for all reports can be found here

In this post we will cover the following topics:

1. Creating a tabbed  report to maximize real estate in the browser.

2. Expanding the tabbed report so it can be printed with page breaks.

3. Introduce a new JavaScript library called Underscore.js.

4. Add mouse over to view the values in the simple graph. 

Here's the link to the demo. Here's the link to the code.  I have hard coded the data into the HTML instead of retrieving the data from SharePoint to make this post easy to understand. If you want to look at an example of retrieving data from a SharePoint list look at the  previous two posts.

 The code is quite self-explanatory, so I will go over just the main portions.

 The entire contents of the reports are enclosed in a div  called "tabs" . Look at the init() - line 287 function to understand how the tabs work using jQuery. 

The PrintReport method (line 268) toggles between  showing all the page contents and showing the tabs. 

Notice that we make use of the  Underscore.js library's each method here to cycle through each element in the array and either hide or show the tabs.  In a later post we will look at another great use with the Underscore library , which is the groupby method. 

 Finally, notice the code in line 75 and 81.  The page break in 81 makes the report print in two pages.

Combination charts: Stacked bar chart with line

Welcome to part 2 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.

I plan to post articles on the following topics:

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.
4. Visualization: Packing a lot of data into a simple chart to yield  maximum benefits.
5. Slice and Dice data while using a  page from social media  to build intelligence into your reports. 
6. Analytics  -  What If Analysis.
7. Tips for extracting, transforming and loading data for online reports.
 

  The code for all reports can be found here

 

This time , we are going to build a slightly more sophisticated chart. It's a stacked bar chart with a trend line.   In Excel,  it's relatively easy to create this kind of a chart. However, I haven't seen any free/open source web based  library out there where you can build a chart like this.

  One of the uses of this chart is that at least more than three data points are packed into a small space

  without sacrificing usability.  This chart can prove useful if you  need to show the actual values of more than one data point and then compare the sum of these data points against a predefined target . In our example, we are going to continue with the sales theme from the last post. Eating too many donuts  is not really healthy, so let's sell some fruit this time. 

  We are going to measure the sales of apples and oranges and compare the sales of apples vs oranges!  We are also going to compare the target we set against the total sales in the same chart. 

   

  Data Source: We are going to use the same SharePoint list  we used in  part 1 if this series.  Go ahead and delete all the data in that list and then insert the following data into the list.

                      Jan       Feb       Mar       Apr        May     Jun   

  Apples          200      250      300        350         250     500

  Oranges        300      350      250        350         250     400 

  Target           800      700      600        600         600     800

 

  

  Tip: If you have data in an excel list or a CSV file, you can edit the list in datasheet view  and copy and paste data into the datasheet view of the list. There are numerous other ways to import external data (especially in SP 2010) , but we'll stick to the basics for now.

 Note that we only have a couple of rows of data here to deal with.  What if we had  to report on a hundred products?  Well, we would not  display that many items  as a graphical chart.  If there is a way to categorize the data, we could then show the sales data rolled up by category in a tabular report and then drill down from there.  We'll visit this topic in an upcoming post. 

 

 Here's the link to the code to  retrieve and render the chart. 

 In addition, you also need to include the flot.stack  plugin to your page. This plugin comes along with the flot library.

 <code><script language="javascript" type="text/javascript" src="../jquery.flot.stack.js"></script></code>

Also, in line 62 , replace the server name with the path to your server.

  Notice that we have added very little to the previous example.  Let's discuss the new elements added briefly.

  The setBars method is used to add a stacked bar data point.  Refer to the flot API for more options.

 <code>

function setbars(title, d)

{

   var bardata = {

          label: title,

          data: d,

          bars: {

              show: true ,

              lineWidth: 0,

              barWidth: 0.4,

              fill: true,

              fillColor: { colors: [ { opacity: 0.9 }, { opacity: 0.9 } ] }         

          }

        };

    return    bardata; 

}

</code>

 

We pass the title and data to this method and it returns a JSON object formatted to be plotted as a bar.

   The following line sets the colors for the data series.

   colors: ["red", "orange", "black"]

  You can also use hex colors to add fancy colors or colors from your company's branding standards to your chart.

  <code>

       var Target = {

            data: d3,

            label: "Target",

            lines: { show: true, steps: false },

            stack: null

        };

   </code>    

   Notice that the stack attribute is set to null.  This is the trick to drawing a line in a stacked bar  graph.

<code>

        legend: {

            show: true,

            backgroundOpacity: 0.8,

            container: "#labels",

            labelFormatter: function(label, series) {

              return '<span>'+ label + '</span>';

            }

</code>

The above lines instruct Flot to add the legends in a separate div outside the chart area.

Here's the result you should see:

   

   That's it. We are done as I promised last time with a shorter post. We now have a fancy graph with  three data points  represented in a compact area which gives a clear picture. 

Here's the link to the code and the demo

  

   As usual, I encourage you to send me feedback and questions. 

  

   In the next post,  we will take a break from building charts and organize the two reports we have built so far into a set of tabs.  Additionally, we will also address printing reports that are displayed inside tabs.

       

Introduction

Welcome to my blog.  I am starting this blog to share my knowledge of things I learnt  by building   dashboards and different kinds of reports in  SharePoint  with just a few  simple JavaScript frameworks and a Content Editor WebPart. 

The theme of this blog series is to adhere to the following principles to the best extent possible.

1. Do not reinvent the wheel - Use Free/Open source JavaScript Frameworks as much as possible to solve problems.

2. Use JavaScript/Content Editor Web parts/SharePoint Lists/Document libraries to build solutions - We will not use Visual Studio, Silverlight , Flash, custom web parts, Workflows,  etc.

3. Try and design elegant solutions by using simple techniques. By elegant, I mean effective, easy to visualize and intuitive enough to analyze the reports. (I realize that this is a tall order and a little bit subjective, but I aim/hope to please)

Here are the articles in the series.

1. Simple online column chart  (Demo)
2. Combination charts: Stacked bar chart with line. 
3. Using tabs to build a dashboard and printing reports. 
4. Visualization: Packing a lot of data into a simple chart to yield  maximum benefits 
5. Slice and Dice data while using a  page from social media  to build intelligence into your reports. 
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


 One of the reasons I am posting these articles is based on the challenges I faced and the experience I gained from it, but also   the need to exchange ideas.  When I attended SharePoint Saturday in NYC a few days ago, I realized that  you learn a lot more quickly when ideas are exchanged.   There are quite a lot of resources on the web about getting started with using jQuery in SharePoint, so I won't start with the basics and focus more on  creating reports (including some tips on creating effective reports based on my experience) .


Some Context
Before I get started with my first topic, I would like to provide some context (You can skip this section if you are not interested).  I had a similar situation  to the SharePoint Hillbilly's  client requirements where  they wanted fancy solutions to be built without  allowing you to deploy any code using Visual Studio,   the only tool I had at my disposal was SharePoint Designer.  My requirements were to build  a set of automated reports to replace the manual and tedious work  of building reports using MS-Excel/VBA.
  Now,  MS-Excel/VBA have been around for  about  a couple of decades or so and have matured quite a bit and you can build great reports if you know the tool and can keep you employed in the financial industry if you are an expert.
      However,  I quickly realized that we ended up spending  a lot of time retooling our  Excel reports due to constant changes to the dozens of dashboard reports which we were generating on a monthly basis. In addition, we were dealing with multiple data sources (Excel files, SharePoint lists, Other databases)   where the schema was constantly changing.  If you have a similar scenario, I hope to share my experiences of converting these reports into online  reports with you and learn from you in return.

Let's Build Something Already
Here are a few resources to help you get familiar with some of the online charting options:

Flot Charting library  - This canvas based  library is an excellent free charting library that is actively maintained and has a vibrant community that not only posts so many solutions, but also numerous plugins.   This has worked great even on IE6 for me.
Here are a few other solutions that you can explore if you are adventurous:
1. gRaphael  - Canvas based JavaScript charting library based on Raphael
2. JSCharts
3. Highcharts
In addition, there are other Flash/Silverlight/webpart based solutions, but since this blog  dedicated to simple JavScript based solutions, I will let you research those on your own.

Now, let's get started building a simple online column chart. This is going to be  our "Hello World" of online charts. 

Tip - Test this example on a local HTML file with hard coded sample  before trying it in SharePoint.

Include the following JS libraries in your page in the order shown.  
Excanvas - Used to support canvas tag in IE
jQuery
flot

	<!--[if IE]><script src="<Path to file on your server>/excanvas.js"></script><![endif]-->
	<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
	<script src="<Path to file on your server>/jquery.flot.js>"></script>

view raw kwinkndirty1 hosted with ❤ by GitHub

Now, let's build the chart using sample hard coded data before we  use a SharePoint list to retrieve, process and build the chart. 

Let's add a placeholder  for the chart in our content query web part.

	<h1> Total Sales </h1>
	<div id="simplecolumnchart" style="width:600px;height:300px" >
	</div>

view raw kwinkndirty2 hosted with ❤ by GitHub

Now, let's add some JavaScript code to build the chart. 

	<script id="source" language="javascript" type="text/javascript">
	//Define a method to call the flot plot method to draw a bar chart.
	function PlotGraph( d )
	{
	$.plot($("#simplecolumnchart"), d,
	{
	series: {
	bars: { show: true, barWidth: 0.2, align:'center',fill :0.8, fillColor: { colors: [ { opacity: 0.9 }, { opacity: 0.6 } ] }}
	},
	xaxis: {
	ticks: 5,
	tickFormatter: function(x) {
	var Months=new Array("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
	return Months[x] ; }
	},
	legend: {
	show: true
	}
	});
	}
	// setup some random data
	var d1 = [];
	d1.push([0,3000]);
	d1.push([1,3200]);
	d1.push([2,3500]);
	d1.push([3,3200]);
	d1.push([4,3000]);
	d1.push([5,2800]);
	donutSales = { label: "Donuts", data: d1} ;
	//Call the Plot function with our donut sales data
	PlotGraph( [donutSales]);
	</script>

view raw kwinkndirty3 hosted with ❤ by GitHub

Now, loading this on your browser should yield the following chart:

Now let's  add the data into a SharePoint list and  read  from this list and generate the chart.

For the sake of simplicity, I am going to include all my code in a Content Editor web part.  
Create a SharePoint list called  SalesList and add the following columns (of type numeric):
Jan, Feb, Mar, Apr and so on up to Dec.  Creating your list this way helps you save on the amount of rows you need to create and eventually retrieve when the report is generated.
Now, add the following data as a new item in the list.

Title: Donuts, Jan: 3000, Feb: 3200, Mar: 3500, Apr: 3200, May:3000, Jun: 2800

Now let's  read from this list using an ajax call and generate the chart.
Here's the link to the code and demo. Remember to replace the server path in the

getSales function. Note that the GetSales method is  commented out and we have hardcoded the data for simplicity. 

I hope you found this post useful. I realize that this is a rather long post, but now that  we have gone through a lot of the basics,  I'll  try and be more concise in the upcoming posts.

Do write back to me and share your own experiences as well as feedback about this post.




Resources
Here are some resources you can use to get started if you are an absolute newbie with JavaScript/jQuery.
1. JavaScript Tutorial
2. jQuery Tutorial
3. JSON


Also, the following resources can help you learn how to use JavaScript/jQuery in SharePoint. 
1. SharePoint Hillbilly  - Mark gave an excellent presentation on using jQuery in SharePoint at the SharePoint Saturday NYC event and inspired me to get started with my blog quicker.
2. SPServices  - This free library is an excellent abstraction tool, but I have to admit that I only recently found this. I'll try and weave this into my later posts.
3. jPoint  - Another library that I discovered recently and hope to play with and hopefully provide some feedback.