May
5

Sample Metric

imageThe Metric functionality in Arena is a very powerful tool for viewing changes in data over time (yes, I know, the UI could still use some improvements).  Essentially it allows you to create any number of metrics by specifying a SQL statement that just returns one numeric value.  Arena will then run that statement how ever often you decide, save the result, and chart the values over time. 

You can also set up a metric that isn’t linked to Arena data but instead is hand-entered (i.e. Total Weekend Attendance)

 

Membership Status Metrics

Since you may not have gotten any sample metrics out of the box, here’s a SQL script that will add a few for you…

DECLARE @OrganizationID int
SET @OrganizationID = (SELECT TOP 1 organization_id FROM orgn_organization)

DECLARE @AllUsers int
SET @AllUsers = (SELECT TOP 1 role_id FROM secu_role WHERE role_name = 'All Users')

DECLARE @ParentMetricID int
DECLARE @MetricID int

-- Create Parent Metric
INSERT INTO [Arena].[dbo].[mtrc_metric]
       ([date_created]
       ,[date_modified]
       ,[created_by]
       ,[modified_by]
       ,[parent_metric_id]
       ,[metric_order]
       ,[graph_type]
       ,[title]
       ,[series_caption]
       ,[description]
       ,[source_summary]
       ,[collection_frequency]
       ,[collection_last_date]
       ,[collection_sql_statement]
       ,[organization_id]
       ,[aggregate_type]
       ,[numeric_x_values])
     VALUES
       (GETDATE()
       ,GETDATE()
       ,''
       ,''
       ,NULL
       ,1
       ,-1
       ,'Membership Status'
       ,''
       ,'Metric to show the number of people of each Membership Status over time'
       ,''
       ,0
       ,'1/1/1900'
       ,''
       ,@OrganizationID
       ,'MAX'
       ,0)    

SET @ParentMetricID = SCOPE_IDENTITY()

INSERT INTO [Arena].[dbo].[secu_permission]
       ([date_created]
       ,[date_modified]
       ,[created_by]
       ,[modified_by]
       ,[object_type]
       ,[operation_type]
       ,[subject_type]
       ,[object_key]
       ,[subject_key])
    VALUES
        (GETDATE()
        ,GETDATE()
        ,''
        ,''
        ,6
        ,0
        ,0
        ,@ParentMetricID
        ,@AllUsers )

INSERT INTO [Arena].[dbo].[secu_permission]
       ([date_created]
       ,[date_modified]
       ,[created_by]
       ,[modified_by]
       ,[object_type]
       ,[operation_type]
       ,[subject_type]
       ,[object_key]
       ,[subject_key])
    VALUES
        (GETDATE()
        ,GETDATE()
        ,''
        ,''
        ,6
        ,1
        ,0
        ,@ParentMetricID
        ,@AllUsers )

DECLARE @LookupID int
DECLARE @LookupValue varchar(50)
DECLARE @LookupOrder int

DECLARE MemberStatusCursor CURSOR LOCAL FOR
    SELECT
        lookup_id,
        lookup_value,
        lookup_order
    FROM core_lookup L
    INNER JOIN core_lookup_type LT ON LT.lookup_type_id = L.lookup_type_id
    WHERE LT.guid = '0B4532DB-3188-40F5-B188-E7E6E4448C85'

OPEN MemberStatusCursor

FETCH NEXT FROM MemberStatusCursor
INTO @LookupID, @LookupValue, @LookupOrder

WHILE @@FETCH_STATUS = 0
BEGIN

    INSERT INTO [Arena].[dbo].[mtrc_metric]
           ([date_created]
           ,[date_modified]
           ,[created_by]
           ,[modified_by]
           ,[parent_metric_id]
           ,[metric_order]
           ,[graph_type]
           ,[title]
           ,[series_caption]
           ,[description]
           ,[source_summary]
           ,[collection_frequency]
           ,[collection_last_date]
           ,[collection_sql_statement]
           ,[organization_id]
           ,[aggregate_type]
           ,[numeric_x_values])
        VALUES
           (GETDATE()
           ,GETDATE()
           ,''
           ,''
           ,@ParentMetricID
           ,@LookupOrder
           ,0
           ,'Number of ' + @LookupValue + 's'
           ,@LookupValue + 's'
           ,'Number of people classifed as "' + @LookupValue + '"'
           ,''
           ,2
           ,GETDATE()
           ,'SELECT COUNT(*) AS people_count FROM Arena.dbo.core_person WHERE record_status = 0 AND member_status = ' + CAST(@LookupID as varchar)
           ,@OrganizationID
           ,'MAX'
           ,0)

    SET @MetricID = SCOPE_IDENTITY()

    INSERT INTO [Arena].[dbo].[secu_permission]
           ([date_created]
           ,[date_modified]
           ,[created_by]
           ,[modified_by]
           ,[object_type]
           ,[operation_type]
           ,[subject_type]
           ,[object_key]
           ,[subject_key])
        VALUES
            (GETDATE()
            ,GETDATE()
            ,''
            ,''
            ,6
            ,0
            ,0
            ,@MetricID
            ,@AllUsers )

    INSERT INTO [Arena].[dbo].[secu_permission]
           ([date_created]
           ,[date_modified]
           ,[created_by]
           ,[modified_by]
           ,[object_type]
           ,[operation_type]
           ,[subject_type]
           ,[object_key]
           ,[subject_key])
        VALUES
            (GETDATE()
            ,GETDATE()
            ,''
            ,''
            ,6
            ,1
            ,0
            ,@MetricID
            ,@AllUsers )

    FETCH NEXT FROM MemberStatusCursor
    INTO @LookupID, @LookupValue, @LookupOrder

