Introduction

This guide is generic and applies to all Longview products (Close, Tax, Plans, Transfer Pricing). 

Installation Prerequisites

• CXO Version 21.3.3 (or higher)

• .NET 56.0 or higher has to be installed

• Longview version 21.2 or newer

Installation

• Install Longview Adapter

CXO

...

Configuration and Source Creation

Note: To be able to use the Longview Adapter you need require a license key with Longview enabled.

1. Create a new source system in the SSM for the correct source type: Longview

...

1. to create:

   • Fact database

   • SSAS cube

...

1. Initialize the Longview adapter and the source.

...

1. This is performed by running a few commands which require few parameters as an input.
   For more information on the available commands and their parameters, run the CXO.Adapter.LongviewTidemark.Cli.exe help command for detailed descriptions.

2. Open command line as Administrator, browse to the Longview adapter folder and run the initialize command to create the configuration database.

...

4. Run the create-source-in-adapter command to create a new Longview source.

• IMPORTANT:

...

• At this point you can decide the type of Longview source

...

• as either Longview Close or Longview Tax. This selection cannot be changed after source creation!
  

...

Command

...

• Longview Plan and Longview Close work the same way. Similarly, Longview Tax and Transfer Pricing work the same way. But if you want to integrate with Longview Plan, you need to select Longview Close, and if you want to connect to Transfer Pricing, you will have to select Longview Tax.

• After running these the above commands, the adapter is configured, and a first source is available. When multiple sources are required, run the create-source-in-adapter command for each source neededthat is required.

• Each source is created with a default configuration for dimensions, Longview symbol names, etc. These defaults need to be changed for the source to function properly. The next section explains how to configure the Longview sources.

Longview Source Configuration

A newly created Longview source needs additional configuration to function properly. Since the Longview adapter currently does not have a user interface, these configurations have to be done by running SQL scripts on the Longview configuration database.

Longview Connection

To show the currently configured connection information for the sources in the Longview adapter, run the following query on the configuration database:

...

select  s.Name, 
        Type = CASE 
             WHEN s.Type =  1 THEN 'Longview Close'
             WHEN s.Type =  2 THEN 'Longview Tax'
             ELSE 'Unknown'
        END,
        sc.Url,
        sc.UserName,
        sc.Password,
        sc.ListenerPort,
        sc.Access, 
        sc.UserGroup,
        sc.InstanceId
from    Source s
join    LVSourceConfiguration sc on sc.SourceId = s.Id

The results result of this query shows per source the is shown for each source:

• URL to the Longview instance

...

• .

• Username

• Password to authenticate with the Longview

...

• systems and

...

• few

...

• Longview-specific fields.

The values for these fields should be provided by people user managing the Longview system to connect to.

To update the connection fields, run the following query command with the correct values and source name. When certain values do not need to be replaced, remove the particular lines from the query.

QUERY 2

...

...

those parameters. Especially use this command when updating password. If password is updated by SQL update query it will not be encrypted, running this command updates and encrypts the password.

Other option to update the connection fields is by running the following query with the correct values and source name. When certain values do not need to be replaced, remove those lines from the query. This update query should not be used for updating the Password value since it will not be encrypted. Use the above command for updating password.

QUERY 2

update  sc
set     sc.UserGroupUrl = '...',
        sc.InstanceIdUserName = '...',
from    LVSourceConfiguration sc
join    Source s on sc.SourceId = s.Id where   ssc.NameListenerPort = 'SOURCE0,
NAME HERE'

An example to replace just the URL and credentials for a source called MyLongviewSource:

update  sc set     sc.UrlAccess = 'https://example-url-to-longview-source.com...',
        sc.UserNameUserGroup = 'Administrator...',
        sc.PasswordInstanceId = 'secret_password...'
from    LVSourceConfiguration sc
join    Source s on sc.SourceId = s.Id
where   s.Name = 'MyLongviewSource'

Showing Dimension Configuration for a Source

To show the currently configured dimension mappings for a given source, run the following query with the correct source name:

...

SOURCE NAME HERE'

An example to replace just the URL and credentials for a source called MyLongviewSource:

selectupdate  dm.LVSourceConfigurationId,sc
set     sc.Url    dm.CxoDimension= 'https://example-url-to-longview-source.com',
         dm.LVDimensionName sc.UserName = 'Administrator',
from    Source s
join    LVSourceConfiguration sc on sc.SourceId =LVSourceConfiguration s.Idsc
join    DimensionMappingSource dms on dmsc.LVSourceConfigurationIdSourceId = scs.Id
where   s.Name = 'SOURCE NAME HERE'

