Viewing entries tagged
business intelligence


Understanding different kinds of Data Reporting. Why and how is done.

In business, the words reporting, data reporting, audit reports, big data, and analytics are used very loosely sometimes. So, I thought I'd put in writing some thoughts about reporting as a reference point for anyone scratching their heads.

We've all heard the parrots say "DATA is King", but how do we get "DATA"?

Data for almost ANY application can be categorized into one of 3 categories:

  1. Operational Data (aka. Configuration Data)
  2. Event Data (aka. Business Event Data)
  3. Audit Data (aka. Tech Data)

The patterns to display and materialize these data sources generally follow a very ordered and logical process where:

  1. Operational Data is shown in small digestible chunks so a human being can easily analyze and modify what is presented to him/her at the moment.
    • Most often than not, it takes the form of a UI in a webpage or a native app.
    • Some other times, the configuration data is shown in printable format. More often than not, these take the form of counters and aggregate live data. A common use case is to keep counters of live metrics,  KPI alerts, and application heartbeats.
    • Operational data is modeled to support full CRUD operations.
    • Regardless of the format, this kind of data is never presented with thousands of rows or configuration entries... and the reason for that is: is not human readable. Just imagine your reaction if every time you do right-click on a file in Windows you get a menu with 2 thousand lines; not very useful.
    • Last but not least, configuration data is ALWAYS live data, it NEVER holds historical significance.
  1. Event Data exists with the sole purpose of fulfilling business intelligenceand analytics.
    • Event Data is always decentralized, meaning there is no magic bullet that can fulfill all applications, since the nature of the events being logged is usually tightly coupled with the actions that occur within applications, and those have some semantic meaning around the business of the application.
    • Event Data usually take the form of a separate database, or a different schema within the same database where events are logged in an asynchronous way so they don't interrupt the main flow of the application in question.
    • Event Data is WRITE-ONLY data. There are NO UPDATES, and NO DELETES, only writes to drop the events when they occur and reads to generate the reports. This usually makes event databases grow very rapidly depending on the number of events being reported.
    • Event data generally follows a star data model with multiple correlated tables holding DUPLICATED data from the operational data, with the additional time stamp on each event dropped.
    • Depending on the needs and performance boundaries allowed by the reporting tool, reports can run directly from the Event database, or from a Data Warehouse (DW). The DW is nothing more than a centralized database or collections of databases that are highly optimized to run reports as fast and efficient as possible, so business folks can run analytics and complex searches on the data from different perspectives.
    • Reports running from a Data Warehouse repository usually perform better and faster, since the tables are flattened to match exactly the dimensions of the reports. The data is said to be 'curated' when is transformed and loaded into the DW, since the schema of the data after ETLs are run, can be totally different from the way it was designed on for the operation of the application (the Operational Data)
    • If you run your reports from Event database, your queries will likely have a lot of joins; whereas if you run them from the DW, very few joins will be necessary (because the data is curated to match those reporting needs, and perform faster).
    • Last but not least, event data ALWAYS holds historical significance and ALWAYS report on BUSINESS data.
  1. Audit Dataexists with the sole purpose of reporting on Technology events. That is everything that is NOT BUSINESS data, but relates to the performance, security, functionality, etc, from the application.
    • Audit Data usually follows a very similar path to the Event data in terms of implementation and consumption (like having an Audit database with Star schema, and moving to the DW, blah, blah, blah).
    • The main differentiator between Audit Data and Business Data is that one relates to the business events, while the other relates to technology events.
    • Just like events, audit data ALWAYS holds historical significance and ALWAYS reports TECHNOLOGY-DRIVEN data.

When asked to generate reports and such, ask yourself what type of report it is and don't be afraid to ask questions and challenge the true business need for the 'report'. If someone is looking for operational (live) data, and there are UI/Screens already available that show such data for configuration or anything else, then ask yourself if you want to go on a limb and create a lot of overhead duplicating the effort, or simply adding a "Save as PDF" button or link in the screen will do the trick.

The last piece is about BIG DATA. That's the new hot word around IT these days. Big Data is nothing more than a group of very large data sets (in the order or exabytes) whose relationships and business semantics are so broad and messy, that is very difficult to drive analytics using the traditional DBA tools found in a DW environment. You can think of it like the next level to data warehousing.

