Tracking TM1 Usage

[jim wood]

Guys,

I have had a look at the good logging documentation and I haven't been able to find a way of tracking which users are accessing which cubes. My boss is trying to find out if certain cubes are still being used. If not we aim to bin them. Does anybody know the best method to achieve this?

I am working with a slow Unix server and version 9.1 SP3 (9.1.3) and would prefer to leave cube logging off to avoid the old performance slow down.

Many thanks,

Jim.

[John Hobson]

My boss is trying to find out if certain cubes are still being used. If not we aim to bin them. Does anybody know the best method to achieve this?

Remove access using security and wait to see if anyone squeals ???

[Alan Kirk]

Guys,

I have had a look at the good logging documentation and I haven't been able to find a way of tracking which users are accessing which cubes. My boss is trying to find out if certain cubes are still being used. If not we aim to bin them. Does anybody know the best method to achieve this?

I am working with a slow Unix server and version 9.1 SP3 (9.1.3) and would prefer to leave cube logging off to avoid the old performance slow down.

I would have thought }StatsByCubeByClient would be the answer to that. Obviously you won't want logging on on the cube; that's what slows down performance as well as generating huge log files. (I haven't seen a noticable performance hit in any version from just having performance monitoring itself on.) However a TI which exports the data from the cube to a text file every day or two would be sufficient if all you want to know is who's accessing the cubes. I have the config file set up to turn performance monitoring on automatically.

But John's idea also has a certain appeal...

[jim wood]

Performance monitor did look like the way forward. I'll look in to it. Thanks guys.

As for John's suggestion: I have come close to blocking access to all the cubes!!!

[lotsaram]

I was going to suggest 2 possibilities one of which was to read from }StatsByCubeByClient which Alan has already covered.

If users are accessing mainly from either Excel or TM1 web reports versus directly from the cube viewer and you are on a version with the TM1User function, the other alternative would be to design a tracking cube which would get written to via DBS each time a user recalculated a report. Using this method you can go one step further and track not only cube usage but also report usage and figure out which Excel reports are not being used and can be safely nuked.

[Steve Rowe]

Wasn't there a tool around a while back that many people were using that worked off the log file that TM1Top produces? Can't remember who wrote it or waht it was called... Was from the old forum days.

[wissew]

My boss is trying to find out if certain cubes are still being used. If not we aim to bin them. Does anybody know the best method to achieve this?

Remove access using security and wait to see if anyone squeals ???

I agree with John. Deny Access! You can go one step farther and move the cub, dims & views to a backup folder after a while for safety. Besides, gathering stats from the }stats cubes is time consuming and not entirely accurate.

Wes

[jim wood]

I was going to suggest 2 possibilities one of which was to read from }StatsByCubeByClient which Alan has already covered.

If users are accessing mainly from either Excel or TM1 web reports versus directly from the cube viewer and you are on a version with the TM1User function, the other alternative would be to design a tracking cube which would get written to via DBS each time a user recalculated a report. Using this method you can go one step further and track not only cube usage but also report usage and figure out which Excel reports are not being used and can be safely nuked.

TM1User Function. Is that in 9.1? If so how can that help?

[lotsaram]

I sometimes implement a report and cube usage tracking cube. Typically with the dimensions cube, client, report, date and time. (where cube and client are replicas of the control dimension with a total, reports is maintained with xdi, date is typically linear date rolled up but sometimes split into separate time dims, and time is typically 30 min blocks.)

Inputs to the cube are sourced from substringing the xl now function, tm1user and view functions with report name being manual. I read from the cube and increment with dbs each time the user recalcs. This allows tracking of cube and report use.

Obviously though it only tracks cube hits from an excel or web template where this is set up.

[Alan Kirk]

Besides, gathering stats from the }stats cubes is time consuming

Oh yeah, soooooo time consuming, what with TI doing all the work for you. Such a silly idea I'm surprised I even suggested it. I mean, it took me the better part of my trip in on the train this morning to knock up the code below which I need to do a total of... once. Thereafter it's necessary to undertake the time consuming task of browsing an attributes cube occasionally.

