cancel
Showing results for 
Search instead for 
Did you mean: 

Excel Import - linking to objects on other models

GeorgeMcGeachie
Active Contributor
0 Kudos

In PD 16.5 SP03 the Excel import was improved to allow us to link to objects in other models. Does anyone have a simple example of an import that successfully creates such a link? As a trial, I want to link LDM Entity Attributes to domains in a different model. I've tried qualifying the domain naems with the name of the model, but it doesn't help. e.g. "Model Name.Domain Name"

Accepted Solutions (1)

Accepted Solutions (1)

former_member200945
Contributor
0 Kudos

Hi George,

Could you copy & paste your excel sample in here?

If I understand correctly, after excel import is done, your open property of the attribute in LDM,in General tab, you see the domain name is in Domain field.

Am I right?

GeorgeMcGeachie
Active Contributor
0 Kudos

Here's a sample of the Excel:

ParentNameDomain
Sample Parent EntityPar Party Type Code3SampleDomains.Domain1

The import returns errors -

Error: Could not find object with Name = SampleDomains.Domain1

the domains are in a LDM called SampleDomains

former_member200945
Contributor
0 Kudos

George,

I cannot make it work in 16.5.5.

To access external domain, you need open the external model first. But the existing ExcelImport API seems do not have such function.

Fortunately, it is not difficult to write vbscript to do the work.

The following is an example:

1) Create a LDM(LogicalDataModel_1.ldm) with two domains:

    domain_test1

    domain_test2

2) Open project.ldm ( it's  available in Powerdesigner example folder)

  .

    I want to link Customer.Customer name to domain_test1

    Division.Division name to domain_test2

3) Create excel sheet:

The first row is headline

    

Entity.AttributeExternal domain
Customer.Customer namedomain_test1
Division.Division namedomain_test2
Project.Start date (act)domain_test1

4) Make sure current active model is project.ldm. Execute the following code:

set dict=CreateObject("Scripting.Dictionary")
set objExcel = CreateObject("Excel.Application")

'Read all entries into dictionary
set objWorkbook = objExcel.Workbooks.Open("D:\domainShortcut.xlsx")
intRow = 2 'start from line 2
Do Until objExcel.Cells(intRow,1).Value = ""
   'first column is entity plus attribute name
   key= objExcel.Cells(intRow, 1).Value
   'second column is the domain name in external model
   item=objExcel.Cells(intRow, 2).Value
  
   'add pairs into dictionary. Duplicate key not allow.
   if dict.Exists(key)=false then
     dict.add key, item
   else
     output "duplicate key for " & key
   end if
   intRow=intRow + 1
Loop


'To access the external model, we need open it first
set ldm=OpenModel("D:\LogicalDataModel_1.ldm")

set model=ActiveModel

'Loop through each entity and its all attributes
set Entities=model.entities
for each entity in entities
  set attributes=entity.attributes
  for each att in attributes
    'build key
    key=entity.name & "." & att.name
    'find external domain name
    if dict.exists(key)=true then
        dm=dict.item(key)
        'get external domain object
        set domain=ldm.findchildbyname(dm, PdLDM.cls_Domain)
       
        'if the current attribute is inherited from other attribute,
        'it does not allow to re-assign of domain
        if att.inheritedFrom is nothing then
           ' assign external domain. This create shortcut
           att.domain=domain
        else
           output key & " is inherited from other entity " 
        end if
    
    end if 
  next
next

GeorgeMcGeachie
Active Contributor
0 Kudos

I have got this to work now, and it was far simpler than I expected.

I removed the model name (SampleDomains) from the domain name and it worked OK. The attributes were linked to the domain shortcuts that I had already created. I didn't need to qualify the name at all.

ParentNameDomain
Party TypePar Party Type CodeDomain1
Entity_9Ref data 2Domain5

Even better, when the import still worked after I'd removed all the domain shortcuts - it found the domains 'Domain1' and 'Domain5' in an open model, and used those.

However, if you have more than one visible domain with the imported name it will choose which of them to link to. From my quick tests just now, I can see that it will prioritise domains to link to in this sequence:

  • owned by the model I'm importing into
  • a shortcut in the model I'm importing into
    • if there is more than one shortcut domain with the same name, link to the oldest shortcut
  • a domain in another open model of the same type
    • I don't know how it chooses if there are more than one open model with a domain with that name

I suspect there are some factors here that my simple tests haven't made apparent, but I think that's not an issue for this matter.

When linking data items, attributes or columns to domains, make sure that the only models you have open are the ones containing domains you want to reference. That should be no more than the model you're importing into and a model of standard domains. If you need to have more models than that open, I suggest your domain management policy is too complicated .

Answers (0)