When a DW gets big enough, or when massive amounts of data from different DW needs business analysis, then it becomes BIG DATA. The challenges are different and harder when you are trying to put order to this kind of mess, but once you do, a lot of goodies can come out of it, including predictive analytics based on past performance and events. Predictive business analytic engines are one of the hardest things to do, and it is a valued asset, especially for large corporations that have been hoarding data for decades, and now want to use it as a competitive advantage to plan ahead.

Good luck with your reporting adventures!


1 Comment

SharePoint Site Architecture

In this post:

  • WSS vs MOSS
  • SharePoint site architecture

SharePoint is an ecosystem of software and server products as it was described in this previous post. To understand it better let's look at the architecture and object model that it is offered with SharePoint. The basic component of SharePoint is Windows SharePoint Services (WSS from now on). WSS is a free server component that must be installed in a Windows Server OS and contains the basic framework and infrastructure that supports any other SharePoint offerings like the different Microsoft Office SharePoint Server (MOSS from now on) versions.

You can think of it the same way selling cars work. You get the basic model with a chassis, 4 tires and a steering wheel (that will be WSS) and then you can add stuff to it like a great sound system, a dynamic design, GPS and Cruise Control, etc (those nifty things being the analogous to MOSS).

So now that we have an idea of how WSS relates to MOSS, let's see exactly how their offerings stand apart.

As we can see all MOSS trims are nothing more than a very useful set of extensions and additions to the WSS platform, offering users a more powerful and pleasant experience out of the box. These extensions are supported thanks to a very complex architecture and framework that has evolved quite a bit over time.

In a nutshell the industry refers to a SharePoint installation (be it WSS only or MOSS) as a SharePoint FARM. A Farm is a collection of physical SharePoint servers  that serve the content to users. A Farm can be formed using different topologies that we'll explore later, before we get to that, let's actually see what is going to be served, how does SharePoint structures its web sites and content.

Farm:  A collection of SharePoint Web Applications.

SP Web Application:  A collection of Site Collections.

SP Site Collection: Formed by a single root site (aka Top-Level site) and a collection of sub-sites.

SP Sub-Site: A collection of pages, lists, libraries and content that are displayed through the browser.

Each of these components in the SharePoint site architecture have different rules and security settings and restrictions. As you can see, this is a tree structure and very successful way to isolate users and content to ensure confidentiality, availability and integrity in the data security. This model is very user friendly since everyone can understand a tree structure and most people are already used to it.

I always like to compare a SharePoint Farm with a physical hard drive. If the Farm is the hard drive, then we can think of the Web Applications as partitions of the Farm. Likewise Site Collections will be Folders directly on the root of the partition. Sub-Sites are also represented by folders that only go inside of the Site Collections and so on, you get the idea.

Because is easy to understand the site architecture SharePoint offers, users can quickly provision (a fancy word for 'create') web sites with the click of a button. Because of this, planning for a successful deployment and IT infrastructure to support all the greatness SharePoint offers does not come at ease. Planning the right topology that will hold all of this together is very important for a successful SharePoint deployment. More on SharePoint topology and planning in upcoming posts.

1 Comment

1 Comment

SharePoint? What is that?

The first questions people ask themselves when they hear the SharePoint Buzz for the first time is What the heck is SharePoint? I will explain in a friendly and simplistic way what SharePoint is, and how it has fold out to be one of the most powerful and fast growing software solutions in the history. Coming up:

  • What is SharePoint?
  • How exactly is SharePoint different from any regular web site?
  • How can SharePoint help you in your bottom line?
  • Conclusions

What is SharePoint?

From the technical-friendly point of view SharePoint is a software ecosystem formed by many different Microsoft software products. Microsoft indifferently refers to it as Microsoft SharePoint Products and Technologies. SharePoint (aka SP) targets the space of web collaboration functions, content and document management, search and social networking in one centralized solution that offers a plethora of very powerful business applications that are deeply tight to the already popular Microsoft Office applications.

From the end users perspective SharePoint is a web site (web application) that allows users to collaborate and share information, documents, photos and other media rich content directly from the web browser without needed to have any technical skill. Simply by using tools they already know like Microsoft Word, Excel, Power Point, InfoPath and such, users can create very rich documents and have them broadcast to a large audience with the click of a button.

