Transcript Document
Automating Security in IBM Cognos 8 Transformer Evan Ross Principal IBM Services Partner Expertise in the BI platform for more than 12 years Cognos BI Dashboarding Reporting Analysis Scorecarding Data Manager Some of our Cognos Clients Cognos security Great Active Directory integration for Framework Manager Functionality added in Transformer 8.3 for Custom Views The problem: Hospital ERP system Complex security by department Security table available in SQL Framework Manager can easily connect to the SQL table Hard to maintain in Transformer Security Concerns Timeliness Standard hierarchies are easy… Corporate Canada East USA West South …Complex Hierarchies are not! Transformer views Exclude Not in cube Cloak In totals, not visible Suppress Hide level Summarize Hide lower levels Apex Hide upper levels The manual method What does the user see? Two options: Supress all categories at the lowest level and then unsuppress applicable categories Apex on applicable categories What is the difference? Division Division Business Business Unit Unit Location Location [Text] The solution Cognos 8.3 and higher allows for OLE automation of Transformer Model Read SQL database and create User Views for each class Tools needed: Transformer Visual Studio – free Express version is fine Does NOT need Cognos SDK! The next problem – Active Directory names! Security name RossE Cognos tables uses login expects full name Ross, Evan The answer – link directly to Active Directory Connecting to Active Directory SQL AD Server supports linking directly to Once connected, it’s a simple query… Insert Into L_activedirectory Select Cn, Samaccountname From Openquery(adsi, 'Select Cn, Samaccountname From ''Ldap:// Dc1'' Where Objectclass = ''User'' And Objectcategory = ''Person'‘ Order By Givenname') End Process model to update security Clear Add • Deletes all existing user views • Reads database • Creates new views • Build cubes from batch file Build • Copy and deploy cubes VB.NET code – what does it do? For intX = 1 To dimension.DrillDowns.Item(1). Categories.Count Dim category As Category = dimension.DrillDowns.Item(1).Categories(intX) If category.Name = deptName Then view.SetViewStatus(category,xtrViewStatus.t rViewStatusApexAncestor) End If Next intX The end result No manual maintenance of Transformer security Security preserved in all Cognos tools – relational, dimensionally modeled relational (DRM), and cubes No more headaches for BI team! Questions? Evan Ross [email protected] 416-987-5793