Uniqueness Violation

[agorsky]

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

[EasyRider]

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.

[agorsky]

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

[EasyRider]

I can't think of any. That's my standard approach to model maintenance. It avoids models that grow uncontrolably.

[dwood09]

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

[dwood09]

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

[agorsky]

All I have to say is "WOW!"  Thank you for posting that code.  I am going to give it a try!

Aaron

[Darek]

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.