Sample Date Functions for Analytics

For this example we start off by creating a new domain. Creating a new domain is done by clicking the 'Create' drop-down box and selecting Domain. Below we will utilize the value of 'CPUMemory Domain' as it is best practice to include the type of object after the name.

Picture: Add New Domain Screen

Another good practice is to keep your save locations different for each type of object {Domain, Ad Hoc View, Report}. Below, the 'Data Source' is 'Create with Domain Designer…' which will take us to the next section. For the sample report we choose the following tables:

  • dim_date
  • dim_device
  • dim_group
  • hourly_load_1
  • hourly_load_5
  • hourly_load_15
  • hourly_mem_avail_real tables

    Picture: Selected Tables

The "dim_date" was chosen because we will be utilizing the "measure date_day_of_week" to get the Nth day of the week and we will be using the "measure date_month" to get the Nth month of the year.

The "dim_device" is to add the device name in the table and will serve as the base for the left joins.

The "dim_group" will added to the group name in the table.

The remaining tables will be left joined to gather additional metrics. Another table will be chosen as an additional base for left joins. The reason for the multiple set of left joins is to align the hour time between the metric tables and display the hour the time. We want to choose the table will the most values.

Picture: Domain Joins

Above an example of the left joins. The "dim_device:device_key" will be used as much as possible to link the other tables to the device. The left join is used because we do not want to eliminate any rows we do not have values for. The second join set is "dim_date:date_key" which is used to line the metrics to the correct day and since this table does not have hour field we will use a third table. The third table base, which is our hand picked metric "table:fct_ts_gmt" is used to align the metric collections based on hour. Our goal is for the collection time to line up across the report for the different metrics we are using.

On the Display section of the Domain creation we want to select all the fields or the one join set item. It is recommend to update the name of the fields that are the same before finishing the report. In our example we will update all the "fct_avg" fields with their corresponding table name which will allow easier identification in the ad hoc view creation.

Picture: Updating Identification

Update any of the field's id and description you may want to use in the ad hoc view as this will make the completion of an ad hoc view easier. When we are done, we will click submit.

To create an Ad Hoc View, click on the top 'Create' and choose 'Ad Hoc View'. Next we want to choose our domain we created or an existing domain.

Picture: Selecting the domain

We are choosing "CPUMemory Domain" and selecting all the fields. After we have completed the setup dialogs we will be configuring the Ad Hoc View page. To create calculated measures, click on the drop-down box to the right of the title 'Measures' and choose 'Create Calculated Measure...'

Picture: Creating a Calculated Measure

One of the measures we want to create is the hour in which the data collection was performed. We can do this by using the formula: "hour("fct_ts_gmt")" and "fct_ts_gmt" which comes from the the base of our third join. You may also be able to use "fct_ts_gmt."

Picture: Creating Hour of Day

We want to put a time stamp in our report for the metric, which can be done with the "any_metric:fct_ts_gmt" and "hourly_mem_avail_real:fct_ts." To clean up the view right click on the column and update the name. We will also change the format by right clicking on the column, choosing 'Change Date Format', and selecting "Apr 4, 2019 5:12:46 PM."

Picture: Changing Data Format for Hour - Picking the field

Picture: Changing Data Format for Hour - Changing the format

Next we will setup the column Day of Week by selecting the "measure dim_date:date_day_of_week." To setup the numeric value of the month we will use the "measure dim_date:date_month."

Picture: Measure Day of Week and Month



The remaining fields are device name, group name, and metrics. The following pictures displays the corresponding fields.

Picture: Adding the device name


Picture: Add the device group

Picture: Choosing metrics for Memory

Create any filters you are wanting to use by renaming the columns, and when you are done save the Ad Hoc View. Next clicking the floppy icon, choosing Save Ad Hoc View As ..., choosing the name "CPUMemory Ad Hoc View," saving to the AdHocView folder, and clicking Save.


Was this article helpful?
0 out of 0 found this helpful


Powered by Zendesk