Hi All--
I am running into a problem that I'm hoping has a simple solution.Ã, As my nickname indicates I am new to Cognos so if I leave any important information out please let me know.
I have a customer dimension defined with the Customer ID set as the unique value.Ã, Occasionally a customer name is changed.Ã, I am currently having the entire cube rebuilt every night.Ã, The problem is that the cube will throw an uniqueness violation error.Ã, I initially thought that Transformer purges the categories when a cube is rebuilt but this is obviously not true.
I used the following command line to build the cube once my datamart (SQL) has finished.Ã, Is there another flag I could set that would cause the categories to be purged?Ã, My only solution is to manually delete the old customer category.Ã, I can't use "Clean House" because it removes my relative time calculations that I created.
Any advice/info is appreciated!
Thanks!
Aaron
Command Line Used:
trnsfrmr.exe -n2 -s -i -nologo -r4 -dLogFileName=SalesCubeBuildLog.txt -dLogFileDirectory=C:\BuildCube E:\Cognos\Models\AESales.pyi > C:\BuildCube\Salesresults1.txt
Assuming you want the historical records to follow the latest and your records are sorted by time, in the properties of the unique level next to the unique check box, set the 'Move' check box so with each change the historical records will move to the new or latest reference.
or..
If you remove the categories at this level, save the model and don't save the model after building your cube. This would mean removing the -s in your command line.
The second would be my choice since I don't like my model growing with my data.
Thank you so much for the advice. It seems that the second option is the best. What are the potential negatives for doing it that way? Are there any?
Aaron
I can't think of any. That's my standard approach to model maintenance. It avoids models that grow uncontrolably.
I also like to keep my models as data free as possible ... I have developed a process that helps keep the models clean. it involves the following:
1. Within the model I add a flag to the description of every category that I want to keep, such as "category retained". Categories to keep can include orphanage parents, special categories, manual levels, base level static categories that need to be retained, categories with hardcoded ordering, categories that have security associated with them etc etc. This may sound painful to start with, but I have found that once it's done, it doesn't require very much on going work!!!
2. I then save the model.
3. I have built a macro that removes all categories that do not have the required flag, all those categories where the desciption does not contain "category retained" within it. The process then saves the model as a "*.mdl" file, opens it back up again and sets the signon info, as required and saves it back to ".pyi"
Issues with the process:
1. v7.1 MR3 automation does not expose the "Prompt for Password" property of the signon object, which means that the resultant pyi needs to be opened manually and have the signon "Prompt for Password" switched off!!!
I'll prepare a post with the code etc in a bit!
hope this helps,
cheers,
DW
Here's the latest code .... prior version had a few issues ...
NB: This works for 7.3 models with VB.NET ... and the previous problem with the property not exposed is no longer a problem ... ie the model will be ready to run, once complete.
cheers,
DW
Module Module1
Dim iCatsRemoved As Int32
Dim iCatsRetained As Int32
Sub StripOutCats()
'***** SET THESE VARIABLES ****
Const sInPath As String = "u:\bi\forecast\ModelsToBuild\zbackup\forecast P and L\" ' must end in \
Const sInFile As String = "01-forecast p and l.pyi"
Const sAMlogon As String = "" 'logon for access manager
Const sAMpwd As String = "" 'password for access manager
Const sUserClass As String = "Root User Class" 'user class for Access Manager
Const sOutputPath As String = "c:\" ' path for temporary output file
Const sPYIlogon As String = "dbaselogon" ' logon required to be embeded within Transformer model
Const sPYIpassword As String = "dbasepwd" ' password required to be embeded within Transformer model
Const sFlag As String = "retain=1" 'string to find within the description for categories to retain
'***
Dim iDim As Integer
Dim iDdown As Integer
Dim iCat As Integer
Dim objTranApp As Object 'transformer application object
Dim objModel As Object ' model object
Dim objDimension As Object ' dim object
Dim objDrillDown As Object ' drilldown object
Dim objCat As Object ' Category object
Dim iDataSourceId As Integer
Dim sNewFilePath As String
Dim iSignons As Integer
Dim bSave As Boolean
Dim obFS As Object 'FileSystem Object
Dim bErr As Boolean
Dim bCont As Boolean
Dim sErr As String
Dim sName As String
Dim iModelId As Integer
Dim bComplete As Boolean
'open transformer
bComplete = False
sName = Left(sInFile, Len(sInFile) - 4)
objTranApp = CreateObject("CognosTransformer.Application")
objModel = objTranApp.OpenModel(sInPath & sInFile) ', sAMlogon, sAMpwd, sUserClass)
'scroll thru each dim
For iDim = 1 To objModel.Dimensions.Count
objDimension = objModel.Dimensions(iDim)
For iDdown = 1 To objDimension.drilldowns.Count
objDrillDown = objDimension.drilldowns(iDdown)
bComplete = False
While Not bComplete
If objDrillDown.categories.Count > 0 Then
For iCat = objDrillDown.categories.Count To 1 Step -1
objCat = objDrillDown.categories(iCat)
If Not TraverseDim(objCat, "", True, sFlag) Then 'not to be retained
objCat.Delete()
iCatsRemoved = iCatsRemoved + 1
bComplete = False
Else
bComplete = True
iCatsRetained = iCatsRetained + 1
End If
Next iCat
Else
bComplete = True
End If
End While
Next iDdown
Next iDim
'scroll thru each level and cat
If iCatsRemoved > 0 Then
objModel.Update()
objModel.Save()
End If
bErr = False
Try
sNewFilePath = sInPath & Left(sInFile, Len(sInFile) - 3) & "mdl"
objModel.SaveAs(sNewFilePath)
Catch objException As Exception
bErr = True
MsgBox("Unable to save the file as mdl.Ã, Msg: " & objException.ToString())
End Try
If Not bErr Then
bSave = False
'open the new mdl
objModel = objTranApp.OpenModel(sNewFilePath)
For iSignons = 1 To objModel.signons.Count()
If objModel.signons(iSignons).userid = sPYIlogon Then
objModel.signons(iSignons).Password = sPYIpassword
objModel.signons(iSignons).PromptForPassword = False
objModel.signons(iSignons).Update()
bSave = True
Else
MsgBox("You will need to set the password for signon " & objModel.signons(iSignons).Name)
End If
Next iSignons
If bSave Then
'save as pyi
objModel.Update()
objModel.SaveAs(sOutputPath & Left(sInFile, Len(sInFile) - 3) & "pyi")
End If
objModel.Close()
objModel = Nothing
'delete mdl
obFS = CreateObject("Scripting.FileSystemObject")
obFS.DeleteFile(sNewFilePath)
'move pyi
bErr = False
Try
'backup existing pyi
obFS.MoveFile(sInPath & sInFile, sInPath & Format(Now(), "yyyyMMdd HHmm") & " - " & sInFile)
'copy in new file
obFS.MoveFile(sOutputPath & Left(sInFile, Len(sInFile) - 3) & "pyi", sInPath & sInFile)
Catch objException As Exception
bErr = True
MsgBox("Could not move the file from " & sOutputPath & ".Ã, Please move yourself.")
End Try
Else
objModel.Close()
objModel = Nothing
End If 'end test for err on save as mdl
MsgBox("Categories Removed: " & iCatsRemoved & Chr(13) & "Categories Retained: " & iCatsRetained)
End Sub
Function TraverseDim(ByVal objInCat As Object, ByVal sOutSheet As String, ByVal bInTraverse As Boolean, ByVal sInFlag As String)
Dim bCont As Boolean
Dim sDesc As String
Dim iCat As Int32
Dim objCat As Object
Dim iCurrCount As Int32
Dim bResult As Boolean
bCont = True
bResult = False
'check this cat for removal
sDesc = Trim(objInCat.Description)
If InStr(1, sDesc, sInFlag, CompareMethod.Text) > 0 Then
'check cat at this level
bResult = True
End If
iCurrCount = objInCat.childcategories.Count
If iCurrCount > 0 Then
For iCat = iCurrCount To 1 Step -1
Try
objCat = objInCat.childcategories(iCat)
Catch objException As Exception
Exit For
End Try
'traverse to the next level
If TraverseDim(objCat, "", True, sInFlag) Then 'to be retained
bResult = True
iCatsRetained = iCatsRetained + 1
Else
objCat.Delete()
iCatsRemoved = iCatsRemoved + 1
End If
Next iCat
End If
TraverseDim = bResult
End Function
End Module
All I have to say is "WOW!" Thank you for posting that code. I am going to give it a try!
Aaron
Nice work, Woody.
If you don't want to store uid/pwd in the code, just use OSSignOns and Acc Mgr driven connections and DB signons.