Forum Discussion

ssmith-nova's avatar
ssmith-nova
New Contributor III
6 months ago

How do I look up the property name for a Member PropertyId or a Relationship PropertyId?

I'm trying to write a BR to export metadata to csv so that I can export them via a schedule.

1) Is there an easier way to repeatedly export dimensions to csv?

2) How do I look up the property name for a Member  PropertyId or a Relationship PropertyId? I am querying the members and member_properties table and I'd like to get the property name?

Thank you,

Scott

Data Integration
  • MarcusH's avatar
    MarcusH
    6 months ago

    1) I agree with TheJonG 

    2) I think the easiest way to do this is is to export the metadata to XML and pick out what you want. You will need to format it to csv but it should have all the member and relationship property names. This code extracts just the metadata to a string variable. I have cobbled this together from some other scripts but I think it should work.

    Imports System.IO.Compression
Imports System.Xml

Dim xmlOptions As New XmlExtractOptions
xmlOptions.ExtractAllItems = True
'Extract dimensions
Dim extractDict As New Dictionary(Of XmlExtractItemPk, Boolean)
extractDict.Add(New XmlExtractItemPk(XmlExtractItemType.Dimensions), True)

'Execute the Metadata Extract
Using dbConnFW As DbConnInfo = BRApi.Database.CreateFrameworkDbConnInfo(si)
    Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
        Dim zipBytes As Byte() = ApplicationZipFileHelper.Extract(dbConnFW, dbConnApp, Nothing, xmlOptions)
        ' Decompress the zipped bytes into memory
        Using zipStream As New MemoryStream(zipBytes)
            Using archive As New ZipArchive(zipStream, ZipArchiveMode.Read)
                ' Iterate through each entry in the zip archive
                For Each entry As ZipArchiveEntry In archive.Entries
                    If entry.fullname.toupper = "METADATA.XML" Then
                        ' Read the contents of the entry into a string
                        Using entryStream As Stream = entry.Open()
                            Using reader As New StreamReader(entryStream)
                                Dim contents As String = reader.ReadToEnd()
                                ' Process XML contents here using XMLDocument or similar
                            End Using
                        End Using
                        Exit For
                    End If
                Next
            End Using
        End Using
    End Using
End Using

     

  • TheJonG's avatar
    TheJonG
    Contributor III
    6 months ago

    1. You should be able to use Task Scheduler to kick off the Business Rule.

    2. There aren't any BRApi calls for this but should be in documentation somewhere. I will let you know if I find anything.

  • MarcusH's avatar
    MarcusH
    Contributor III
    6 months ago

    1) I agree with TheJonG 

    2) I think the easiest way to do this is is to export the metadata to XML and pick out what you want. You will need to format it to csv but it should have all the member and relationship property names. This code extracts just the metadata to a string variable. I have cobbled this together from some other scripts but I think it should work.

    Imports System.IO.Compression
Imports System.Xml

Dim xmlOptions As New XmlExtractOptions
xmlOptions.ExtractAllItems = True
'Extract dimensions
Dim extractDict As New Dictionary(Of XmlExtractItemPk, Boolean)
extractDict.Add(New XmlExtractItemPk(XmlExtractItemType.Dimensions), True)

'Execute the Metadata Extract
Using dbConnFW As DbConnInfo = BRApi.Database.CreateFrameworkDbConnInfo(si)
    Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
        Dim zipBytes As Byte() = ApplicationZipFileHelper.Extract(dbConnFW, dbConnApp, Nothing, xmlOptions)
        ' Decompress the zipped bytes into memory
        Using zipStream As New MemoryStream(zipBytes)
            Using archive As New ZipArchive(zipStream, ZipArchiveMode.Read)
                ' Iterate through each entry in the zip archive
                For Each entry As ZipArchiveEntry In archive.Entries
                    If entry.fullname.toupper = "METADATA.XML" Then
                        ' Read the contents of the entry into a string
                        Using entryStream As Stream = entry.Open()
                            Using reader As New StreamReader(entryStream)
                                Dim contents As String = reader.ReadToEnd()
                                ' Process XML contents here using XMLDocument or similar
                            End Using
                        End Using
                        Exit For
                    End If
                Next
            End Using
        End Using
    End Using
End Using