by Roger·Comments Off on Security groups in Planning Analytics
Product:
Planning Analytics 2.0.9.13
Microsoft Windows 2019 server
Problem:
If i remove a user from the TM1 Security dialog, will he come back if he have access in Cognos Analytics, when we use CAM security in TM1?
Solution:
Yes, If a user can login to CA11, that is used as the validator for TM1 access ( with the IntegratedSecurityMode=5 parameter in tm1s.cfg file), then the user will pop up inside the Tm1 architect security clients/groups dialog.
The AD group he is part of will not be listed, but if you have before added a AD group into TM1, then he will be marked with a X that he belongs to that AD group.
This give that you can remove a user from security clients/groups dialog in Tm1 architect, and he will next time he logins to TM1 come up again. He will also get back the security he had, as he belongs to the same AD groups as before.
You can not remove the AD groups from security clients/groups dialog, as then the security you have set on that group in TM1 is gone.
If you set security direct on the user, that security is also gone when you remove the user from Tm1.
To remove several users at once from a tm1 application with CAM security, create a text file, with each AD account name on a row (that you want to be removed). Save the file as D:\user_handling\removeuser.txt on the TM1 server.
Login to Tm1 Architect on the Tm1 server direct, and create a new TM1 process.
Select Text as datasource, and select the D:\user_handling\removeuser.txt file.
Click Preview, and you should see a list of the names.
Go to the Variables tab, and change the name of the first (and only) variable to vUser.
Change the contents to be Other.
Go to the Advanced – prolog tab, and enter below code:
sCube = '}ClientGroups';
sDimName = '}Clients';
#-- enter the name of the company domain as it looks in CA11 below --
sDomainName = 'CompanyAD/' ;
#-- file name setup for debug text file --
sFileName= 'debugfile1.txt';
sFilePath = 'd:\temp\';
sDEBUGFILE = sFilePath | sFileName ;
In the metadata tab, enter below code:
#--- add domain name to username name
sFullUser = sDomainName | vUser;
#--- find users alias in client dimenstion and get the tm1 internal name
sPrincipalName = DimensionElementPrincipalName( sDimName, sFullUser );
#--- check that user is not admin
vGroupAdmin = CellGetS( sCube , sPrincipalName , 'ADMIN' );
#--- remove remark to debug write out data to file
# ASCIIOUTPUT ( sDEBUGFILE, sPrincipalName, vGroupAdmin );
If (vGroupAdmin @<> 'ADMIN' );
#--- remove user from security cube in tm1
#--- only if user is not part of ADMIN group
DeleteClient ( sPrincipalName );
endif;
Save the TM1 TI process, and test run it in a sample database first.
by Roger·Comments Off on How publish a dynamic report to TM1WEB
Product:
Planning Analytics 2.0.9.13 ( IBM_PAfE_x64_2.0.82.5.xll )
Microsoft Windows 2019 server
Issue:
How publish a excel sheet with a dynamic report to TM1WEB?
Solution:
You publish the excel sheet to PAW, and it will be availble in both TM1WEB and PAW.
To be able to create Action Button inside excel for Planning Analytics, you need to enable trust for VBA.
Inside Excel – go to file – options – trust center – trust center settings button – macro settings.
Mark “trust access to the VBA project object model”. Click OK.
You need also in many cases have “Optimize for compatibility” marked.
Go to Excel Options – General – User Interface options, mark “Optimize for compatibility“.
Click OK.
To be able to publish a excel dynamic report, you must save the report first, before you publish it. Recommended to save it with the same name, as you would like to have it shown in TM1WEB.
Click on Publish icon in Excel ribbon.
Select the application folder, where you want your websheet and enter a name. Above we have expanded Planning Sample applications.
Mark “make public” if you want other users to see the websheet in TM1WEB. Press publish button.
If you get a blank page in PAW when you insert a Excel sheet to a workbook, that works fine in TM1WEB, you need to ensure that the firewall ports between the TM1 web server and the planning analytics workspace server is open on ports 9510 and 9511. The websheet that show inside PAW; is rendered from TM1WEB.
Access to TM1WEB for both end user and the PAW server is essentiell to make it (PAfE) work.
Copy the file IBM_PAfE_x64_2.0.82.5.xll to a folder on your laptop – like c:\program files.
Right click on the file IBM_PAfE_x64_2.0.82.5.xll and select create “short-cut”
Click Yes on above question, and place the shortcut on your desktop.
You should now get a icon like above – click on this icon to start PAFE (new pax) on your laptop.
If you inside excel get a sheet with garbage characters – then you have downloaded wrong bit version, like if you use 64 bit version in 32 bit excel version.
Inside Excel click on Planning Analytics and options.
Click on IBM and then ADD button.
Enter the full URL to your PAW server. Check your company server-name for your PAW server. This is what you need to get the pafe to work.
Click TEST connection and then save.
You may need to inside Excel, allow the xll file to be run by “Enable all code published by this publisher” inside file – options – trust center.
Before you can publish a excel workbook with buttons, to TM1WEB, you need to save it as a .xlsm file. then you can click publish it to TM1 application folder.
After you publish it, you go back into publish dialog, and right click your tm1websheet and select “public” to make it accessible for all users.
by Roger·Comments Off on Error: Prolog procedure line (0): Unable to open data source ” “.
Product:
Planning Analytics 2.0.9.13
TM1_version=TM1-AW64-ML-RTM-11.0.913.10-0
Microsoft Windows 2019 server
Issue:
Can not read data from ODBC connection in TI process.
Solution:
Check that the ODBC connection still exist, and that the TM1 service have access to the ODBC connection.
How setup a ODBC connection for TM1:
On the server go to control panel – administrative tools – ODBC data source (32 bit).
Click on system DSN tab.
Click on Add button.
Select SQL server native client 11.0 and press Finish button.
Fill in a name and a server-name, that you can connect to the database on.
Enter a SQL native login, that you can use to test the connection with.
Click next – next – finish.
Click Test data source button, you should get this if you did correct:
Microsoft SQL Server Native Client Version 11.00.5058
Running connectivity tests…
Attempting connection Connection established Verifying option settings Disconnecting from server
TESTS COMPLETED SUCCESSFULLY!
Now go into you TM1 application, and create a new TI process.
Select ODBC as data source, and pick your ODBC from the list.
Enter a working SQL native username and password.
Enter a SELECT SQL statement, that you have tested in SSMS, that it works to bring some data back.
Press Preview to check that the SQL connection work – you should see your data.
Go to variables tab.
The variables name suggestion is created from first data line, you can change them to vNames that you think is more describing. Do not use spaces in variable names.
For the lines of data that you want to use, change the Contents column to ‘Other’ as shown above. Click on Advanced tab.
Go to prolog tab, and enter some code to test this process:
# variables setup - set them all here as empty at least
sAccount = '';
# file name setup for debug text file
sFileName= 'debugfile2.txt';
sFilePath = 'c:\temp\';
sDEBUGFILE = sFilePath | sFileName ;
# get the date and time to set a stamp in the log file
# https://edu.cubewise.com/tm1-function-for-rules-timst/
sNowTime = NOW();
sDATETIME = TIMST( sNowTime , '\Y-\m-\d \h:\i' );
Then go to the Data tab, to enter code to write out the data to a file for debugging.
# set the variable from the data source to a variable in the code and print it out
sAccount = AccountDescription;
ASCIIOUTPUT ( sDEBUGFILE, sAccount, sDATETIME );
Save the process with a name that describe the function.
Run the process.
Then to check that it works, go to c:\temp and open the text file.
The value in the database for column [AccountDescription] is first, and then a date-time stamp from the code.
As we use the TM1 data source tab, we do not need to use the ODBCopen command to get the data in the data tab.
Planning analytics should work with both 32 bit and 64 bit ODBC connections on the Windows server, as below parameter is on by default.
EnableODBCProxy makes 32-bit data source names to be available to TurboIntegrator processes on 64-bit machines.
EnableODBCProxy is useful, if a 64-bit driver is not available. Each proxied connection creates a 32-bit tm1odbcproxy.exe process during the connection. EnableODBCProxy is true by default, but you can disable the feature by including EnableODBCProxy=false in the tm1s.cfg.
This give that your TM1 64 bit server can use 32 bit ODBC connections. But for best result you should use a 64 Bit connection in Windows server.
by Roger·Comments Off on How prevent user to login by mistake to TM1
Product:
Planning Analytics 2.0.9.3
Microsoft Windows 2019 server
Issue:
In a TM1 solution where CAM security with Cognos Analytics is used, user can login to the TM1WEB, as long they are validated by CA11 – but then not see anything in TM1 as they have no access in the TM1 product.
But the user leave a mark (name) in the }ClientSecurity cube. That may be registered in the ILMT software scan of TM1 license usage.
Can we prevent them from login?
Solution:
A Cognos Analytics user that run reports on TM1 data, should in most cases be good with the license of CA11 they have, and not need a license for TM1 access. TM1 access are in many license forms included in the Cognos BI license.
If you active the tm1s.cfg parameter CreateNewCAMClients=F on a TM1 Instance, then no one can login if they not are already part of the security setup.
This mean you have to manually add new users to TM1 Security in TM1 architect.
Please note: to be able to run a Cognos report with TM1 data, the user that run the report must have read rights in the TM1 cube, and therefor in most cases be registered inside TM1 security. Only when the access to TM1 is done with a special TM1 account in Cognos Data Source connection, this may not be needed.
When CreateNewCAMClients=F and a logon is attempted with a valid set of CAM credentials, but a corresponding Planning Analytics client does not exist in the security cube, the Planning Analytics client is not created and the logon is rejected.
User who have left the company or do not need access to TM1, need to be manually removed from the }Clients dimension.
Users are not automatically removed from a TM1 database – even if the TM1 database is configured for Cognos authentication. IBM Planning Analytics customers must manually remove users that are no longer licensed to use Planning Analytics and no longer should have access to the TM1 database.
Failing to remove inactive users from the TM1 database may result in over-counting the number of Authorized Users, which can lead to auditing issues.
You can also inside Cognos Analytics Configuration set that users must be part of a Cognos group to be allowed to login:
Open Cognos Configuration and under the ‘Security’ tab, click on ‘Authentication’.
Against ‘Restrict access to members of the built-in namespace?’ set the value to ‘True’. This excludes all others from third party authentication sources who are not explicitly part of a Cognos namespace group or role as defined in Cognos Analytics 11.
Save the configuration and restart the Cognos service.
Then the user can not login to CA11, and therefor also not login to Tm1 that are using CAM security (IntegratedSecurityMode=5).
IBM® TM1® Server generates IBM Software License Metric Tag (SLMT) files. Versions of IBM License Metric Tool that support SLMT files can generate License Consumption Reports that provide information about license usage for your TM1 Server.
The initial generation of SLMT files is determined by the LicenseMetricTime Tm1s.cfg parameter. When the generation of SMLT files is enabled with LicenseMetricTime, a new SLMT file is created every 24 hours.
The AUTHORIZED_USER metric
The AUTHORIZED_USER metric can have the following subtypes:
IBM Cognos Enterprise Planning TM1 Modeler – Any user that is a member of the Admin, DataAdmin, or SecurityAdmin user groups on the TM1 Server.
IBM Cognos Enterprise Planning TM1 Contributor – Any user that is not a Modeler, but is assigned to a group with write access to at least one cube on a TM1 Server. A group is defined to have write access for a cube if the group is assigned one of the following security permissions for the cube: Write, Lock, Reserve, or Admin.
IBM Cognos Enterprise Planning TM1 Explorer – Any user that is not a Modeler or a Contributor.
For each of these subtypes, the AUTHORIZED_USER metric records the number of users who have logged on to the TM1 Server during the period identified in the SLMT file.
Location of Software License Metric Tag files
On all operating systems, the SLMT files are created in the slmtag directory at the same level as the bin64 directory in the TM1 install location. For example, C:\Program Files\IBM\cognos\tm1_64\slmtag. All SLMT files use the .slmtag file extension.
I think the modern ILMT tool only reads the }ClientSecurity.cub file to find out number of users in the TM1 installation.
I want to use TM1 Data in a BI report but use TM1 security to pass through from BI. Do I need a TM1 license for the Cognos BI user? TM1 is just another data source to BI. If you want to use TM1 as a base for Cognos BI, you need to have the appropriate Cognos BI license. However, real issue is that to assign security on the TM1 data which is being passed to BI, you need to put the user in a TM1 group. For example, if you need to have cell level security, you need to set them up in TM1. If the user will not access TM1, and are only set up in TM1 to assign security, they will NOT need a TM1 user license. This is specified in the TM1 License which states that BI Analytics Users or Explorers don’t need additional authorized user entitlements to TM1. They do however, need the appropriate IBM Cognos Analytic Server license.
by Roger·Comments Off on How skip elements that are part of a subset
Product:
TM1
Microsoft Windows 2019 server
Issue:
How do i skip elements in a list/view in the data tab from a subset?
Solution:
If you have a subset named “DonaldsCoffe” that contain the stores-name that you want not to be transferred used in your data tab, in the TM1 TI process.
Use below statement to check if the element exist in the subset, and if it does skip that data.
IF ( DIMIX ('DonaldsCoffe', vElementName) <> 0);
Itemskip;
ENDIF;
In case you use =0 in above IF statement, that is that you will skip all elements that are not in the list.
DIMIX returns the position of the element in the subset list. 0 (zero) means the element is not in the list.
The comparison operators compare values in the formula portion of a rule calculation statement.
Operator
Meaning
>
Greater than
<
Less than
>=
Greater than or equal to
<=
Less than or equal to
=
Equal to
<>
Not equal to
To compare two string values, insert the @ symbol before the comparison operator, as in the following example:
by Roger·Comments Off on TM1ADMSRV is not starting
Product:
Planning Analytics 2.0.9.11
Microsoft Windows 2019 server
Issue:
The TM1 Admin service is not starting. This program is not found running in task manager d:\program files\ibm\cognos\tm1_64\bin64\tm1admsd.exe.
There are no error in the log file at d:\program files\ibm\cognos\tm1_64\bin64\tm1admsrv.log.
You get this error when you start the service:
“The IBM Cognos TM1 Admin Server x64 service on Local Computer started and then stopped. Some services stop automatically if they are not in use by other services or programs.”
Solution:
Check if a other application is using the port needed by the TM1 admin service (tm1admsdx64).
TM1 admin service uses the ports:
5495
Tm1 architect contact with TM1 Admin service
5498
Tm1 architect contact with TM1 Admin service (ssl)
5895
TM1 Admin Server (REST API)
5898
TM1 Admin Server (REST API)
Checking which application is using a port:
Open the command prompt – start >> run >> cmd or start >> All Programs >> Accessories >> Command Prompt.
Type netstat -aon | findstr '[port_number]'. Replace the [port_number] with the actual port number that you want to check and hit enter.
If the port is being used by any application, then that application’s detail will be shown. The number, which is shown at the last column of the list, is the PID (process ID) of that application. Make note of this.
Type tasklist | findstr '[PID]'. Replace the [PID] with the number from the above step and hit enter.
You’ll be shown the application name that is using your port number.
You can use DOS command NETSTAT -ANO to get a list of used ports as shown above.
Then you can check what service is using that process id (pid) with above command.
If you can, stop the other application/process and then try to start TM1 admin service again.
But in most cases, you need to reboot the full windows server, to clear any runaway processes.
If the tm1admsrv.exe is blocking the start of TM1 admin service, it is because the TM1 has itself started the TM1ADMSRV as a background process – to keep the Tm1 system up, after a crash of the TM1 admin service.
3 types of rules which every consultant should know:
Allocation/Phase/Spread Rule – e.g. Allocate/phase/spread our budgeted sales across States based on the Actual Sales ratio.
Rolling Value Rule – e.g. Opening (Measure) is equal to the Closing of the prior period. Often used in the Balance Sheet or Depreciation rules.
Averaging Rule (C Level) – e.g. Averaging Percentages or Rates up all hierarchies within the cube.
As with TM1, and Platform Software in general, there are a million ways to do anything, so don’t worry if we don’t follow the methodology you are familiar with. That being said, these 3 TM1 rules are a great guide for any developer!
Allocation/Phase/Spread Rule – How to Spread a value Across Periods
This is a common requirement often seen in budgeting rules to allocate/phase/spread an annual budget across months based on Calendar Days, Working Days or Last Year’s Actual Values for the given account.
Here is a sample rule which will phase an annual budget across months based on the number of working days in each month.
There is a number of different ways to write this. For example, I could exclude the {} Months from my rule filter (scope) and filter using an ELISANC within an IF Statement to check that the month element of the current cell being calculated is a descendant of the ‘All Months’ Element but that would clutter the rule tracer when/if used later on the ‘Annual’ Element.
Feeder for Allocation/Spread Rule
Don’t you need a complex feeder with a rule like this?No. We only have to calculate a month if there is a value in the ‘Annual’ month (which is a posting element for annualised data) within the same year. Which means our feeder can simply be:
[‘Annual’,’Budget’,’$’] => [‘All Months’];
If you were writing this longhand, it would look like this:
Calculating a Balance Sheet, Net Book Value or Depreciation? This rule logic is bound to come up. This methodology is going to be slightly different depending on how you have your Time Dimension(s) set up within your cube.
Firstly there are even more possible solutions here, but we are aiming for a sustainable example. This means we will be avoiding DIMNM(DIMIX()-1) in favour of using attributes to help move around periods.
We’ll take a customer subscription calculation as our example. We’ll assume the cube for this rule has a separate Year and Month dimension.
Measures Dimension for Rolling Rule
Given a measures dimension which looks like this:
Closing Subscriptions
Opening Subscriptions
New Subscribers
Subscriber Churn (Displayed as a positive sign, aggregated with a -1 Weighting)
Attribute used on Month Dimension
We then have a clever little attribute table on the Month Dimension:
Sample Attribute Table to assist Rolling Value Rules.
Rolling Rule
A TM1 rule can then be written which looks like this:
This is what I would class as a bare-bones rule for Rolling a Value. This should go back as far as the Year dimension’s elements will go and has not potential to create a circular reference like DIMNM(DIMIX()-1) methodology.
If you want to post an opening amount into the first month and first year within your TM1 cube you can use an additional check to see if the generated Year exists using the DIMIX function, if it doesn’t a STET will make the cell editable.
Then, the feeders for this involve the same amount of coding, but the theory may be daunting for people still learning. This is because where the rule went back across time periods to get the value, the feeder has to go forwards across time periods to push the value into the rule calculation cell.
This could be written simpler if we didn’t piggyback the same “Prior Year” Attributes and instead added new “Next Year” Attributes. The above example feeder has a minimalistic approach to attributes but is paying for it in rule complexity.
I am also using a filter of each month because I have other N level elements in my month dimension which I don’t want this rule applied to.
Averaging Rule (C Level)
C Level (Consolidation Level) TM1 rules which do averaging are very similar to normal rules but the reason we have them listed is that people don’t realize until they have to write one that a (non-zero value) countermeasure is needed and you need to use a separate measure to perform the calculation in most cases.
I’m using price and not units for my product counter as I want to average all prices regardless of if the product is sold in a specific period. However, if I wanted an average price pro-rata units sold I would back solve my revenue equation like so.
