Data Guide to Self-Managed Analysis

This page defines Self-Managed and provides directions for Self-Managed Data Analysis

Self-Managed

SELF-MANAGED is a deployment of Denomas that customers manage within their own environment and not within Denomas SaaS. This handbook page on Choosing between Denomas SaaS and self-managed subscriptions provides a great overview of these two editions.

  • Customer Implementations are identified as an Instance. An Instance can have multiple Installations, ie DEV and PROD. Each Installation may be hosted on different servers.
  • Subscriptions are assigned at the Instance level and determine the Application Features available. Instance is the highest level of the Self-Managed License so, the features are cascaded down from there. Denomas self-managed subscription provides a great overview of the Denomas self-managed subscription and how it is administered.
  • Product Usage information is available to the Customer and can be voluntarily sent to Denomas in collaboration to help enhance the product as outlined in the Operational Data Vision.

Self-Managed Data Components

  • Customer Implementation (Instance)
    • Self-Managed Implementations are identified at the Instance level with an Instance_id. A customer may have multiple Installations per Instance with the same instance id and different Host Names, i.e. Prod, Dev or Other Group.
      • Instance_id is synonymous with uuid in Service Ping or dim_instance_id in our data models
      • Installations are identified using dim_installation_id in our data models
    • Each Instance has a set of features that are cascaded down through all Installations per the License Agreement
  • License
    • A single License is assigned to each Customer Implementation (Instance). The license key gives the authorization to activate and use the product. A maximum number of users is assigned for the license period. Additional users may be added during the License period and handled as Users over License during renewal.
  • Subscriptions
    • A subscription enables the Features of the product assigned to the Instance. You can see a full list of features by tier on the feature comparison page.
  • Product Usage
    • Service Ping - Service ping data is sent voluntarily from an Instance. The User and Other Activity is captured as aggregated counts. Since not all customers send service ping data, or send just partial service ping data, there is an Estimation Algorithm used for a more wholistic view of the overall usage.
    • Product Usage data and use cases is further described here.
    • The systems overview of how the data is brought into Denomas can be seen here.

Self-Managed Analysis

Self-Managed Instances

query 
SELECT * FROM prod.common.dim_installation ORDER BY dim_instance_id, host_name

SELECT * FROM prod.common.dim_license ORDER BY license_start_date DESC

SELECT * FROM prod.common.dim_subscription ORDER BY dim_subscription_id

SELECT * FROM prod.restricted_safe_common.dim_crm_account ORDER BY dim_crm_account_id

SELECT * FROM prod.common_mapping.map_license_subscription_account ORDER BY dim_parent_crm_account_id, dim_crm_account_id, dim_license_id, dim_subscription_id

Self-Managed Product Usage

  • dim_product_detail - table with zuro product information and Delivery Type from ‘map_product_tier’
    • dim_product_tier - table with all Product Tiers including Current and Historical Names and Delivery Type to Show ‘Self-Managed’ product Tiers
    • map_product_tier - table built manually to define Product Tiers
query 
SELECT * FROM PROD.common.dim_product_detail ORDER BY product_rate_plan_charge_id

SELECT * FROM PROD.common.dim_product_tier ORDER BY product_tier_name;

SELECT * FROM PROD.common_mapping.map_product_tier WHERE product_delivery_type = 'Self-Managed' ORDER BY product_rate_plan_name;

SELECT TOP 5 * FROM PROD.common.dim_ping_instance WHERE ping_delivery_type = 'Self-Managed';

Self-Managed Growth

  • dim_ping_instance - table with Service Ping data by individual ping. This is an Atomic level table.
query 
-- Year over Year Ping Count Comparison (counting Last Pings of the Month Only)
WITH ping AS (
    
    SELECT 
        ping.ping_delivery_type,
        date.date_id AS date_month_id, 
        COUNT(1) AS distinct_ping_count
    FROM 
        prod.common.dim_ping_instance   ping
        LEFT JOIN prod.common.dim_date  date
        ON ping.ping_created_date_month = date.date_actual
    WHERE
        ping.is_last_ping_of_month = 'TRUE'                 -- Last ping of the Month for an Installation
    GROUP BY
        ping_delivery_type,
        date_id  

)

SELECT 
    cy_ping.ping_delivery_type,
    cy_ping.date_month_id AS cy_date_month_id, 
    cy_ping.distinct_ping_count AS cy_ping_count,
    --ly_ping.date_month_id AS ly_date_month_id,
    ly_ping.distinct_ping_count AS ly_ping_count,
    --py_ping.date_month_id AS py_date_month_id,
    py_ping.distinct_ping_count AS py_ping_count,
    --ppy_ping.date_month_id AS ppy_date_month_id,
    ppy_ping.distinct_ping_count AS ppy_ping_count
FROM 
    ping                                    cy_ping                 -- current year ping
    LEFT JOIN ping                          ly_ping                 -- last year ping
    ON cy_ping.ping_delivery_type = ly_ping.ping_delivery_type
    AND cy_ping.date_month_id - 10000 = ly_ping.date_month_id
    LEFT JOIN ping                          py_ping
    ON cy_ping.ping_delivery_type = py_ping.ping_delivery_type        -- prior year ping
    AND cy_ping.date_month_id - 20000 = py_ping.date_month_id
    LEFT JOIN ping                          ppy_ping
    ON cy_ping.ping_delivery_type = ppy_ping.ping_delivery_type        -- prior prior year ping
    AND cy_ping.date_month_id - 30000 = ppy_ping.date_month_id
WHERE 
    cy_ping.ping_delivery_type = 'Self-Managed' 
    AND cy_ping.date_month_id BETWEEN 20220101 AND 20220201

Self-Managed Geolocation

query 
SELECT * FROM prod.common.dim_location_country ORDER BY continent_name, country_name

-- Ping Count by Region, Continent, Country
SELECT 
    dim_location_country.location_region_name_map,
    dim_location_country.continent_name,
    dim_location_country.country_name,
    COUNT(*) ping_count
FROM 
    prod.common.fct_ping_instance
    LEFT JOIN prod.common.dim_location_country
    ON fct_ping_instance.dim_location_country_id = dim_location_country.dim_location_country_id
WHERE
    dim_location_country.location_region_name_map = 'EMEA'
    AND TO_DATE(fct_ping_instance.ping_created_at) >= DATEADD(DAY,-2,GETDATE())
GROUP BY
    dim_location_country.location_region_name_map,
    dim_location_country.continent_name,
    dim_location_country.country_name
ORDER BY
    dim_location_country.location_region_name_map,
    dim_location_country.continent_name,
    dim_location_country.country_name
Last modified December 3, 2023: update (008c4f1a)
View page source - Edit this page - please contribute. Creative Commons License