Create your own tables


There are a number of occasions when adding your own data into Advanced MIS makes sense. Advanced MIS allows you to create your own data tables within the Data Warehouse.

You might want to:

This article sets out a number of useful approaches or questions where creating your own tables could be useful.

Basically counts over time. Some things in the application data are transient, but don’t necessarily warrant or generate events that can be counted.

Some example questions

Method overview

Create a table

This is a one time operation. As well as the fields related to counting that you want to keep, you will also want to ensure that you are capturing a time component. You would want to consider the following.

The design of the table —the columns and their types— will be up to you but would need to support whatever report you wanted to run.

An example create table syntax might look like this. We are going to track the status of lists in each time period over time to see how the flow of list statuses looks over the year.

CREATE TABLE IF NOT EXISTS list_status_trends (
    date_calculated DATE not null,
    date_reported DATE not null,
    published_count VARCHAR(255),
    published_with_changes_count VARCHAR(255),
    draft_count VARCHAR(255),
    archived_count VARCHAR(255),
    time_period VARCHAR(255)

You would then run a task in some program with scheduling ability to insert some data into this table.

Insert data

We’re going to capture the data weekly. The weekly automatic refresh of the Advanced MIS application data happens on a Sunday, so we would suggest that you run aggregations on a Monday.

The insert query might look like the example given here. The order of the fields should match the order they were created in.

INSERT INTO list_status_trends (
    getdate() as date_calculated,
    date_trunc('week', getdate()) as date_reported,
    count(CASE WHEN status='Published' then 1 end) as published_count,
    count(CASE WHEN status='Published With Unpublished Changes' then 1 end) as published_with_changes_count,
    count(CASE WHEN status='Draft' then 1 end) as draft_count,
    count(CASE WHEN status='Archived' then 1 end) as archived_count,
from f_rl_lists
group by time_period


You would need a scheduling tool to allow you to run this query on a regular basis. Some examples might be:

Access your captured trend data

You can use normal Business Intelligence tools or scripts to pull the data back, or include it in another query, just like you would any other data in Advanced MIS.