Self Service Reporting

Self Service Reporting

Self Service Reporting 

Self service reporting typically provides data to a handful of expert Business users who understand the data and the business operations best. The expert business users also understand  how to analyse the data to obtain information that can assist the Company going forward. They can analyse the data in far less time than if they were forced to specify their requirements to an IT professional who goes off and creates a report on the users behalf. And the cost saving and delivery time reduction are significant.

Our experience has been that the Businesses that use Self Service Reporting most effectively have a large percentage of their business users receiving information. We are aware of one business that expanded its reporting usage from around 30 specialist users, many of which were IT users, to over 1,000 Business users (over 25% of all persons in the Company). The data access was personalised to just include what the user wanted to see. The dashboards allowed each user to pick and choose their displays based on their needs and desire.  Reports also presented the data that was just relevant to the business users with most users accessing selective information on me as an employee, my group and my Company as a whole.

Self Service User Types

ANALYTICS Power Users 
  • What occurred?
  • Why did it occur?
  • What will occur going forward?
  • How can we influence it?
Ad Hoc Users
  • Use a simplified data model (resembles a spreadsheet)
  • Often upload data to Excel
  • Personalised, contains real time data, runs on iPAD + iPhone and PC
Dashboard Users
  • Personalised – add the displays on the dashboard that are relevant to their position
  • Typically contains compliance information (Timesheet information)
  • Very fast response on iPad, iPhone or PC 

Benefits of Self Service Reporting

  • Empowers the Business
  • Business experts with sound knowledge access own data
  • Allows users with best knowledge of business operation to analyse the data
  • Considerable reduction in IT spend
  • Pre-canned dashboards run on iPad, iPhone and PC
  • Very fast response
  • Near Real Time Data capture
  • 365 degree view

Major Requirements of Data Provisioning

DATA Capture (replication) – Implemented by IT
  • Timely, accurate (fully reconciled), secure
  • Supports tools to gather data from variety of data sources
  • Low impact in source database & applications
  • Handles a large number of source database, data and file types
  • Data placed into highly available, high performance, secure and easy to manage target database
DATA Model – Implemented by IT
  • Simplified data model (spreadsheet like simplifying hundreds of tables into a handful)
  • Comprehensively documented with training
  • Quality data, reconciled and audited
  • Secure data access
  • Exceptionally fast response
  • Allows users to upload into Excel
PRE CANNED Reports and Dashboards – Provided by Toolset used by Business Users
  • Easy to use dashboards creators that allow end user to pick and choose what they wish to display
  • Personalised dashboards that run on the users PC, iPhone and iPad
  • Business has ability to easily create own ad hoc reports using latest easy to use technology (Excel)
  • Ability to easily share reports across business users

Why do Self Service Reporting Systems Fail

To ensure the success the self service reporting implementation, it is appropriate to observe the major causes of failure and ensure that each is avoided for the successful implementation.

#1. – Performance  (Query performance too slow, Data loads not meeting required delivery timeframe or impact on source systems unacceptable)

#2. – Scalability (Capacity to store data and accommodate load and query usage has exceeded the original forecasts)

#3. – Budget blow out (Typically occurs in the IT delivery cost where skills are not sufficient for a rapid and high quality delivery)

#4. – Inappropriate database or toolset selected (Some databases do not scale as well as others, some replication tools are considerably more complex and costly to set up)

#5. – Data model too complicated for end users (Simplifying data model access requires a special skill set that is different to conventional data modelling techniques)

#6. – Inability to provide near real time data (Modern reporting systems require data in near real time – achieving this can be difficult)

Successful Implementation Checklist

  • Performance specialists on the team to ensure low impact on source applications, rapid replication and fast query response
  • Infrastructure and capacity planning specialists to ensure that the implementation scales
  • Architecture and Product Selection specialists
  • Data modelling specialists that can simplify your data access
  • Data specialists including replication techniques and products, databases, reconciliation, storage reduction + archive and purge

Power BI Best Approach

  • On Premise or Cloud could work equally as well
  • Separate core transactional database from BI & Analytics
  • Use a combination of facts and dimensions (real time data) and cubes
    • However Columnar in Memory allows data to be stored in first normal form in SQL Server 2016 and later
  • Simplified Reporting Data Model is Key
  • Create a Group Workspace (share reports)
  • Use Enterprise Gateway to assist with security
  • Do data cleansing, normalization, and formatting in ETL (PowerQuery) instead of via calculated columns in DAX
  • Tables should be tall and skinny whenever possible
    • Unless using columnar in memory SQL Server 2016 or later in which case tables can be very wide
  • Create a date dimension table to select all date variations
  • Don’t allow Power Query to automatically determine each columns data type – especially dates
  • Have a main data table and lookup tables
  • Don’t mix data tables