Fullscreen Image

Mappings

DataMart mappings provide a connection between the incoming data and the SQL Server Database your organization has created to store your Lucernex data.

ClosedData model

The data model is tied to Lucernex version number and not necessarily the DataMart version. As schema / data model changes are made in Lucernex, DataMart automatically picks them up, so while your DataMart may be v.4.0.xx, the data model might be from Lucernex 24.8. Schemas are specific to your organization. Contact your Accruent representative if you need documentation on your schema.

Primary entities

Program is the highest level grouping of data. This is a Program at the data level, in the Lucernex it is also known as a Program with a program type of Portfolio.

There has to be at least one portfolio in the firm. There might be multiple portfolios depending on your organization. These entities are assigned a specific Portfolio using the PortfolioID:

  • Location: Second-highest level of group of data. Each of these entities points to a Location from the LocationID.

    • Parcel: Facilities and / or Contracts can link to a Parcel using the ParcelID.

    • Facility: Facilities can have one or more contracts linked to the Facility using the FacilityID.

    • Contract: Contracts are linked to a Location from LocationID. Depending on the type of contract, the contract may be linked to Facility via FacilityID and / or a Parcel via the ParcelID.

View relationships

You can view the table relationships in the Lucernex Production environment.

  1. Click Admin in the toolbar in the upper-right corner of the window.

    The System Administrator Dashboard page opens.

  2. In Data / PS Tools, click View Object Model.

    The View Object Model page opens.

  3. In Show Table Details For, select the table you want.

    Selecting All Tables might slow the page down due to the large amount of data to collect.

Tables

The primary key is always listed as the first field in any table, and foreign keys use the table it refers to as its field name. So, for example, ContractID refers back to the Contract table, and ProgramID refers back to the Program table.

Create a mapping

Create mappings for each type of record you want to pull and run them when needed, or create one mapping for all record types.

Do not modify a single mapping to change the types of records it includes. If you do, the next delta run does not pick up changes to records that occurred in the time frame when those record types weren’t included in the mapping.

Example scenario:

  • First run: On Monday, the mapping has Projects and Sites selected,

  • Second run: Wednesday, the mapping has only Sites selected,

  • Third run: On Friday, the mapping has Projects and Sites selected again.

In this scenario, the third run will pick the time stamp from the run on Wednesday. This means if there are any changes for Projects between Monday and Wednesday, they will not be reflected in the database. Only changes between Wednesday and Friday are reflected on Projects table.

A mapping specifies the source and target of the DataMart information flow.

  1. Click Create Mapping in the toolbar below the Mapping Schedules section.

    Step 1 of the New Mapping wizard opens.

  2. Complete these fields:

    • URL: DataMart enabled Lucernex URL you want to download data from. This URL must be SSL, for example: https://dm-lxretail.lucernex.com. The URL must contain https, not http.

    • Firm: Enter up to 20 characters for your firm name.

    • Username: Enter up to 20 characters for the Username on this environment.

    • Password: Password for your username.

  3. Click Continue.

    Step 2 of the New Mapping wizard opens.

  4. Complete these fields:

    • Host: Server name for the database.

    • Port: Port where your SQL server lives.

    • Name: Enter up to 20 characters for your database target name.

      Your Database Target name should be all one word, or separated by underscores or hyphens.

    • Instance: Enter up to 20 characters for your database instance.

    • Username: Enter up to 20 characters for your database username.

    • Password: Your database password.

      If your firm has password security settings that require passwords to expire periodically, you will need to change your password in the mapping to accommodate the change.

  5. Click Continue.

    Step 3 of the New Mapping wizard opens.

  6. Click the schedule you want.

    • Hourly: Enter the number of hours between each run in Every X Hours.

    • Daily: Enter:

      • Every X Days: Number of days between each run.

      • At: Time you want the run to occur.

    • Weekly: Enter:

      • Days of the Week: Days of the week the run is to occur.

      • At: Time the run is to occur.

    • Times / Day: Enter the times during each day that you want to schedule the run for in the 24 hour clock format HH:mm. After each time, hit Enter. Available in DataMart Version 7.0.176 and later.

  7. Click Continue.

    Step 4 of the New Mapping wizard opens.

  8. Complete these fields:

    • Name: Enter up to 20 characters for the mapping name.

    • Record Types: Select the record types you want this mapping to pull from.

      By default, the mapping pulls all record types.

    • PGP Encrypt: Use PGP encryption for this mapping. PGP encryption is applied to the stream of bytes sent from IWMS to DataMart. DataMart writes decrypted XML as it reads the encrypted stream from the IWMS RESTful endpoint.

    • Global Fields First: Retrieve global fields before retrieving user defined fields.

      This causes the global fields to be added to the first partitioned table. User defined fields always start on the second partitioned table.

    • Only Global Fields: Only retrieve global fields for the mapping.

      If you modify the Global Fields First or Only Global Fields settings on an existing mapping, the Trigger Full Run warning displays. If you click Save in the warning message. The next run is a full run, and DataMart re-partitions your tables.

    • Auto Schema: Enable incremental updates. These updates include schema changes so that your DataMart instance includes both user defined fields and new global fields without having to do a full brush rebuild of the target database from scratch.

      DataMart checks the number of fields which exist on a database table. If the maximum number has been exceeded, new fields are added to a subsequent table.

  9. Click Finish.

    The mapping is created.