This shows which Longview dimension is mapped to which CXO dimension. If the LVDimensionName column has a NULL value, nothing is mapped to this CXO dimension.

For each Longview dimension we also have to configure:

• The symbol names to extract

• The symbol attributes to extract

• The description of the Longview dimension

...

where   s.Name = 'MyLongviewSource'

Showing Dimension Configuration for a Source

To show the dimension mappings for a given source that has been configured, run the following query with the correct source name:

QUERY 43

select  dec.LVSourceConfigurationId, 
        dec.DimensionName,
        dec.DimensionDescription,
        dec.SymbolNamesdm.LVSourceConfigurationId, 
        decdm.SymbolAttributesCxoDimension, 
        dm.LVDimensionName 
from    Source s
join    LVSourceConfiguration sc on sc.SourceId = s.Id
join    LVDimensionsExtractionConfigurationDimensionMapping decdm on decdm.LVSourceConfigurationId = sc.Id
where   s.Name = 'SOURCE NAME HERE'

Finally, it is possible to extract additional attributes from the Longview system and link them to members in a configured dimension. To show the currently configure member attribute mappingsThe result shows which Longview dimension is mapped to which CXO dimension. If the LVDimensionName column has a NULL value, nothing is mapped to this CXO dimension.

For each Longview dimension we also must configure:

• The symbol names to extract

• The symbol attributes to extract

• The description of the Longview dimension

To display the values for these fields that are currently configured, run the following query:

QUERY 54

select  dm.Iddec.LVSourceConfigurationId, 
        dec.DimensionName,
        dmdec.CxoDimensionDimensionDescription,
         dmdec.LVDimensionNameSymbolNames, 
        mam.CxoMemberAttribute, 
dec.SymbolAttributes        mam.SymbolAttribute
from    Source s
join    LVSourceConfiguration sc on sc.SourceId = s.Id
join    DimensionMapping dm on dm.LVSourceConfigurationId = sc.Id
join	MemberAttributeMapping mam on mam.DimensionMappingId = dm.Id
where   s.Name = 'SOURCE NAME HERE'

Per dimension this shows the SymbolAttribute that needs to be extracted from Longview, plus the CXO attribute on the member that needs to get the extracted value. For instance, the ZGPNativeCurrency attribute can be mapped to the Currency attribute of the CXO Entity dimension.

Updating Dimension Configuration for a Source

For updating dimension configuration of a Longview source we identify the following scenarios:

1. Create a new dimension mapping from a Longview dimension to a CXO dimension

2. Remove an existing dimension mapping from a Longview dimension to a CXO dimension

3. Update symbol names, symbol attributes and/or dimension description for a mapped Longview dimension.

4. Create a new member attribute mapping

5. Remove an existing member attribute mapping

NOTE: if you want to map a Longview dimension mapping to a CXO dimension that already has a Longview dimension mapped to it, first remove the existing mapping before adding the new mapping.

Create a Dimension Mapping

To create a new dimension mapping from a Longview dimension to a CXO dimension, run the following query with correct values for LVSourceConfigurationId and CxoDimension (you get these from running QUERY 3) plus the name of the LV Dimension you want to map.

QUERY 6

declare @lv_dimension_name varchar(255) = 'LV DIMENSION NAME HERE';
declare @lv_source_configuration_id uniqueidentifier = 'LV SOURCE CONFIGURATION ID HERE';
declare @cxo_dimension varchar(50) = 'CXO DIMENSION HERE';

update	DimensionMapping
set		LVDimensionName = @lv_dimension_name
where	LVSourceConfigurationId = @lv_source_configuration_id
and		CxoDimension = @cxo_dimension

insert into LVDimensionsExtractionConfiguration (
    Id, DimensionName, LVSourceConfigurationId, SymbolNames, 
    DimensionDescription, SymbolAttributes)
values (newid(), @lv_dimension_name, @lv_source_configuration_id, '', '', '')

After running this query, there will be no symbol names, symbol attributes or dimension description configured yet. Proceed to update these by following Scenario 3: Update Symbol Names, Symbol Attributes and Dimension Description

Remove a Dimension Mapping

...

LVDimensionsExtractionConfiguration dec on dec.LVSourceConfigurationId = sc.Id
where   s.Name = 'SOURCE NAME HERE'

