Unsupported content This version of the documentation is no longer supported. However, the documentation is available for your convenience. You will not be able to leave comments.

Building a Pod from an Excel Spreadsheet

This section explains the procedures for building a Pod from an Excel spreadsheet:

As discussed previously, Calbro publishes the data for vendors and their associated contracts in a Microsoft Excel spreadsheet, within the Vendor Worksheet and the Contracts Worksheet:

(Click the image to expand it.)

excel_1.jpgexcel_2.jpg

Since BMC Dashboards for BSM supports Excel spreadsheets as a data source, we can connect to this spreadsheet using the Pod Builder Tool. Before the product can access the spreadsheet, the spreadsheet must be copied to the product installation folder.

Note

 To access the Excel functionality, the BMC Atrium DIL component of BMC Dashboards for BSM must be installed on a Microsoft Windows computer.

Copying an Excel spreadsheet to the BMC Dashboards for BSM installation folder

BMC Dashboards for BSM looks for Excel spreadsheets in the following sub-folder of its installation folder:

DIL\CIS\apps\dil\excel_ds

In a typical installation, this translates to:

C:\Program Files\BMC Software\BMCDashboardsForBSM\DIL\CIS\apps\dil\excel_ds

In order for BMC Dashboards for BSM to access this spreadsheet, it must be copied to this location.

Note

 To avoid manual copies, a scheduled task or cron job can be set up which can copy the Excel spreadsheet to the BMC Dashboards for BSM installation folder.

Creating an Excel Data Source

In order to retrieve data from the Excel spreadsheet, we will create a Data Source. We’ll do this by:

  1. Logging onto the BMC Dashboards for BSM Administration Console, typically available at the following URL:

    https://<dashboards_installation_host_name>/bsmdashboards/admin.

  2. Launching the Pod Builder Tool by clicking on its icon as shown below:

    (Click the image to expand it.)

    excel_step2.jpg

  3. Creating a Data Source by clicking the Data Sources button, as shown below:

    (Click the image to expand it.)

    excel_step3.jpg

  4. Clicking the New Custom Data Source button, setting the Type to Excel and giving the Data Source a name such as Calbro Vendors. After this, we’ll select the Vendors spreadsheet from the discovered list, which was populated when we set the Type to Excel, and save the data source, as shown below:

    (Click the image to expand it.)

    excel_step4.jpg

Creating Data Views

Excel spreadsheets are composed of one or more worksheets, analogous to tables in a database. Now that the Data Source pointing to the Vendor spreadsheet has been created, BMC Dashboards for BSM will need these worksheet names.

Since BMC Dashboards for BSM uses Data Views to connect to work sheets or named ranges in Excel spreadsheets (or schema tables and views in the case of relational databases), we’ll create two Data Views, one for each worksheet: Vendors and Contracts.

Data Views can be created by clicking the Data Views button in the Pod Builder Tool.

(Click the image to expand it.)

excel_dataviews_step0.jpg

We can create the two Data Views by:

  1. In the Data View Management dialog, clicking New.
  2. In the Name and Type page of the Configure Data View Wizard, specify Calbro Vendors as the data view name, select Single and click Next.

  3. In the Data Set page, selecting the Excel data source that was just created and selecting the Vendor$ table.
    (Click the image to expand it.)

    excel_dataviews_step3.jpg

    At this point, BMC Dashboards for BSM will fetch the worksheets in this Excel document.

  4. Selecting all of its columns and specifying serviceName as the alias for the BUSINESS_SERVICE column.

    (Click the image to expand it.)

    excel_dataviews_step4.jpg

    Note

    Pods and charts in BMC Dashboards for BSM communicate with each other via special pod events, containing key-value pairs. The key is matched to a column or alias name in the underlying Data View and the data is filtered out by matching it to the value for the key. Hence, in order for a chart in a pod to respond to an event from another pod (that is, filter its data based on a value selected in the other pod), a column or alias name in its Data View must match a key in the pod event.

    The Business Services Summary pod sends out a pod event each time the user selects a business service. Since Calbro desires that the Vendor pod only show data relevant to the selected business service, the Vendor pod must be set up to consume pod events from the Business Services Summary pod.

    Since this pod event contains a key called serviceName whose value is the name of the selected business service, the Vendor data column BUSINESS_SERVICE_NAME must match this key. Since we don’t wish to change anything in the underlying Excel spreadsheet, we will create a Data View Alias called serviceName for this column, thereby ensuring that when the Vendor pod receives the pod event, it will map it to the corresponding data column in the Excel spreadsheet.

  5. Repeating these steps to create another Data View named Calbro Contracts for the Contracts spreadsheet.

    Note

    Since the data schema is not complex, our corresponding Data View is very simple. As much as possible, we should prepare the data before exposing it to BMC Dashboards for BSM since the product is focused on visualization rather than data manipulation and transformation. 

    With data sources and data views set up, it’s time to create pods.