The greatest part of the time in writing the code below was when I took it from 9.0 to 8.2.12 and had to write workarounds for some missing functions.

and not entirely accurate

There have been times when I haven't been able to tally the exact cell numbers reported with what I know has happened, but I'd put that down to opacity regarding the calculation methods on Iboglix's part and isn't really relevant if all you want to know is "is someone accessing the cubes?" In all the tests I've done with PM (and there have been a few over the years) I've NEVER seen PM fail to report SOME access to a cube when a user has accessed it, or report a false positive when they haven't. I did once think there was one... a user swore blind that she hadn't accessed a cube when PM said that she had... but I later found out that she was using a report which read from that cube, and she wasn't aware that it did.

If you have some contrary information, feel free to share with the class.

In any case, the "time consuming" code is below.

Prolog

Code: Select all

# Process 000_DocumentCubeUseage

# ----------------------------------------------------------------------
# Process Purpose
# To identify the approximate date that a cube was last accessed.
#
# The process should be put in a chore which is scheduled to 
#  run daily at a set time.
#
# The process does the following:
# - Creates, if necessary, a dimension which lists all of the non-system
#    cubes on the server, and gives that dimension a string
#    attribute which can store when the cube was last
#    accessed. This is used in preference to the }Cubes dimension
#    because modifying system objects is not recommended.
#    The dimension is given a } prefix in this process (to 
#    keep it hidden from most users), but that can be modified 
#    at the Admin's discretion.
#
# - Creates an N level, zero suppressed view of the data in
#     the }StatsByCubeByClient cube.
#
# - Reads the data from that view. If the data relates to one of the 
#   non-system cubes, the date accessed attribute of the cube is 
#   updated in the dimension that has been created.
#
# - Generates a text file which outputs the current data in the 
#    }StatsByCubeByClient cube. If you want to know WHO accessed the
#    cubes on the dates shown in the dimension attributes, you can
#    cross-refer to the file from that date.
#  *** YOU NEED TO CHANGE THE OUTPUT PATH CONSTANT BELOW
#       FOR THIS TO WORK ***
#
# To use this process, ensure that you have the Performance Monitor on.
#  That can be set to start automatically by setting the 
#  PerformanceMonitorOn=T
#  parameter in your tm1s.cfg file.
#
# Note that the date used is only approximate. The }StatsByCubeByClient
#  cube records times in UTC, not local time. To get an exact day's worth
#  of data you'd need to know the current UTC hour (which Iboglix hasn't 
#  provided us with a function to get, thanks Iboglix), then select all of 
#  the 0M elements, the 0H elements, and the 1H elements going back
#  for 24 hours from the current one. There are convoluted ways 
#  of doing that but since the 
#  object of the exercise is to simply determine which cubes are or 
#  aren't being looked at, it's not worth the effort. If you want to do it,
#  knock yourself out.
#
# To recreate this chore from the Advanced Tab code:
# - Create a view in the }StatsByCubeByClient. Select this
#   as your data source. (That view won't be used (it's dynamically 
#   changed below), but it gives you a starting point.) 
#
# - Set the  Contents of all of the variables to "Other".
#      NB: Variable names for a cube view are normally the dimension names.
#        In this case, however (tested in 8.2.12 and 9.0) the variables will be
#        V1 to V5, presumably because the leading braces would freak TI out.
#      You don't need to worry about that because the code already 
#        assumes that the variables will use those names.
#
# - Copy and paste the code into the appropriate tabs.

# CAUTION: If you're using a version which predates the
# DimensionExists function, you'll need to comment out the If and Endif lines
# of the block:
#If (DimensionExists( SC_USEAGE_DIM ) = 0 );
#    DimensionCreate ( SC_USEAGE_DIM );
#EndIf;
# After the first run of the process (which creates the dimension)
# comment out the DimensionCreate line as well.


# ----------------------------------------------------------------------
# Revision History
# Version 1.0 03-SEP-09 (Alan Kirk)
# Version 1.01 03-Sep-09 (Alan Kirk)
# Replaced If block to identify dim names with TabDim function;
# more efficient.


