Viewing entries tagged
reporting

Comment

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!

Comment

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.

CRYSTAL REPORTS

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

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)

Comparison:

  • 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
       <Query>
         <SoapAction>
           http://localhost/TestSSRSSite/GetData
         </SoapAction>
         <Method Namespace = "http://localhost/TestSSRSSite/"
           Name = "GetData" >
           <Parameters>
            <Parameter>
             <DefaultValue>1</DefaultValue>
            </Parameter>
           </Parameters>
         </Method>
         <ElementPath IgnoreNamespaces="True">
           GetDataResponse{}/GetDataResult{}/
           diffgram{}/MyDataSet{}/
           MyDataTable{Firstname,LastName}
         </ElementPath>
       </Query>
    
    
  • 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
Supports:

  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
Supports:

  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.

UPCOMING VERSIONS

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

Conclusions:

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

Comment