TM1 Security and VBA

Post Reply
Carl Peach
Posts: 5
Joined: Tue Oct 04, 2011 2:55 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2003 2007

TM1 Security and VBA

Post by Carl Peach » Thu Oct 13, 2011 3:04 pm

At the moment I am returning a subset based on MDX and using the following code to popluate a spreadsheet.

strService = "harmony dev:"
strCube = "HR Employee List Oracle"
strCostCentre = "Total Group"
strServCube = strService & strCube
strHR_Location = "All Oracle Locations"
strPersonType = "Employee"
strYear = "2011_12"
strMonth = "Sep"
strVersion = "All Versions"

strSubsetName = "FTE_By_Month"
strDimension = strService & "HR_Employee_Oracle"
strSubsetSize = Run("SUBSIZ", strDimension, strSubsetName)
lSubsetSize = val(strSubsetSize)
lRow = 0

For i = 1 To lSubsetSize
strElement = Run("SUBNM", strDimension, strSubsetName, i)
strFTE = Run("DBRW", strServCube, strYear, strMonth, strVersion, strCostCentre, strPersonType, strHR_Location, strElement, "FTE")
dFTE = val(strFTE)

If dFTE > 0 Then

strSurname = Run("DBRA", strDimension, strElement, "Surname")
strFirstName = Run("DBRA", strDimension, strElement, "First Name")
strCostCentreAttr = Run("DBRA", strDimension, strElement, "Cost Centre")

ActiveCell.Offset(lRow, 0 + OffsetNo).Value = strElement
ActiveCell.Offset(lRow, 1 + OffsetNo).Value = strSurname
ActiveCell.Offset(lRow, 2 + OffsetNo).Value = strFirstName
ActiveCell.Offset(lRow, 3 + OffsetNo).Value = strCostCentreAttr
ActiveCell.Offset(lRow, 4).Value = strFTE

lRow = lRow + 1
End If

Next i

The results returned are two cost centres having two employees each. However, I have set up security on the cost centre such that the user does not have access to one cost centre but using this code returns values for that cost centre and hence ignoring security.

Can you please help.

Moved in to the main TM1 forum. It was previously in useful code, hints and tips, Admin Team

Posts: 3052
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TM1, CX
Version: TM1 10.2.2 PA 2.0x
Excel Version: 2010 2013 365
Location: Switzerland

Re: TM1 Security and VBA

Post by lotsaram » Fri Oct 14, 2011 7:57 am

This is an unfortunate artifact of using the TM1 macro functions as SUBNM etc will not respect user security rights. There is no easy way around this. You would need to resort to TM1 API development which adds several layers of complexity compared with the simplicity of running the available TM1 macros.

User avatar
Posts: 574
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: 10.1.1 and 10.2.2
Excel Version: 2013

Re: TM1 Security and VBA

Post by paulsimon » Thu Nov 17, 2011 12:18 am

I haven't tried it but try running the M_CLEAR macro before you access your MDX based subset. That in some cases will force Excel to refresh its local copy of the MDX results.


Paul Simon

Post Reply