# Modify the following as needed, or have it read from a control cube.

SC_OUTPUT_PATH = 'D:\Temp';


SC_STATS_CUBE = '}StatsByCubeByClient';
SC_USEAGE_DIM = '}CubeUseageMonitor';
SC_USEAGE_ATTR = 'Last_Date';

SC_CUBES_DIM =  '}Cubes';
SC_VIEW = 'CubeUseage';
SC_N_SUBSET = 'N_Level_Cube_Useage';

# ----------------------------------------------------------------------
# Check output path
# ----------------------------------------------------------------------
# You can only check for a folder if there's no
# trailing backslash.

If ( Subst ( SC_OUTPUT_PATH, Long ( SC_OUTPUT_PATH), 1 ) @= '\' );

    SC_OUTPUT_PATH = Subst ( SC_OUTPUT_PATH, 1, Long ( SC_OUTPUT_PATH) -1 );

EndIf;

If ( FileExists ( SC_OUTPUT_PATH ) = 0 );
# Suggest writing a log file to your error log path or control cube here.

        ProcessQuit;
Else;

    SC_OUTPUT_PATH = SC_OUTPUT_PATH | '\';

EndIf;

# ----------------------------------------------------------------------
# Create the subsets and recreate the view.
# ----------------------------------------------------------------------

#Precaution only.
ViewDestroy ( SC_STATS_CUBE, SC_VIEW);
ViewCreate ( SC_STATS_CUBE, SC_VIEW);

ViewSetSkipCalcs( SC_STATS_CUBE, SC_VIEW, 1);
ViewSetSkipZeroes( SC_STATS_CUBE, SC_VIEW, 1);
ViewSetSkipRuleValues( SC_STATS_CUBE, SC_VIEW, 1);

l_Subset = 1;

While ( l_Subset <= 5 );

    s_Dim = TabDim ( SC_STATS_CUBE, l_Subset );

#Precaution only
    SubsetDestroy( s_Dim, SC_N_SUBSET );

# Add the N Level elements to the subset. This block is only needed
# for versions which don't have SubsetCreateByMDX. For those
# that do, delete it and use the alternative "one liner" below. 

    SubsetCreate( s_Dim, SC_N_SUBSET );
    l_DimSiz = DimSiz ( s_Dim );
    l_DimIdx = 1;
    l_SubIdx = 0;

    While ( l_DimIdx <= l_DimSiz );

        s_Elt = DimNm ( s_Dim, l_DimIdx );

        l_ElLev = ElLev ( s_Dim, s_Elt );

        If ( l_ElLev = 0 );

            l_SubIdx = l_SubIdx + 1;

            SubsetElementInsert( s_Dim, SC_N_SUBSET, s_Elt, l_SubIdx );

        EndIf; 

        l_DimIdx = l_DimIdx + 1;

    End; 

#    NB: For versions later than 8.2.12 you can save yourself some effort by using:
#    SubsetCreatebyMDX( SC_N_SUBSET, '{TM1FILTERBYLEVEL( {TM1SUBSETALL( [' | s_Dim | '] )}, 0)}');

    ViewSubsetAssign(  SC_STATS_CUBE, SC_VIEW, s_Dim, SC_N_SUBSET );

    l_Subset = l_Subset + 1;

End;

DatasourceNameForServer= SC_STATS_CUBE;
DatasourceCubeview = SC_VIEW;

# ----------------------------------------------------------------------
# Create the useage tracking dimension
# ----------------------------------------------------------------------

# See introductory notes if using an early version.
If (DimensionExists( SC_USEAGE_DIM ) = 0 );
    DimensionCreate ( SC_USEAGE_DIM );
EndIf;

l_CubesDimLen = DimSiz ( SC_CUBES_DIM );

l_CubesDimIdx = 1;

While (  l_CubesDimIdx <= l_CubesDimLen );

    s_Cube = DimNm ( SC_CUBES_DIM, l_CubesDimIdx );

    If ( Subst ( s_Cube, 1, 1 ) @<> '}' );

        l_UseageIdx = DimIx ( SC_USEAGE_DIM, s_Cube );

        If ( l_UseageIdx = 0 );

            DimensionElementInsert( SC_USEAGE_DIM,'' , s_Cube, 'N');

        EndIf;

    EndIf;

    l_CubesDimIdx = l_CubesDimIdx  + 1;

End;

AttrInsert( SC_USEAGE_DIM, '',SC_USEAGE_ATTR , 'S');

# ----------------------------------------------------------------------
# Determine the output file name
# ----------------------------------------------------------------------

s_OutputFile = 'PerfMon' | TimSt ( Now, '\Y\m\D\h\i\s') | '.txt';

DatasourceASCIIQuoteCharacter='';

s_Date = TimSt ( Now, '\d \M \Y');

Data

Code: Select all

AsciiOutput ( SC_OUTPUT_PATH | s_OutputFile, V1, V2, V3, V4, V5, NumberToString(Value) );

If ( Subst ( V2, 1, 1 ) @<> '}' );

    AttrPutS( s_Date, SC_USEAGE_DIM, V2, SC_USEAGE_ATTR );

EndIf;

Epilog

Code: Select all

ViewDestroy ( SC_STATS_CUBE, SC_VIEW);

l_Subset = 1;

While ( l_Subset <= 5 );

    s_Dim = TabDim ( SC_STATS_CUBE, l_Subset );

    SubsetDestroy( s_Dim, SC_N_SUBSET );

    l_Subset = l_Subset + 1;

End;

If anyone encounters any problems using it please let me know; I think I've made it fully generic aside from the output path but it's possible that I've missed something. (Although it's been tested in two environments, it was knocked together in about 15 minutes so I can't guarantee that I've thought of every possible issue.

Oh, the file from my dump from the cube (in my production environment) was only 124k, so it's not too onerous.

[Gregor Koch]

Nice one, Alan. How long is your train ride?

[Alan Kirk]

Nice one, Alan. How long is your train ride?

Thank you.

Well, it's CityRail, soooo...

(Sydneysiders will get the reference, even if it's lost on the rest of the world. But I'm sure that there are plenty of equivalents out there. )

[jim wood]

Thansk for the code Alan. Saved me a job. For some reason I'm not getting any export though. We are running a Unix server and the file path is a different structure. Do you think this may be the issue? (Have changed the file path check so the slashes are reversed)

[Alan Kirk]

Thansk for the code Alan. Saved me a job. For some reason I'm not getting any export though. We are running a Unix server and the file path is a different structure. Do you think this may be the issue? (Have changed the file path check so the slashes are reversed)

You're welcome.

As to the failure to output... that's a possibility. As I mentioned I was using my notebook on the train on the way in to write it. 13.3" screen, very tiny text in TI. It originally had an output path (or so I thought) of 'C:\ Temp', but nothing was coming out. It was only when I hard coded an AsciiOutput of the expression SC_OUTPUT_PATH | s_OutputFile that I found that it was actually 'C;\Temp' (that is, a semi colon rather than an colon, which I was able to tell once I put the screen under a scanning electron microscope). Oddly it didn't error out.

I'd be inclined to do the same test if I were you; at the end of the Prolog shoot that expression out to a temporary file on a path that you know to exist, and make sure that it really is returning a valid filepath for your system.

[Alan Kirk]

Thansk for the code Alan. Saved me a job. For some reason I'm not getting any export though. We are running a Unix server and the file path is a different structure. Do you think this may be the issue? (Have changed the file path check so the slashes are reversed)

Actually I'm glad you asked that, because it gave me a better idea. I may be lying in bed rather than sitting on a train this time, but I shan't let that stop me.

There's a question mark over whether you even NEED that file output if the only things that you want to know are (a) when was the cube last used and (b) by whom. The answer therefore struck me with a thunk... store it in a cube! And thus I reveal Mk II of my fiendish code (which is in any case shorter and simpler), tested on 9.0 only:

Prolog

Code: Select all

# Process 000_DocumentCubeUSAGEMk2

# ----------------------------------------------------------------------
# Process Purpose
# To identify the approximate date that any cube was 
#  last accessed, and when each user last accessed it.
#
# The process should be put in a chore which is scheduled to 
#  run daily at a set time.
#
# The process does the following:
# - Creates, if necessary, a cube (the "monitor cube") which consists of the the 
#    }PerfClients performance monitor dimension and the }Cubes system 
#    dimension. (In that order, since the }Cubes dimension is the 
#    one which contains S elements.)
#   }PerfClients is used in preference to the standard }clients dimension
#   since the last OVERALL access can be stored in the Total Clients
#  consolidation.
#
# - Creates an N level, zero suppressed view of the data in
#     the }StatsByCubeByClient cube.
#
# - Reads the data from that view. If the data relates to one of the 
#   non-system cubes, the date is updated in the monitor 
#   cube referred to above for both the individual client
#   and the total clients consolidation.
#
# To use this process, ensure that you have the Performance Monitor on.
#  That can be set to start automatically by setting the 
#  PerformanceMonitorOn=T
#  parameter in your tm1s.cfg file.
#
# Note that the date used is only approximate. The }StatsByCubeByClient
#  cube records times in UTC, not local time. To get an exact day's worth
#  of data you'd need to know the current UTC hour (which Iboglix hasn't 
#  provided us with a function to get, thanks Iboglix), then select all of 
#  the 0M elements, the 0H elements, and the 1H elements going back
#  for 24 hours from the current one. There are convoluted ways 
#  of doing that but since the object of the exercise is to 
#  simply determine which cubes are or aren't being looked at
#  (and by whom), it's not worth the effort. If you want to do it,
#  knock yourself out.
#
# To recreate this chore from the Advanced Tab code:
# - Create a view in the }StatsByCubeByClient. Select this
#   as your data source. (That view won't be used (it's dynamically
#   changed below), but it gives you a starting point.)
#
# - Set the  Contents of all of the variables to "Other".
#      NB: Variable names for a cube view are normally the dimension names.
#        In this case, however (tested in 8.2.12 and 9.0) the variables will be
#        V1 to V5, presumably because the leading braces would freak TI out.
#      You don't need to worry about that because the code already
#        assumes that the variables will use those names.
#
# - Copy and paste the code into the appropriate tabs.

# CAUTION: If you're using a version which predates the
# CubeExists function, you'll need to comment out the If and Endif lines
# of the block:
#If (CubeExists( SC_USAGE_CUBE ) = 0 );
# Etc
#EndIf;
# After the first run of the process (which creates the cube)
# comment out the CubeCreate line as well.

# ----------------------------------------------------------------------
# Revision History
# Version 1.0 03-SEP-09 (Alan Kirk)

SC_STATS_CUBE = '}StatsByCubeByClient';
SC_USAGE_CUBE = '}CubeUsageMonitor';

SC_CUBES_DIM =  '}Cubes';
SC_VIEW = 'CubeUsage';
SC_N_SUBSET = 'N_Level_Cube_Usage';

# ----------------------------------------------------------------------
# Create the subsets and recreate the view.
# ----------------------------------------------------------------------

#Precaution only.
ViewDestroy ( SC_STATS_CUBE, SC_VIEW);

ViewCreate ( SC_STATS_CUBE, SC_VIEW);

ViewSetSkipCalcs( SC_STATS_CUBE, SC_VIEW, 1);
ViewSetSkipZeroes( SC_STATS_CUBE, SC_VIEW, 1);
ViewSetSkipRuleValues( SC_STATS_CUBE, SC_VIEW, 1);

l_Subset = 1;

While ( l_Subset <= 5 );

    s_Dim = TabDim ( SC_STATS_CUBE, l_Subset );

#Precaution only
    SubsetDestroy( s_Dim, SC_N_SUBSET );

# Add the N Level elements to the subset. This block is only needed
# for versions which don't have SubsetCreateByMDX. For those
# that do, delete it and use the alternative "one liner" below.

    SubsetCreate( s_Dim, SC_N_SUBSET );
    l_DimSiz = DimSiz ( s_Dim );
    l_DimIdx = 1;
    l_SubIdx = 0;

    While ( l_DimIdx <= l_DimSiz );

        s_Elt = DimNm ( s_Dim, l_DimIdx );

        l_ElLev = ElLev ( s_Dim, s_Elt );

        If ( l_ElLev = 0 );

            l_SubIdx = l_SubIdx + 1;

            SubsetElementInsert( s_Dim, SC_N_SUBSET, s_Elt, l_SubIdx );

        EndIf;

        l_DimIdx = l_DimIdx + 1;

    End;

#    NB: For versions later than 8.2.12 you can save yourself some effort by using:
#    SubsetCreatebyMDX( SC_N_SUBSET, '{TM1FILTERBYLEVEL( {TM1SUBSETALL( [' | s_Dim | '] )}, 0)}');

    ViewSubsetAssign(  SC_STATS_CUBE, SC_VIEW, s_Dim, SC_N_SUBSET );

    l_Subset = l_Subset + 1;

End;

DatasourceNameForServer= SC_STATS_CUBE;
DatasourceCubeview = SC_VIEW;

# ----------------------------------------------------------------------
# Create the Usage tracking cube
# ----------------------------------------------------------------------

# See introductory notes if using an early version.
If (CubeExists( SC_USAGE_CUBE ) = 0 );
    CubeCreate( SC_USAGE_CUBE, '}PerfClients', SC_CUBES_DIM);
EndIf;

# ----------------------------------------------------------------------
# Current Date
# ----------------------------------------------------------------------

s_Date = TimSt ( Now, '\d \M \Y');

Data

Code: Select all

If ( Subst ( V2, 1, 1 ) @<> '}' );

    CellPutS( s_Date, SC_USAGE_CUBE, V1, V2 );

    CellPutS( s_Date, SC_USAGE_CUBE, 'Clients Total', V2 );

EndIf;

Epilog

Code: Select all

ViewDestroy ( SC_STATS_CUBE, SC_VIEW);

l_Subset = 1;

While ( l_Subset <= 5 );

    s_Dim = TabDim ( SC_STATS_CUBE, l_Subset );

    SubsetDestroy( s_Dim, SC_N_SUBSET );

    l_Subset = l_Subset + 1;

End;

[jim wood]

As grateful as I am I have one word for you: SLEEP!!!!!! I hate to think that I will be to blame for you not being able to work tomorrow. Still if could stay awake a little longer while I test it!!!!

[anoops81]

Hi Alan ,

I was able to successfully create the tracking process & Monitor Cube in our tm1 server . I want to know whether this tracking is meant only for users who are logging into tm1 server or it can be used for tracking the people who are logging into tm1web & tm1Executive viewer also ? when I looked into the "Monitor Cube" I saw the details of admin alone , but many other clients were also active via tm1web & Executive viewer.Details regarding their cube usage was not tracked in the monitor Cube Do we need to restart of tm1 server for tracking all the users ? (Performance Monitoring is enabled).


Regards
Anoop

[lotsaram]

}StatsByCubeByClient is "broken" in all versions of 9.4 as far as I am aware.

[anoops81]

Hi Lotsaram ,

Thanks for the update . Is there any way to correct the broken "}StatsByCubeByClient " ? My Manager wants to see how many users are logging into Cognos Systems via TM1 web & Executive Viewer.


Thanks & Regards

Anoop

[Alan Kirk]

}StatsByCubeByClient is "broken" in all versions of 9.4 as far as I am aware.

According to the Fix List, this was rectified in 9.4.1 FP3. I don't have an FP3 release running so I can't confirm that. However the release notes claim that it still doesn't work on a Vista machine because the .dll doesn't register properly; that's unlikely to be a problem for most users who are running on a Windows Server 2003 box.

Whether it's worth upgrading to FP3 just to get that functionality when 9.4 seems to have new and exciting issues with each FP release is another matter.