Integrated Business Planning
With the maturity of the products that makes up a BI solution, organizations can create and maintain a technically integrated BI solution. With SQL Server, an organization can create a central repository for data and repurpose the data for use in PerformancePoint Services to communicate valuable business information.
On the back end, SQL source systems collect and store data in normalized forms through transactional systems that provide the capacity to insert, update, and delete data. The SQL solution enables these transactional systems, and with extract and load processes provides the potential to extend the business value and potential of this data. Using extract and load processes, an organization can then transform and optimize data into cubes and prepare it for use in scorecards and dashboards.
To better understand this, consider a transactional sales system that tracks and stores sales quantities and orders as they are entered (see Figure 1.2). The data elements are stored in a relational database, and the underlying architecture of the system might look something like the structure shown here.
Figure 1.2 SQL Server systems collect and store data that can be transformed into valuable business information.
This system captures and stores valuable information that a manager may want to use when making daily or long-term decisions. The challenge is to extract the most valuable data and to transform it into a usable form for performance management—in other words, to transform the data into information.
To optimize data for reporting and analysis, data elements are typically structured into measures and dimensions. Measures identify data that you want to analyze, such as sales, head count, defective products, and profit margin. Dimensions enable you to add context to one or more measures. For example, if we have a measure called Sales, and its value is $1,000, what does that mean? Is it total sales for a year, quarter, or month? Is it for all products or a specific product category? A number by itself does not mean much to most users. By adding one or more dimensions that contain information about time, products, and regions, for example, you can start to add context to the number $1,000. You can now analyze sales based on time, products, and region. A dimension can also include hierarchies that provide navigational paths. It can make it easier for the user to browse data by looking at sales for all years and then navigate to a specific year, quarter, or month.
To use the data for analyses, the data elements must be converted into measures (see Figure 1.3). This is done by associating data elements with a dimension. Raw data elements are considered facts, whereas dimensions group these facts by time or geography, for example. Associating a fact with a dimension enhances its informational value. In this case, it is a fact that the sales organization sold 10,000 shoes. It is important to know that 5,000 of these shoes were sold in Quarter 4. It is also important to know that of these 5,000 shoes, 3,000 were sold in Europe. The measure that surrounds the fact communicates a dimension. In this case, Quarter is an example of the Time dimension, and Europe is an example of the Geography dimension.
Figure 1.3 In this scenario, sales data is transformed into sales information displayed as KPIs on a Sales scorecard.
Dimensions allow you to measure facts in different ways to create and communicate valuable business information. You can then use these measures in the final step in this process, which occurs in PerformancePoint Services (which is where KPIs are built on available measures and presented in scorecards and dashboards). In this example, KPIs might display sales by quarter and by region. This information is populated dynamically from the underlying transactional database reflecting at all times a current view of the organization's sales performance.