END

CLOSE MemberStatusCursor
DEALLOCATE MemberStatusCursor

Running this script will create a parent “Membership Status” metric and then a child metric for each Member Status you’ve defined (and grants the “All Users” role view and edit permissions).

Each of the child metrics will chart the number of people of that member status over time.  As long as the ArenaProcessMetrics agent is running, you will see a chart after it’s collected the metrics data twice (two weeks from now).

 

Historical Data

If you don’t want to wait two weeks to see your data and/or you want to see all your historical member status data up to this point, we can use the core_member_status_history table to generate historical membership status data for your metrics…

SET NOCOUNT ON
GO

DECLARE @ParentMetricID int
DECLARE @MinHistoryDate datetime
DECLARE @WorkingDate datetime
DECLARE @Status varchar(15)

SET @ParentMetricID = (SELECT MAX(metric_id) FROM mtrc_metric WHERE title = 'Membership Status' AND parent_metric_id IS NULL)
SET @MinHistoryDate = (SELECT MIN(date_created) FROM core_member_status_history)
SET @WorkingDate = GETDATE()

DELETE mtrc_metric_item
FROM mtrc_metric_item MI
INNER JOIN mtrc_metric M ON M.metric_id = MI.metric_id
WHERE M.parent_metric_id = @ParentMetricID

WHILE @MinHistoryDate IS NOT NULL
AND @WorkingDate > @MinHistoryDate
BEGIN

    SET @Status = CONVERT(varchar(10), @WorkingDate, 101) + '...'
    RAISERROR (@Status, 0, 1) WITH NOWAIT;

    WITH memberStatusTable (person_id, member_status)
    AS
    (
        SELECT
            person_id,
            ISNULL((SELECT TOP 1 member_status
                FROM core_member_status_history
                WHERE person_id = P.person_id
                AND date_created < @WorkingDate
                ORDER BY date_created DESC), p.member_status) AS member_status
        FROM core_person P
        WHERE date_created <= @WorkingDate
    )

    INSERT INTO mtrc_metric_item
    SELECT
         GETDATE()
        ,GETDATE()
        ,''
        ,''
        ,M.metric_id
        ,(SELECT COUNT(*)
            FROM memberStatusTable
            WHERE member_status = L.lookup_id)
        ,@WorkingDate
        ,''
        ,0
    FROM core_lookup L
    INNER JOIN core_lookup_type LT
        ON LT.lookup_type_id = L.lookup_type_id
    INNER JOIN mtrc_metric M
        ON M.title = 'Number of ' + L.lookup_value + 's'
        AND M.parent_metric_id = @ParentMetricID
    WHERE LT.guid = '0B4532DB-3188-40F5-B188-E7E6E4448C85'

    SET @WorkingDate = DATEADD(wk, -1, @WorkingDate)

END

This script will create data for the metrics you created above for as far back as you have data in the core_member_status_history table.

Posted by David Turner Arena Subscribe to RSS feed

2 Comments on Sample Metric

  1. dturner says:
    May 5, 2008 at 6:18 am

    In the first script you may need to change the database name if your database is not "Arena" (i.e. ArenaDB)

  2. Wesley Jones says:
    October 6, 2009 at 9:25 am

    David,

    Thanks for the scripts. I used the historical script so I could see what everything looked like.

    Now I would like to use the script that uses the agent to collect the information. Do I delete all the membership status metrics and run the top script or just run the top script on this page?

    Thanks for any help!

Leave a Reply

*