wissew wrote: 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.
wissew wrote: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');
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;
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.
)