Building a Pod from a Relational Database

Since we are now already experienced with the Pod Builder, this section will not get into details of some of the obvious steps involved in creating the Project Pod such as assigning roles, setting data preferences and so on.

As discovered previously, Calbro publishes the data for projects and business units in their PPM database. As we open up and analyze the database, we realize that the schema is normalized for use within the PPM application and hence, is not directly usable for metrics extraction. The schema diagram is shown below:

(Click the image to expand it.)

• • • Data preparation
    • Data preparation using SQL Views
    • Creating an RDBMS Data Source
    • Creating a Data View
    • Creating the Project Pod
    • Putting it all together

Data preparation

Preparing the data before exposing it to the Pod Builder is necessary since the Pod Builder tool is designed for the visualization of data rather than the aggregation or manipulation of large volumes of data. To prepare the data before using it with the Pod Builder tool, you should perform the following tasks that are relevant, in the order specified so that you create a subset of your data:

1. Filter the data.
2. Aggregate the data.
3. Transform epoch integer fields to date/timestamp fields.
4. Perform enumerated value translations.

Since Pod Builder only supports the creation of one Data View per relational table, SQL View or Excel worksheet, we must prepare the data for consumption by the Pod Builder by creating SQL Views on top of this normalized schema.

Using materialized views

In preparing your data, you should consider whether you should use materialized views or SQL views.

Since a SQL view created on an RDBMS database is a virtual table, no data is actually stored in the table. The data is retrieved at run time using the SQL in the view creation statement.

In a materialized view, however, the data is stored and retrieved from the storage at run time. The SQL in the view creation statement is used when the view data is refreshed. If you use materialized views, you create the materialized view and then set up a periodic data refresh process for that view.

A materialized view requires more effort to create and maintain, but it yields better run-time performance and fewer hits to the underlying database table. However, materialized views do not provide current data, as the data is only as fresh as the most recent refresh of the materialized view. Using a standard view is probably the best choice when data volumes are low and data volatility is high. Using a materialized view is the best choice for optimum performance when data volumes are high and some latency in data currency is tolerable.

BMC recommends that the solution be tested with production data volumes using both standard and materialized views before choosing the approach that is best for your site.

Data preparation using SQL Views

Based on our UI requirements, and the data analysis, we decide that we will need two SQL Views:

1. A SQL View that shows a comprehensive list of business services and their associated projects and its details. Example:
   
   

   CREATE VIEW BSD_SIM_CALBRO_PROJECTS_VIEW AS
   SELECT
   services.NAME as BUSINESS_SERVICE_NAME,
   projects.NAME as PROJECT_NAME,
   projects.JUSTIFICATION,
   projects.OVERALL_HEALTH,
   projects.SCHEDULE_HEALTH,
   projects.FINANCIAL_HEALTH,
   projects.IMPORTANT,
   projects.STATE,
   projects.STATUS,
   projects.BUDGET
   FROM
   BSD_SIM_CALBRO_BUSINESS_SERVICE services,
   BSD_SIM_CALBRO_PROJECTS projects,
   BSD_SIM_CALBRO_BUSINESS_SERVICE_PROJECTS services_projects
   WHERE
   services.GUID = services_projects.BUSINESS_SERVCE_GUID
   AND
   projects.GUID = services_projects.PROJECT_GUID

2. A SQL View that shows a comprehensive list of projects and their associated business units and its details. Example:
   
   

   CREATE VIEW BSD_SIM_CALBRO_BU_VIEW AS
   SELECT
   projects.NAME as PROJECT_NAME,
   bu.name as BU_NAME,
   project_bu.PROJECT_BUDGET
   FROM
   BSD_SIM_CALBRO_BU bu,
   BSD_SIM_CALBRO_PROJECTS projects,
   BSD_SIM_CALBRO_PROJECT_BU project_bu
   WHERE
   bu.GUID = project_bu.BU_GUID
   AND
   projects.GUID = project_bu.PROJECT_GUID

