Data Services


AIM Report Writing uses the Microsoft business intelligence library of tools to plan, develop, and deliver data warehouse and business intelligence products.

As our name says, we write reports. We also plan and develop the data warehouse and business intelligence products that source the data for these reports. The business will use these reports (score cards, dashboards, portals) to make better informed decisions and resolve issues faster.



In this section, we want to describe that most important part of a business intelligence product, the presentation layer, or commonly called the reporting and analytic layer. We are describing our services here in terms of reports, not the underlying layers of technology, since these reports described below will be much more familiar to the general business user. Following a description of the reports users are familiar with, we will look at our services from the technology layers and the tools used to plan and develop these technology layers required to support the reports discussed here.

Select examples of report types and the business needs they satisfy are provided below. As you read through these examples, you will quickly see that a list of business intelligence services offered by AIM Report Writing are limitless.

Reporting Tools


SQL Server Reporting Services
  • Scheduled and Ad-Hoc Reports
  • Dashboards and KPIs
  • Tables and Lists
  • Charts
  • Data Driven Reports
  • Parameter Reports
  • Alerting and Highlighting
  • Analysis Services (OLAP)

Microsoft Excel
  • On-Demand Reports
  • Dashboards and KPIs
  • Tables and Lists
  • Charts
  • Alerting and Highlighting
  • Analysis Services (OLAP)

SQL Server Integration Services
  • Cross Server Connections
  • Extract, Transform, and Load Data
  • Auditing
  • Tasks and Transformations
  • Conditional Processing
  • Configurations
  • Expressions
  • Data Sources
  • Data Destinations
  • Data Flows
  • Error Flows
  • Merging
  • Parsing
  • Processing Analysis Services (OLAP)

SQL Server Database (T-SQL)
  • Data Warehouses
  • Data Marts
  • Data Staging
  • Extract, Transform, and Load Data
  • Data Repositories
  • System Databases
  • Application Databases
  • Star Schema
  • Link Servers
  • Referential Integrity
  • Tables
  • Views
  • Stored Procedures
  • Functions
  • Auditing
  • Logging
  • Conditional Processing
  • Merging
  • Parsing

SQL Server Analysis Services (T-SQL)
  • Cubes (OLAP)
  • Star Schema
  • Facts
  • Measures
  • Dimension Relationships
  • Hierarchies
  • Hierarchy Relationships
  • Data Source Views
  • Data Sources
  • Calculations
  • Currency Conversion
  • Translations
  • Aggregations
  • Perspectives
  • Roles

Types of Reports

Types of Reports
  • dashboard (KPIs) reports
  • balanced score-card reports
  • ad-hoc reports
  • data driven reports
  • scheduled reports
  • analytic grid reports
  • analytic chart reports
  • auditing reports
  • metadata reports
  • exception reporting
  • comparison reporting
  • time-period reports
  • date-in-time reports
  • daily operational reports
  • forecasting reports
  • process reports
  • policy reports
  • summary reports
  • detail reports
  • error reports
  • list reports
  • count reports
  • sub-reports
  • drill-reports
  • correlated-reports
  • nested-reports
  • parameter-reports
Customer Analytics
  • Customer profiling
  • Targeted marketing
  • Personalization
  • Collaborative Filtering
  • Customer satisfaction
  • Customer lifetime value
  • Customer loyalty
Human Capital Productivity Analytics
  • Call center utilization and optimization
  • Production effectiveness
Sales Channel Analytics
  • Marketing
  • Sales performance and pipeline
Business Productivity Analytics
  • Defect analysis
  • Capacity planning and optimization
  • Financial reporting
  • Risk management
  • Just-in-time
  • Asset management and resource planning
Supply Chain Analytics
  • Supplier and vendor management
  • Shipping
  • Inventory control
  • Distribution analysis
Behavior Analysis
  • Purchasing trends
  • Web activity
  • Fraud and abuse detection
  • Customer attrition
  • Social network analysis
Monetary Amounts
  • The cost of raw materials
  • The value of a sale
  • Operational Expenses
  • Labor Expenses
Counts
  • Number of items produced
  • Number of items ordered
  • Number of items shipped
  • Number of items returned
  • Number of customer service calls
Time Periods
  • Minutes, hours to produce products
  • Days required to fill orders
  • Time between product failures

Stages of Usefulness
and Our Methodology

When referring to a data warehouse, data is transformed through stages of usefulness.

In the first stage of usefulness, raw data is collected from source systems and applications. This raw data is of little use to the business except to the users of these specific systems and applications.

Information is the second stage of usefulness. Once business intelligence services extract raw data from the source systems (ERP, Flat Files, XML, Applications) and transforms the data according to the defined business rules (processes that are useful to the business as a whole), the transformed data is scheduled to be loaded into a specialized reporting environment called a data warehouse. This transformed data is now useful to the entire organization and we call this kind of data information.