Creating the Vendor Pod

The Pod Builder tool ships with a wizard-like Create Pod Guided Workflow which walks an administrator through every step of the pod creation process. In this white paper, we will only cover the steps that are relevant to our discussion. These include:

  • Launching the Create Pod Guided Workflow
  • Selecting charts and binding data from Data Views in the Pod Builder Design Panel
  • Setting up Pod Interaction options in the Pod Builder Design Panel
  • Setting up Data Preferences and Publishing the pod to the Pod Catalog
  • Setting up Role Assignments for the pod

We will start by:

  1. Clicking the Create Custom Pod button to launch the create pod guided workflow.
    (Click the image to expand it.)

    excel_vendor_step1.jpg

    This will bring up the “Welcome Panel” of the workflow:
    (Click the image to expand it.)

    excel_vendor_step1a.jpg

  2. Next, we will click anywhere on the Welcome Panel. This will advance us to the next screen, where we will fill in the pod name, description and select a layout. We will select a Side by Side layout (screenshot omitted for brevity).

  3. The next panel will lead us to the Pod Builder Design Panel, which is divided into two panes, one for each chart. In the left-hand side pane, we select a column chart to display vendors and vendor budgets.

    (Click the image to expand it.)

    excel_vendor_step3.jpg

  4. We will now bind this column chart to the Calbro Vendors data view we created earlier, selecting the 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 NAME column to allow the end users to select the vendors displayed in this pod, as shown below:
    (Click the image to expand it.)

    excel_vendor_step4.jpg

    This series of steps results in the following design screen:
    (Click the image to expand it.)

    excel_vendor_step4a.jpg

  5. In order to fulfill Calbro’s requirement that the data in this pod 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.)

    excel_vendor_step5.jpg

  6. At this point, the Vendor 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).

  7. Moving on to the right-hand side pane, we will select a donut chart to show the breakdown of the various contracts and their impact on the vendor budget, and associate the chart with the BUDGET (series) and CONTRACT (category) columns of the Calbro Contracts data view.

    (Click the image to expand it.)

    excel_vendor_step7.jpg

  8. 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.)

    excel_vendor_step8.jpg

  9. 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.)

    excel_vendor_step9.bmp

  10. The next step in the Pod Builder Design Panel is to publish the pod to a custom Pod Category, as shown below:
    (Click the image to expand it.)

    excel_vendor_step10.jpg

  11. Finally, let’s save this pod and move to the next step, which is to set the Data Preferences.
    (Click the image to expand it.)

    excel_vendor_step11.jpg

  12. In the Data Preferences Panel, we will configure the data preferences for this pod and also, make this pod available to end users. Each of the column for which we selected Data Prefs in the Pod Builder Design Panel is seen as a separate tab UI in the Data Preferences Panel. The Available column contains all the unique data values for Data Prefs column. We will move some of these values to the Selected column to make these data values available to the end users. We will also make some of these User Defaults, thereby making sure that this pod will use these default values when first used by the end user. This way, we minimize the amount of configuration required by the end user.

  13. We can also preview the result so that we can gauge exactly what the end-user will see as a result of our configurations.

    (Click the image to expand it.)

    excel_vendor_step13.jpg

  14. We will check the Make Available to Users check box so that it is available for end-users.

  15. Finally, permissions for this pod can be assigned so that only authorized users may have access to it. In this case, we grant access to this pod to the “IT Director” and “Service Delivery Manager” roles. Any users assigned these roles will now be able to add these pods to their dashboards.

    (Click the image to expand it.)

    excel_vendor_step15.jpg

Seeing the Vendor Pod in Action

Let’s login to the BMC Dashboards for BSM Executive Console, add the Vendor Pod to the Calbro Dashboard and see it interact with the other Calbro Dashboard pods. When we click on a business service tile, such as WWW Presence in the Business Services Summary pod, we see the Incidents Details and the Vendor pod get updated to show only those incidents and vendors that are associated with the selected business service.

(Click the image to expand it.)

excel_vendorinaction.jpg

In this section, we built a sophisticated pod that:

  • Fetched data from an Excel spreadsheet
  • Showed metrics in multiple charts
  • Filtered data according to user input
  • Responded to communication from other pods
  • Allowed data customizations from end users
  • Allowed default configurations so that it was ready with data at first use by end users

While Pod Builder allows further data and UI customization options, these features fall outside the scope of this white paper and are not covered. Please refer to the product documentation for further information on this topic.

While some pods in the customer environments will require minimal setup, other pods may be built upon a complex data schema. In the next section, we explore a pod that requires such an intensive data setup.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*