With these SQL Views created in our database, we are ready to build Data Views on top of them in the Pod Builder.

Creating an RDBMS Data Source

We can create a new Data Source by clicking on the New Custom Data Source icon, and entering the connection information to the relational database. In this example, we are using a Microsoft SQL Server 2008 database as shown below. For more information, see Setting-a-custom-data-source-with-a-relational-database.

(Click the image to expand it.)

Creating a Data View

Now that a Data Source has been created, BSM Dashboard will need the schema and the SQL Views where the data resides. We will create two Data Views in the Pod Builder, to correspond to each relevant SQL View.

The Calbro Projects data view is shown below.

(Click the image to expand it.)

The Calbro Business Units data view is shown below:

(Click the image to expand it.)

Now that the relevant data sources and data views have been set up, we are ready to create pods.

Creating the Project Pod

We will skip the steps prior to the Pod Builder Design Panel since they are simple and have already been covered in the section on the Vendor Pod.

1. In the Pod Builder Design Panel, in the top-left pane, we will select a bar chart. We will then select the PROJECT_NAME (category) and BUDGET (series) columns to bind to this chart. We will also create UI labels for these columns by clicking on them and filling in a text label. Finally, we will check the Data Prefs checkbox for the PROJECT_NAME column to allow the end users to select the projects displayed in this pod, as shown below:
   
   (Click the image to expand it.)
   
   
   
   This results in the following design screen:
   
   (Click the image to expand it.)
   
   
2. In order to fulfill Calbro’s requirement that the data in this chart be filtered by the business service selected in the Business Service Summary pod, we will click the Pod Interaction button in the left hand chart and make the appropriate selections in the Pod Interaction Panel as shown below.
   
   (Click the image to expand it.)
   
   
3. At this point, this chart in this pod is set up to receive communication from other pods in the dashboard. We will finish designing this chart by clicking Options to set up chart titles and X-Y axis labels (screenshot omitted for brevity).
4. Moving on to the right-hand side pane, we will select a donut chart to show the breakdown of the various business units and their budgets. We will associate this chart with the PROJECT_BUDGET (series) and BU_NAME (category) columns of the Calbro Business Units data view.
   
   (Click the image to expand it.)
   
   
5. As per Calbro requirements, the data in this chart needs to be filtered by the selection of the vendor in the left-hand side chart. Hence, we set up the appropriate options in the Pod Interaction Panel as shown below:
   
   (Click the image to expand it.)
   
   
6. Now, let’s test the filtering behavior in the pod by selecting a vendor in the left-hand side chart. As expected, we see the data in the donut chart on the right-hand side filtered to show only contracts for the selected vendor, as illustrated below:
   
   (Click the image to expand it.)
   
   
7. Moving on to the bottom side pane, we will select a grid chart to show the various details of every project associated with a business service. We will associate this chart with various columns in the Calbro Projects data view, taking care to include PROJECT_NAME (category).
   
   (Click the image to expand it.)
   
   
8. As per Calbro requirements, the data in this chart needs to be filtered by the selection of the business service in the Business Services Summary pod. Hence, we set up the appropriate options in the Pod Interaction Panel as shown below:
   
   (Click the image to expand it.)
   
   
   
   Finally, we are at a stage where our design is finished, as the following screenshot illustrates:
   
   (Click the image to expand it.)
   
   
9. In the next few steps, we will publish this pod to the Calbro custom Pod Category, save this pod, set the Data Preferences and assign roles to this Pod (screenshots omitted for brevity).

At this point, we have successfully created the Project Pod.

Putting it all together

Calbro has been able to create their Calbro Dashboard using out-of-the-box pods from BMC and custom pods created via the Pod Builder. Let’s login to the BMC Dashboards for BSM Executive Console, add the Project Pod to the Calbro Dashboard and see it interact with the other Calbro Dashboard pods. The following screenshot shows the Calbro Dashboard.

(Click the image to expand it.)