Another important notation to make is that SharePoint is a cross platform product. That means SharePoint, being a browser based tool, can be experienced from many different operating systems like Windows, Mac OS-X and Linux systems. Although SP is a cross platform tool, as of today's official product (MOSS 2007) there are a few rendering abnormalities with some specific content in browsers different from Internet Explorer. Users accessing SharePoint sites with the popular Firefox browser, Safari, Opera and Chrome to name a few may experience that some controls and parts of certain pages will be rendered incorrectly in the page. This issue is not very common and will not affect the functionality and power SharePoint brings to its users. SharePoint 2010 is in beta stage as of the writing if this article and the SP Team announced most of the browser compatibility problems from previous versions will disappear in the upcoming release.

Browsers in SharePoint

If you are interested in diving a bit more into the browser war, SharePoint official compatibility and the Microsoft recommendations for browser support you can explore this TechNet article that is dedicated to such topic.

Ok, so how exactly is SharePoint different from any regular web site again?

As we mentioned before, SharePoint is a collection of software applications. These applications are Server products, meaning they were made to be run in a Windows Server OS. The combination of these products (Search, Forms Server, Excel Services, SSO, etc) provide SharePoint with many built in functionality that does not need to be implemented and can still be customized to a very granular level with very little IT support.

To better understand the difference between the SharePoint offerings and building and maintaining your own web site, we must first see what goes into creating and maintaining a web site. The following table shows some (I said SOME) of pros and cons of the most popular methods to build a web site and their pros and cons (Click to enlarge).

The "Web Site" in the title of the table is quoted, because the reality is that SharePoint offers a lot more than just a Web Site, it offers collaboration, networking and business tools to make any SP install a catch-all experience. Also is worth noting that technically SharePoint does not offers a Web Site, instead (from IIS perspective) it builds a set of IIS Web Applications that are linked and can exchange information via the SharePoint ecosystem. Hold on to your hats, more on that and the SharePoint terminology later.

As you can see there is plenty to eat in the SharePoint family. But how does it directly affect you?

How can SharePoint help me as an organization or as an individual?

Honestly, even if I try I don't think I can come up with a better sentence than this one from the official Microsoft SharePoint site:

"… Microsoft Office SharePoint Server 2007 provides a single, integrated location where employees can efficiently collaborate with team members, find organizational resources, search for experts and corporate information, manage content and workflow, and leverage business insight to make better-informed decisions."

--- Pasted from <>---

SharePoint is an extension to the cloud (Internet) of all of the Office Family of products. In fact SharePoint is part of the Microsoft Office Group. If you or your organization are using any of the Microsoft Office products (Word, PowerPoint, Excel, etc) then you are half the way into becoming a great SharePoint user and take full advantage of its offerings. Although SharePoint clearly targets more businesses than individuals, the fact that most of us work for some corporation means that we're very likely to at one point or another face the SharePoint solution of your workplace. And trust me, I'll make your live so much pleasant… SharePoint will be there when:

  • You want to cut dramatically the use of paper and ink in your organization.
  • You want to improve the performance of your employees and make them work better and more organized between them.
  • You need to collaborate with a team member in writing on the same document simultaneously.
  • You want to request a day off without having to confront the boss.
  • You want people to request a day off without coming to your office or spending a fortune in paper and ink. Also want to be able to notify the requestor of your decision about his request.
  • You are a manager and you want your team to work under your schedule and not the other way around.
  • You want to expose your professional profile in the company's SharePoint to find opportunities in other departments and move up within the organization. Get discovered.
  • You want to make a small correction to a published document and will be able to make the change and make it available immediately online with one click. No downloading and re-uploading, no emailing required.
  • You want to see how a particular spread sheet or document looked like last month without disturbing the IT department to pull it off the archive.
  • You want to broadcast the meetings calendar to all the Outlook account in your department with a click of a button.
  • You want to automatically get notified when somebody completes a sale, or gets a new bid, or renews a contract and want to see at a glance the ongoing financial health of your company with real-time data.
  • You want to have a fun home page with your favorite color and alternating pictures of your family.
  • You want to access all corporate data securely from the internet.
  • You want to monitor each department of your organization and ensure they are meeting the new goals with key performance indicators.
  • You want to collect and automate the employee contact info by having forms your employees can fill in and submit from any web browser.
  • And much, much more… :)


I hope you after reading this article you have a better understanding about what SharePoint is, how it differs and stands out over traditional web site design as well as the immediate benefits that SharePoint brings to organizations and individuals and a general feel for some of the coolest features SharePoint offers. I think in general SharePoint is a great solution, ever evolving (SharePoint 2010 is Beta already). I'll try to cover more on SharePoint in future posts.

