Homeworks academic service


A report on databases in my organization

A Little Personal History

I'm a huge advocate of business analytics technology; I see the value to the organization of providing self-service reporting and analysis. To help provide perspective, I like to start classes with an understanding of data warehousing concepts and how these ideas apply to data sources and data models. This example provides the proper perspective for designing environments, models, and reports.

While a number of great texts look at data warehouses for example, Data Warehousing: Architecture and Implementation and Data Warehouse: From Architecture to Implementationmany of my students don't understand the rationale for data warehouse design, and they look for a simple explanation of what a data warehouse is. With that information in mind, learners can apply these concepts to their designs. Reporting Data Versus Analyzing Data The difference between reporting information and analyzing data is huge.

I like to think of reporting as "I know what I want.

Reporting Data Versus Analyzing Data

Just show me the numbers. For example, a sales manager may ask, "How were our sales numbers for last week?

This manager has a specific report design in mind and knows how to react based on the numbers presented in that report. Analysts typically are confronted with a problem such as "I don't know what I want, but I'll know it when I see it. The sales manager may ask a question like, "Why did we miss our quota last month? The process of reviewing the data is more than just looking at a list of numbers.

Perspectives on those numbers need to change as the analysis continues. A Little Personal History When I first started working in information technology, the goal of database design was normalizing data to remove redundancy and to minimize overall size.

This process leads to complex databases with many tables joined together in complex ways. These databases were very efficient for the entry and maintenance of data, but information technology experts were needed to create efficient queries to extract information from that data. As a new programmer, I was taught how to design queries based on report requests.

  • Existing agile developers will find it interesting because it shows how to extend Scrum-based and Kanban-based strategies to provide a coherent, end-to-end streamlined delivery process;
  • The disadvantages are the increased complexity and the increased chances of cross schema referential integrity problems introduced by caches;
  • I need to create a database that allows me to link the numbers to the attributes.

Data warehouses were touted as a way to improve the ability of report authors to extract information from a database. As an IT professional, it took me a while to really understand the importance of these types of databases: It comes back to looking at a report and designing a database specifically for writing such reports. Normalized database are designed for easy data entry and maintenance, but not for easy reporting. Numbers With all of the reports that I have read or created, two pieces of information are key.

First are the numbers. I can think of only a handful of reports I've written that didn't have numbers that managers wanted to see.

  1. For example, a CustomerOrders aggregate table would store summary totals of the orders for customers. As you saw in Table 1 data marts are designed to support flexible, unpredictable access to data whereas data warehouses are not designed this way.
  2. Data warehouses were touted as a way to improve the ability of report authors to extract information from a database.
  3. The primary disadvantages are that partitioning will complicate your mapping efforts Chapter 11 and queries can become more complex because you need to work with several tables to support a single concept. Treat data extraction requests as new requirements.
  4. Page 1 of 1. Treat data extraction requests as new requirements.

I don't mean codes and identifiers; I'm talking about the numbers that drive the business. Managers will often look for the numbers first and then see the other piece of information. If a report has no numbers, the managers won't know whether they're meeting their performance metric targets.

When you create a reporting database, you want to have a grouping of the key performance indicators used by the organization. Perspectives and Insights After looking at the numbers, managers want to provide perspective on the numbers that are being presented. These attributes will organize the numbers in ways that make sense to the user. As I would build reports, the managers would ask for more information about the customers, products, time periods being reported, and the salespeople.

So, when creating a reporting database, you want to organize the attributes logically to allow for added perspectives and insights into why the numbers are what they are. In class, we describe these sets of attributes as providing the "who," "what," "where," "when," and "why" for the numbers.

  1. This is particularly true for any reports that are sent to your customers, such as invoices.
  2. An example is the differences between profit margin as currency and as a percentage of the business. There are other strategies that a good DBA can implement to improve reporting performance, strategies that Larry Greenfield nicely summarizes.
  3. As a database designer, you should include all the numbers that the business will use. As I would build reports, the managers would ask for more information about the customers, products, time periods being reported, and the salespeople.

The Star Schema This was the link I needed to help me understand the design of reporting databases. I need to create a database that allows me to link the numbers to the attributes.

With that, I finally understood what a star schema is. First, reporting databases should have a table for the key performance indicators that you want to include in the reports.

Reporting Databases: The Two Things You Need to Know

This is known as a fact table. As a database designer, you should include all the numbers that the business will use. Sometimes that means that the numbers will be similar, but calculated differently. An example is the differences between profit margin as currency and as a percentage of the business.

The goal for the fact table is that your report authors can perform fewer calculations to generate the desired results. This strategy allows for a simple solution for the writers of queries, as well as consistency in the use of the calculated fields. Along with the key performance indicators, you'll need to include the key fields that will link to the tables of attributes.

  • Data warehouses were touted as a way to improve the ability of report authors to extract information from a database;
  • All of these things influence the performance of your queries and thus your reports;
  • All of these things influence the performance of your queries and thus your reports.

The second type of table contains all of the common attributes that report writers will use to provide context to the facts. This table is commonly known as a dimension table.

A number of dimension tables will help to answer the "who," "what," "when," "where," and "why" of the data. Each table should contain all of the appropriate information in a specific area. For example, the employee dimension table should have all of the information that could be used in a report for employees.

Ideally, dimension tables should only be linked to fact tables, and facts should only be connected to dimensions. If you begin to have sets of attributes pointing to other sets of attributes, you are beginning to normalize the data. This approach reduces the simplicity of the star schema and increases the complexity of the queries to retrieve the data.

  • Just show me the numbers;
  • This sort of basic knowledge is taught in database certification courses and I highly recommend that all Agile DBAs become certified on the database technologies that they work with;
  • An example is the differences between profit margin as currency and as a percentage of the business;
  • Folding and envelope capacity are issues that may you need to be aware of as well;
  • When you are building them into your application you may choose to code them yourself or to use an integrated reporting tool;
  • Combinations are also possible.

I hope you see value in this discussion, and I encourage you to read more about data warehousing and online analytical processing. My goal in this article is simply to start the discussion about these types of databases through this simple presentation. Roger Johnson is a learning consultant on IBM Cognos technologies, delivering a wide variety of courses focused on the needs of his learners.

His background includes application development and technical education across many industries and technologies. He can be reached by email roger. Page 1 of 1.