It is possible to extract additional attributes from the Longview system and link them to members in a configured dimension. To display the member attribute mappings that are currently configured, run the following query:

QUERY 5

select  dm.Id,
        dm.CxoDimension, 
        dm.LVDimensionName, 
        mam.CxoMemberAttribute, 
        mam.SymbolAttribute
from    Source s
join    LVSourceConfiguration sc on sc.SourceId = s.Id
join    DimensionMapping dm on dm.LVSourceConfigurationId = sc.Id
join	MemberAttributeMapping mam on mam.DimensionMappingId = dm.Id
where   s.Name = 'SOURCE NAME HERE'

For every dimension the SymbolAttribute that needs to be extracted from Longview and the CXO attribute on the member that needs to get the extracted value is displayed. For instance, the ZGPNativeCurrency attribute can be mapped to the Currency attribute of the CXO Entity dimension.

Updating Dimension Configuration for a Source

For updating dimension configuration of a Longview source:

1. Create a new dimension mapping from a Longview dimension to a CXO dimension.

2. Remove an existing dimension mapping from a Longview dimension to a CXO dimension.

3. Update symbol names, symbol attributes and/or dimension description for a mapped Longview dimension.

4. Create a new member attribute mapping.

5. Remove an existing member attribute mapping.

Create a Dimension Mapping

To create a new dimension mapping from a Longview dimension to a CXO dimension, run the following query with correct values for LVSourceConfigurationId and CxoDimension (you get these from running QUERY 3) .

QUERY 7

...

...

and the name of the LV Dimension you want to map.

QUERY 6