Thanks for reading!

1 Comment


Crystal and SQL Reporting Services

MSSAP Why talking about reports? Reporting is one of the key elements of Business Intelligence technologies.

- The reason for this article is basically that there is a sort of dislike and panic involved in approaching a Reporting problem.

- Nobody (that I know of) likes working in reports projects.

- Reports are a different beast than OOP, with a complete different nature, programming experience and solution approach.

This is intended to illustrate and discuss the development of reports using Crystal Reports (CR) and SQL Server Reporting Services (SSRS).

CR and SSRS are 2 COMPLETELY DIFFERENT technologies that produce the same result: A Report.

Neither Crystal nor SSRS have a full object-oriented report writer. Both are object-based with a rich object model, and feature a certain amount of cascading. Additionally, Crystal supports CSS themes. However, OOP enthusiasts will need to lower their expectations and adjust to the fact that neither product supports true object-oriented features

In general we can say that SSRS contains roughly 70–75 percent of the functionality of Crystal Reports. Some companies have decided to switch from Crystal to SSRS, and others are contemplating the switch: therefore, it's very important to know what functionality may be lost during the process.


As of today Crystal Reports is the most used general purpose reporting tool in the market (about 55%) Crystal reports is owned by SAP AG (a German Company), Business Objects was acquired by SAP.

It’s been in the market since 1980, when the first Crystal Report version I. Today the product has evolved and has had 11 versions spanning over 20 years.


Reporting Services is much easier to use and has a more rich experience since it supports things like having a Chart AND a table in the same report section. Today it has about 20% of the reporting market share. SQL Server Reporting Services is owned by Microsoft, as part of subsystem that belongs to their SQL Server Enterprise Solutions branch.

It’s been in the market since 2004 and it has 3 versions so far (SRS2000, SRS2005 and SRS2008)


  • Report creation:

Both offer report wizard that helps to define a data source and build general report layout. They also support manual definition where you’ll start with an empty report and modify it from there.

  • Report data sources:
Crystal Reports SQL Reporting Services
Data Source from: DataSet, .Net Class, OLE DB, SQL Server, Oracle, etc.Pull Model: External Data Source like a store procedure. Data Source from: DataSet, .Net Class, OLE DB, SQL Server, Oracle, or XML.

You can specify an XML web service as the data source for an SSRS report!!! To do this you must follow the next steps:

1- You define the data source in the connection string like this:

       data source=http://localhost/test.aspx

2- Use the code below for the query syntax (in the report dataset query).

       // Syntax to query XML web service for an SSRS DataSet
         <Method Namespace = "http://localhost/TestSSRSSite/"
           Name = "GetData" >
         <ElementPath IgnoreNamespaces="True">
  • Linking Tables:

If you use stored procedures (or in-line SQL queries) directly as your data source, be aware both report products will only allow you to access the first table in the result set.

Crystal Reports SQL Reporting Services
Supports multiple tables when pushing data through datasets/XML into the report by using its tables collection database object. It allows put to place columns from != tables on the same report body respecting relationships. Supports only one result set. To use multiple result sets, multiple datasets must be created and use sub-reports to link the data from the datasets
  • Report sections and groups: Both offer similar approach to groups and report sections like header, footer, sections, groups and details area.
  • Formatting and formula options: Here Crystal offers a more powerful approach since it supports a larger variety of formatting options than SSRS.
Crystal Reports SQL Reporting Services

  1. CSS
  2. Rotate Text
  3. Watermarks
  4. Tooltips
  5. Suppressing Duplicates
  6. Date formatting
  7. Currency formatting
  8. RTF/HTML Rendering (Rich Text Format)
  9. Datatype conversion.
  10. Expressions/Calculated fields
  11. Dynamic formatting
  12. Hyperlinks

  1. Watermarks
  2. Tooltips
  3. Suppressing Duplicates (Hide)
  4. Date formatting
  5. Currency formatting
  6. Datatype conversion
  7. Expressions/Calculated fields
  8. Dynamic formatting (+ SWITCH statement for multiple condition evaluation)
  9. Hyperlinks

- No CSS, Text rotation or RTF (Rich Text Format) rendering

- Allows you to add custom code to reports, either by embedding Visual Basic code directly into reports, or by adding externally created and compiled .NET assemblies

  • Page control options:

Crystal generally provides better support for page control through the Keep Together options for both individual report sections as well as entire report groups.

SSRS allows you to define a page break at the end of a group; it will not conditionally allow you to essentially implement "widow-orphan" protection to ensure that a group or section will fit on a page. SSRS does implement a Keep Together property for the rectangle control that is very buggy at this moment and is consider an uncooked feature.

  • Report parameters and global report variables:

Both reporting products support parameters and report variables.

  • Sub-Reports:

Typically you build a sub-report when you need to display multiple levels of detail data, and/or when you need to display data from multiple data sources. In most instances you'll need to establish a link between the sub-report and the parent report.

Crystal Reports SQL Reporting Services
You can “Insert” sub-reports as reusable sections for parent reports (like a reusable header or footer)

Different charts for shared datasets can be linked to their own sub-report and be reuse across multiple parent reports.

You cannot insert a sub-report into sections of other reports. This makes difficult to build common header/footer templates.

Different charts for shared datasets MUST have their own sub-report since they cannot be used with the same result set, if the set is different than the one in the parent.

  • Charts:
Crystal Reports SQL Reporting Services
  1. Bars (horizontal & vertical)
  2. Lines
  3. Area
  4. Pie
  5. Doughnut
  6. 3D Raiser
  7. 3D Surface
  8. XY Scatter
  9. Radar
  10. Bubble
  11. Stock
  12. Numeric Axis
  13. Gauge
  14. Gantt (timeline)
  15. Funnel
  1. Horizontal Bar
  2. Vertical Column
  3. Area
  4. Line
  5. Pie
  6. Doughnut
  7. Scatter
  8. Bubble
  9. Stock
  • Drill-down capabilities:

Both reporting products support drill-down features. Crystal though its Hide property in the Section Expert and SSRS though its Hidden and ToggleItem properties.

  • Exporting reports:
Crystal Reports SQL Reporting Services
PDF, Excel, Word and RTF.

Note: No XML, TIFF or CSV support.

XML, CSV, TIFF, PDF, Web Archive (MHTML) and Excel.

Note: No Word support.

  • Programmatic access:
Crystal Reports SQL Reporting Services
  1. Extensive and complex API with a lot of 3rd party wrapper tools.
  2. Does not directly support web service report definition control (3rd party tools will)
  1. Great open architecture for implementing any function, but many CR features are in developing stage yet.
  2. Built in support to generate reports from a report URL or a Web Service.
  • Previewing in Windows Forms or in the browser:

Both reporting products support previewing reports in Windows Form or in the browser. Note that SSRS intrinsically support for the web is superior to Crystal because SSRS is essentially an ASP.NET application

  • Matrix and Cross-Tab objects:

Sometimes you may want to create reports with a variable number of columns based on the amount of data in a result set. For instance, you might have a result set of orders by year where the year is a row. You want to display a column for each year, but you may not know at design time how many years the result set will contain.

Both reporting products offer decent solutions for this situation through Crystal’s Cross-Tab Report and SSRS’s Matrix report.


Crystal Reports (Beta Release Now Available) SQL Reporting Services (CTP Now Available “Katmai”)
  1. A reduced footprint for the report designer.
  2. Interactive sorting of report columns
  3. Support for web pagination and an improved web report viewer
  4. Cross-Tab enhancements (ability to add Cross-Tab custom summaries, either repeating or one-time)
  5. Built-in barcode font support (Crystal now includes Code39 barcode fonts)
  6. Interactive report parameters (customers can change report parameters on the fly, and the developer can decide which parameters can be modified)
  7. Ability to define XML export format options.
  8. Flash integration—you can embed a Shockwave Flash (SWF) object inside reports.
  9. Improvements to the report engine for performance and resource utilization
  1. A new report control called Tablix, which combines the flexibility of the report table with the cross-tab capabilities of the report matrix.
  2. Improvements to the report engine for performance and resource utilization.
  3. A new hosting model independent of IIS.
  4. New charting tool capabilities (Microsoft has purchased the rights to the Dundas charting capabilities)
  5. SharePoint integration to access and manage reports and data sources from a SharePoint site


Crystal Reports has the benefit of being the veteran in the field and supports almost every conceivable reporting requirement there is. It has versions for developers as well as business users. SSRS is the new kid on the block and it is focused only on the developer market. SSRS is a 1.0 release and still has some growing pains to go through