Hello all ! 😊
When you support IBM PAW environments, or your PAW environments are hosted there, it might come in handy to count both the PAW subscriptions and the PAW roles, as well as the TM1 group memberships. Not to mention the TM1 clients !
The subscriptions count is useful information that can be captured by an Account Administrator. It can be found under "Subscriptions Management" (PAW > Administration > Users and Groups). Also with Users and Groups, an PAW administrator can list/count the assigned PAW roles.
You typically export the information to 2 CSV text files (see the blue arrows). Yet, 1 combined file is not possible. The Excel workbook that I present here, does this.
I created several Power Query connections to:
- import the subscriptions counts
- import the user counts (by role, by environment)
- join the 2 tables on IDs (full outer join)
To import the CSV text files, just change the path and file name in the coloured cells near the top.
Lastly, refresh all connections and pivot tables and you should be good.
Summarized, you will get the counts of:
- subscriptions: User or Modeler
- roles: Consumer, Analyst, Modeler, Administrator
- the combination of subscriptions and roles
Noteworthy is that not 2*4 = 8 combinations would be possible, following the IBM documentation, but only 6.
A User subscription should not be coupled with a Modeler or Administrator role. The template workbook makes this visible by listing users in this special case - if any.
The Excel workbook has 2 custom lists set up (see: Options > Advanced > Edit custom lists) such that sorting and filling the 2 lists is easy enough – rather than an alphabetical or manual sort in every pivot table.
Besides this, the workbook contains several examples of recent Excel functions, like BYROW, FILTER, the Excel data model for DAX/pivot tables, text values in pivot table cells, etc.
You could add slicers if you want to.
My hopes are that with this template, you can (better) manage the costs associated with the subscriptions. For on premises IBM PA environments, you could use the same workbook and load CSV text files as well, provided that you get the information in.
A last useful extension could be the TM1 user count to be added to the dashboard. A CSV file export could be written in a TI process and integrated in a similar fashion. Like that, all information comes together – including last login date in TM1 as well (you have to set up functionality yourself for that). The TI process could be part of a nightly chore, it should run sub-second.
In the near future we hope to automate this with REST API solutions.
Should you want to try it with the files: https://community.ibm.com/community/user/businessanalytics/viewdocument/ibm-paoc-subscriptions-and-roles-co?CommunityKey=8fde0600-e22b-4178-acf5-bf4eda43146b