Distribution is the third stage of usefulness. However, this information is not useful if it is not distributed to the person, people, and departments responsible for the performance and maintenance of the related business processes and rules. Delivering the information to the appropriate business roles facilitates business users with role specific information useful to their specific responsibilities and individual decision making. Once business intelligence services deliver the information to the appropriate business role, we need to make sure that this information was also delivered in a timely manner.

The fourth stage of usefulness is Timely manner. If the information is received by the proper person, people, and departments, but it took 3 weeks for the information to be received, most likely, the information is out dated and not very useful to the business. In the modern business world, the term "timely manner" may actually be in the last 2 hours, or every morning with three updates daily.

Finally, the fifth stage of usefulness is making the information actionable by providing highlights about who to contact, what department(s) is/are under-performing, and hyper-links to related information enabling further investigation and ad-hoc analysis allowing answers to question types of who, what, where, when, and why.

In order to ensure your data is truly useful by being distributed to the proper business roles, in a timely manner, and in an actionable format, AIM Report Writing leverages ETL methods and tools that represent our intellectual property that has been accumulated through past projects and implementations.

Features of the tool set include: the eBI (3.0) ETL Framework, an eBI Environment Map, an eBI Server Configuration diagram, and a questions for decision makers worksheet. These toolbox features are described in the links below.

Other sample documents including dimension best practices, SSAS cube finishing, and a sample data model (eLogging 3.0), are provided in the links below:

The AIM Report Writing tool set will help speed the planning, requirements gathering, development of the ETL packages, assist in logging, and management and maintenance of the business intelligence services. Our methodology for implementing these business intelligence services is described below.


AIM Report Writing follows a Kimball approach. Our data warehouse methodology is a bottom-to-top delivery approach designed to fulfill the reporting and analytical needs of the entire organization.

In the bottom-to-top delivery approach, the data warehouse is built in phases, with each phase comprising the data respective to one or more business processes. The data that these processes generate then become the subject of each respective build phase of the data warehouse. These data warehouse building blocks divide and conquer what otherwise would be risky as a single "Goliath."

This idea of bottom-to-top delivery not only applies to the building blocks of a data warehouse, but it also refers to the technical layers of business intelligence required to deliver information. These technical layers of business intelligence start at the bottom with the infrastructure layer (storage and visualization), then the ETL and ELT data layer (data warehouse and bus, data marts, star schema), and finally the reporting and analytical (presentation) layer, the top. This idea of the bottom-to-top delivery can be seen in the diagram (below) titled bottom-to-top delivery. When considering performance and reliability, planning and decision making needs to consider these separate technology layers (from the bottom-to-top) to eliminate potential bottlenecks and maintain desired performance.

In both concepts of this bottom-to-top delivery approach, the most important consideration is the data warehouse bus. The data warehouse bus ensures that starting with one, or two business process data marts, will evolve into one "master", data warehouse that represents one vision of the truth. The bus is developed simultaneously with each phase ensuring that all of the data marts come together to form a consolidated data warehouse. Data consistency is guaranteed by sourcing all data marts from the "master" data warehouse. This sourcing of data marts from the data warehouse is called the data warehouse bus.


Through proper maintenance of the data warehouse bus, throughout the phased building blocks of the data warehouse, conformed dimensions may be maintained that allow users the ability to compare data across multiple business processes and get a consistent view of the enterprise.

Another advantage of the bottom-to-top delivery approach is speed in delivery. By focusing on one, or two business processes at a time, improved turn around times can be achieved. Comparing how long it takes stake owners of specific business processes to achieve consensus on defining important data points (metrics) and the reference information that describes these metrics derived from a particular process, versus how long it takes the entire enterprise to do the same, demonstrates one way turn around times are improved.

One of the core deliverables of this methodology is the dimensional model. Dimensional modeling is a logical technique for organizing the information in a data warehouse that is very different from the techniques used in entity relationship modeling.

This design technique, also called a star schema, is oriented around the end user interaction and focuses on the data model being easily understood, rather than the typical design factors that drive database normalization. In short, dimensional modeling helps achieve the two primary goals of a data warehouse/business intelligence initiative:

  1. Information Clarity, Cleanliness, and Usability
  2. Fast Query Performance

Dimensional modeling puts the data into the concepts of measurements and context. Measurements, often called facts, are usually numerical values that can be aggregated. Facts are surrounded by context that describes the numeric or measurable event; this context is grouped together into logical subsets called dimensions. Dimensions answer the questions similar to what happened, where, why, who was involved and how was it done.

Our approach at AIM Report Writing provides a framework for organizing all of the tasks and activities required to implement a successful Data Warehouse and Business Intelligence Services.

In short, our framework's primary tenets are as follows:

  • Business Focus
  • Kimball Approach
  • Bottom-to-Top Delivery
  • Dimensional Modeling

AIM Report Writing's overall approach to implementing Warehouse and Business Intelligence Services are illustrated in the diagram below. This diagram describes the relationships between the project / program, business requirements, design tasks (application, technical architecture, physical), product selection and implementation, as well as dimensional modeling.