declare @lv_dimension_name varchar(255) = (
    select LVDimensionName 
    from DimensionMapping 
    where LVSourceConfigurationId = varchar(255) = 'LV DIMENSION NAME HERE';
declare @lv_source_configuration_id uniqueidentifier = 'LV SOURCE CONFIGURATION andID CxoDimensionHERE';
=declare @cxo_dimension varchar(50) = 'CXO DIMENSION HERE';

update  	DimensionMapping
set     LVDimensionName = NULL
where   		LVDimensionName = @lv_dimension_name
where	LVSourceConfigurationId = @lv_source_configuration_id
and     		CxoDimension = @cxo_dimension

deleteinsert frominto LVDimensionsExtractionConfiguration (
where    Id, DimensionName, LVSourceConfigurationId, = @lv_source_configuration_idSymbolNames, 
 and   DimensionDescription,  DimensionName = @lv_dimension_name

Update Symbol Names, Symbol Attributes and Dimension Description

To update symbol names, symbol attributes and dimension description for a LV dimension, run the following query with the correct LVSourceConfigurationId and DimensionName (you get these from running QUERY 4).

...

SymbolAttributes)
values (newid(), @lv_dimension_nname, @lv_source_configuration_id, '', '', '')

Update Symbol Names, Symbol Attributes and Dimension Description which are yet to be configured.

For Longview plan and close, the time period dimension is by default mapped to the other three dimensions, Year, Period, and View. To add a 4th dimension (usually category) run the following query:

QUERY 7

update	DimensionMapping
set		LVDimensionName LVDimensionsExtractionConfiguration= set     SymbolNames'TimePer'
where	LVSourceConfigurationId = @lv_source_configuration_id
and		CxoDimension = '...',
		SymbolAttributes = '...',
		DimensionDescription = '...'
where   LVSourceConfigurationId = 'LV SOURCE CONFIGURATION ID HERE'
and     DimensionName = 'DIMENSION NAME HERE'

Create a Member Attribute Mapping

...

CAT'

Update Symbol Names, Symbol Attributes and Dimension Description which are yet to be configured.

Note: Depending on the Longview installation and configuration TimePer could be named under a different identifier. Other names could be ‘Timeper’, ‘TIMEPER’, ‘Timeperiods’. to check this name, run the following query:

select LVDimensionName
from DimensionMapping
where
LVSourceConfigurationId = @lv_source_configuration_id
and CxoDimension in ('YER', 'PER', 'VIW')
group by LVDimensionName

Remove a Dimension Mapping

To remove a dimension mapping from a Longview dimension to a CXO dimension, run the following query with the correct DimensionMappingId (you get this from running QUERY 5values for LVSourceConfigurationId and CxoDimension (run QUERY 3).

QUERY 98

insert into MemberAttributeMapping 
    (Id, DimensionMappingId, CxoMemberAttribute, SymbolAttribute)
values (
    newid(), 
    'DIMENSION MAPPING ID HERE', 
    'CXO MEMBER ATTRIBUTE HERE', 
    'SYMBOL ATTRIBUTE HERE')

Remove a Member Attribute Mapping

To remove an existing member attribute mapping, run the following query with the correct DimensionMappingId and SymbolAttribute (you get these from running QUERY 5).

QUERY 10

delete from MemberAttributeMapping
where DimensionMappingId = 'DIMENSION MAPPING ID HERE'
and   SymbolAttribute = 'SYMBOL ATTRIBUTE HERE'

Source Metadata and Data extraction

• Performs a full extraction (metadata + data) for the given source and year/scenario slice.

• Important: The command removes all existing data and metadata from fact database and imports data only for the years and scenarios specified in the arguments

...

Command

...

full-extract

...

Parameters

...

• source: name of source for which metadata should be extracted

• years: years for which data extraction is executed

• scenarios: scenarios for which data extraction is executed

...

Example

...

.\CXO.Adapter.LongviewTidemark.Cli.exe full-extract source:longview-close years:17,18 scenarios:Actual

• Performs a metadata extraction for the given source.

• Important: The command removes all existing metadata from fact database and re-imports it again.

...

Command

...

metadata-extract

...

Parameters

...

• source: name of source for which metadata should be extracted

...

Example

...

.\CXO.Adapter.LongviewTidemark.Cli.exe metadata-extract source:longview-close

• Performs a data extraction for the given source and year/period/scenario slice.

• Important: The command removes all existing data from fact database and imports data only for the years, periods and scenarios specified in the arguments

...

Command

...

data-extract

...

Parameters

...

• source: name of source for which metadata should be extracted

• years: years for which data extraction is executed

• periods: periods for which data extraction is executed

• scenarios: scenarios for which data extraction is executed

...

Example

...

.\CXO.Adapter.LongviewTidemark.Cli.exe data-extract source:longview-close years:17,18 periods:Jan,Feb scenarios:Actual

IMPORTANT:

...

If the scenarios contain empty space in their member name they need to be put in double quotes. Ex. .\CXO.Adapter.LongviewTidemark.Cli.exe metadata-extract source:longview-close years:17,18 periods:Jan,Feb scenarios:"Scenarios 001","Scenarios 002","Scenarios 003"

...

declare @lv_source_configuration_id uniqueidentifier = 'LV SOURCE CONFIGURATION ID HERE';
declare @cxo_dimension varchar(50) = 'CXO DIMENSION HERE';
declare @lv_dimension_name varchar(255) = (
    select LVDimensionName 
    from DimensionMapping 
    where LVSourceConfigurationId = @lv_source_configuration_id 
    and CxoDimension = @cxo_dimension
);

update  DimensionMapping
set     LVDimensionName = NULL
where   LVSourceConfigurationId = @lv_source_configuration_id
and     CxoDimension = @cxo_dimension

delete from LVDimensionsExtractionConfiguration
where   LVSourceConfigurationId = @lv_source_configuration_id 
and     DimensionName = @lv_dimension_name

Anchor
#Update-Symbol #Update-Symbol

Update Symbol Names, Symbol Attributes and Dimension Description

To update symbol names, symbol attributes and dimension description for a LV dimension, run the following query with the correct LVSourceConfigurationId and DimensionName (run QUERY 4).

QUERY 9

update  LVDimensionsExtractionConfiguration
set     SymbolNames = '...',
		SymbolAttributes = '...',
		DimensionDescription = '...'
where   LVSourceConfigurationId = 'LV SOURCE CONFIGURATION ID HERE'
and     DimensionName = 'DIMENSION NAME HERE'

Create a Member Attribute Mapping

To create a new member attribute mapping for a specific dimension, run the following query with the correct DimensionMappingId (run QUERY 5).

QUERY 10

insert into MemberAttributeMapping 
    (Id, DimensionMappingId, CxoMemberAttribute, SymbolAttribute)
values (
    newid(), 
    'DIMENSION MAPPING ID HERE', 
    'CXO MEMBER ATTRIBUTE HERE', 
    'SYMBOL ATTRIBUTE HERE')

Remove a Member Attribute Mapping

To remove an existing member attribute mapping, run the following query with the correct DimensionMappingId and SymbolAttribute (run QUERY 5).

QUERY 11

delete from MemberAttributeMapping
where DimensionMappingId = 'DIMENSION MAPPING ID HERE'
and   SymbolAttribute = 'SYMBOL ATTRIBUTE HERE'