Data Experience Deployment

Introduction to Cinchy DXD

CinchyDXD is a utility (downloadable) used to move Data Experiences (DX) from one environment to another. This includes any and all objects and components that have been built for or are required in support of the Data Experience.
The following sections in this document will outline the basics of how to build, export, and install a DX’s.
Items of note moving forward in this document:
    Source Environment - is the environment in which the DX is built
    Target Environment - is the environment in which the DX will be installed
    The example DX is a simple Currency Converter DX that consists of
      One (1) table
      One (1) query
    This example does not include the following:
      NO applets
      NO integrated clients
      NO Data Sync Configurations
      NO Reference Data
      NO Models
      NO Groups
      NO System Colours
      NO Formatting Groups
      NO Literal Groups
Future iterations of this document will add to this example's complexity level.

Build the Data Experience (DX)

Table Creation

Create your data experience (DX) in a virtual data fabric.
    1.
    Logon to Cinchy URL: <cinchy source URL> User ID: <source user id> Password: <source password>
    2.
    From under “My Network”, click the Create button
    3.
    Select Table
    4.
    Select From Scratch
    5.
    Create the table with the following properties
Table Details
Values
Table Name
Currency Exchange Rate
Icon + Colour
Choose your own icon
Domain
Sandbox (if the domain does not exist, create it)
To create a domain on the fly:
    1.
    Enter domain name in Domain field
    2.
    Hit enter on keyboard
    3.
    On the Confirm Domain window, click Yes
Description
This table is a test table for building and deploying a data experience for currency conversion
6. Click Columns in the left hand navigation to create the columns for the table
7. Click the “Click Here to Add” a column tab to add a column
Column Details
Values
Column 1
Column Name: Currency 1
Data Type: Text
Advanced Settings:
    Select Mandatory
    Leave all other defaults
Column 2
Column Name: Currency 2
Data Type: Text
Advanced Settings:
    Select Mandatory
    Leave all other defaults
Column 3
Column Name: Rate
Data Type: Number
Advanced Settings:
    Set Decimal Places to 4
    Select Mandatory
    Leave all other defaults
8. Click the Save button to save your table

Enter Sample Data

    1.
    In your newly created table, enter the following sample data:
Currency 1
Currency 2
Rate
CAD
USD
0.71
USD
CAD
1.40

Create Query

Create a simple query that pulls information from the Currency Exchange Rate table that will allow a simple currency exchange calculation.
    1.
    From “My Network”, click the create button
    2.
    Select Query
3. In the query builder locate the Currency Exchange Rate table and drag it to the “FROM” line Hint: you will find the Currency Exchange Rate table in the “Sandbox” domain, to expand the “Sandbox” domain, click on the gray arrow (or double click)
4. In the “SELECT” line drag and drop the “Rate” column and enter in the following: SELECT [Rate] * @Amount AS 'Converted Amount' Hint: you will find the Rate column by expanding the Currency Exchange Rate table, similarly to expanding the “Sandbox” domain
5. Enter in the following for the WHERE clause
WHERE [Deleted] IS NULL AND [Currency 1] = @Currency_1 AND [Currency 2] = @Currency_2
6. Click the Execute (or play) icon to run the query
7. Test the query by entering in the following and clicking the submit button
@Amount: 100 @Currency_1: CAD @Currency_2: USD
8. Save the Query by clicking on the Info tab (Left Navigation) 9. Enter in the following details for the query
Query Details
Values
Query Name
Currency Converter
Icon + Colour
Choose your own icon
Return
Query Results (Approved Data Only)
Domain
Sandbox
API Result Format
JSON
Description
This query is a test query for building and deploying a data experience for currency conversion
10. Click the Save button

Package the Data Experience (DX)

Download CinchyDXD Utility

The CinchyDXD utility is used to take all of the components (e.g. tables, queries, views, formatting rules, UDF’s etc…) of a DX and package them up so they can be moved from one environment to another.
The CinchyDXD utility is only required (made accessible) for the environment that is packing up the data experience. It is not required for the destination (or target) environment.
For CinchyDXD to work, you must have CinchyCLI installed. For further installation instructions please refer to CLI (https://cli.docs.cinchy.com/) documentation
To access the Data Experience Deployment utility please contact Cinchy support ([email protected]).
To download the Utility:
    1.
    Login to Cinchy
    2.
    Navigate to the Versions Table
    3.
    Select the Experience Deployment Utility View
    4.
    Locate and download the utility (e.g. Cinchy DXD v1.3.1.zip)
Note: the CinchyDXD utility is only upwards compatible with Cinchy version 4.6+
5. Unzip the utility and place the folder at any location on a computer that also has CinchyCLI installed
6. Create a new folder in the same directory that will hold all of the DX exports generated (e.g. CinchyDXD_Output)
This folder will then hold all of your deployment packages.
7. Launch a Powershell console window
8. From the console, navigate to the CinchyDXD directory
Tip: From within your file explorer window (folder: Cinchy DXD v.X) type “Powershell” into the file path. It will launch a Powershell window already at the folder path.

One Time Setup: Powershell

There is a one (1) time powershell setup that is required when using CinchyDXD.
    1.
    From your Powershell window type cin
    2.
    Hit Tab on your keyboard
3. Hit Enter on your keyboard
You will get an error message (above) that CinchyDXD.ps1 cannot be loaded because the running script is disabled. To resolve this error:
4. From your start menu, search for Powershell and select Run as Administrator
5. When prompted “if you want to allow this app to make changes on your device”, select Yes.
6. In your Powershell Administrator window enter Set-ExecutionPolicy RemoteSigned
7. Hit Enter on your keyboard
8. When prompted with the Execution Policy Changes, enter A for “Yes to All”
9. Close the Powershell Administrator window
10. Navigate back to your Powershell window for the CinchDXD v.X window
11. From your Powershell window type cin
12. Hit Tab and then Enter on your keyboard
The basic CinchyDXD instructions will be displayed. You will be able to execute commands such as exporting and installing a Data Experience.

Cinchy DXD Tables Overview

There are four (4) tables in Cinchy that are used for packing up and deploying a Data Experience.
Note: The Data Experience is defined and packed in what will be referred to moving forward as the “Source Environment”. Where the environment that the Data Experience will be deployed to will be referenced to as the “Target Environment”.
    1.
    Data Experience Definition Table - this is where the data experience is defined (e.g. tables, queries, views, formatting rules, UDF’s etc.)
    2.
    Data Experience Reference Data Table - this is where we define any data that needs to move with the Data Experience for the experience to work (e.g. lookup values, static values that may need to exist in tables - it typically would not be the physical data itself)
    3.
    Data Experience Releases Table - once a Data Experience is exported, an entry is created in this table for the export containing:
      Version Number
      Release Binary is the location where you can archive/backup your release history in Cinchy Please Note: if you have your own release management system, you do have the option to opt out of archiving the releases in Cinchy and check the release into your own source control
      Release Name
      Data Experience
    4.
    Data Experience Release Artifact Table - stores all of the files that are part of the Data Experience package as individual records along with all of the binary for each record

STEP 1: Define the Data Experience

When setting up a Data Experience definition, you will need one (1) definition for each Data Experience you wish to package and deploy to a given number of Target Environments.
    1.
    Locate and open the Data Experience Definitions table
Column
Definition
GUID
This value is calculated, please note this value will be required as one of your export parameters in Powershell
Name
This is the Name of your Data Experience
Tables
Select all tables that are part of the Data Experience
Integrated Clients
Select any integrated clientes (e.g. Tableau, PowerBI, custom integrations) that are part of the Data Experience
Data Sync Configurations
Select any data syncs (e.g. CLI’s experience needs to work) that are part of the Data Experience
Reference Data
Select any reference data that is part of the Data Experience. Please note that the setup of the reference data is done in the table called Data Experience Reference Data (see step 2 below for setup details)
User Defined Functions
Select any user defined functions (e.g. validate phone, validate email) that are part of the Data Experience
Models
Select any custom models that override columns or tables in your Data Experience, if there are none - leave blank
Groups
Select any groups that are part of the Data Experience (when moving groups, it will also move all table access [design] controls)
System Colours
Select a system colour (if defined) for the Data Experience
Saved Queries
Select any queries that are part of the Data Experience
Applets
Select any appletes that are part of the Data Experience
Formatting Rules
Select any formatting rules that are part of the Data Experience
Literal Groups
Select any literals that are associated to the Data Experience (e.g. key values with English and French definitions)
Builder
Select the builder(s) who have permission to export the Data Experience
Builder Groups
Select the builder group(s) that have permission to export the Data Experience
Note: Best Practice is to use a Group over a User. Users within groups can fluctuate, where the Group (or Role) will remain. This will require less maintenance moving forward
Sync GUID
Leave this column blank
2. Complete the enter/select the following:
Column
Value
Name
Currency Converter
Tables
Currency Exchange Rate (Sandbox)
Saved Queries
Currency Converter
Builder Groups
Currency Converters
Note: If you make changes to the DX in the future, you are NOT required to build a new Data Experience Definition in this table, you will update the existing definition. If you need to review what the definition looked like historically, you can view it via the Collaboration log.

STEP 2: Define the Reference Data

When setting up a Data Experience Reference Data definition, you will need one (1) definition for each Reference Data table you wish to package and deploy with your Data Experience to the Target Environment.
Note: this table set up will be similar to how you would set up a CLI.
    1.
    Locate and open the Data Experience Reference Data table
Column
Definition
Name
This is the Name of your Reference Data Table, note this name can be anything and does not have to replicate the actual table name
Ordinal
The ordinal number assigned will identify the order in which the data is loaded and required based on dependencies within the data experience. For example if you have tables that have hierarchies in them, you will need to load the parent records first and then load your child records which would then resolve any links in the table.
Filter
This is where a WHERE clause would be required. For example, if you have a table that has hierarchies, you would require two rows within the Data Experience Reference Data table. One to load the parent data and one to load the children data. In the parent record a filter WHERE clause would be needed to filter all parent records. In the second record in the filter column a WHERE clause in another in the secord record that would be needed to filter the children records.
New Records
Identify the behaviour of a new record (e.g. insert, update, delete, ignore)
Change Records
Identify the behaviour of a changed record (e.g. insert, update, delete, ignore)
Dropped Records
Identify the behaviour of a dropped record (e.g. insert, update, delete, ignore)
Table
Identify the table that you are exporting data from
Sync Key
Required (need definition)
Expiration Timestamp Field
If Dropped Records is set to “Expire” then a timestamp column is required
Based on the configuration set up in this table, Cinchy will export the data and create CSV and CLI files.
Please note in this example we do not have Reference Data as part of our Data Experience.

STEP 3: Export the Data Experience

Using Powershell you will now export the Data Experience you have defined within Cinchy.
    1.
    Launch Powershell and navigate to your CinchyDXD folder
Reminder: you can launch Powershell right from your file explorer window in the CinchyDXD folder by entering in the folder path “powershell” and hitting enter on your keyboard. Saving you an extra step of navigating to the CinchyDXD folder manually in Powershell.
2. In the Powershell window type in cin and hit tab on your keyboard
3. Hit Enter on your keyboard, you will see a list of commands that are available to execute.
4. In the Powershell command line hit your “up” arrow key to bring back the last command and type export next to it.
5. Hit Enter on your keyboard
The Powershell window will provide you with the required and optional components to export the data experience.
6. Let’s now set up our mandatory export parameters
NOTE: the parameters executed in Powershell can exist on one line in powershell, however for legibility (below) the parameters have been put on separate lines. If you are putting your parameters on separate lines you will be required to have backticks quote ` for the parameters to execute.
NOTE: please ensure that you are using the sample below as a sample. You will be required to provide values that correspond to:
    the URL for the source environment
    the User ID for the user who is performing the export
    the Password for the user who is performing the export
    your folder path for where CLI is stored
    your folder path for where the CLI output files are written to
    the GUID for the Data Experience that is generated in the Data Experience Definition table
    your own version naming convention
    your folder path for where your CinchyDXD output files are written to
Sample: .\CinchyDXD.ps1 export ` -s "<cinchy source url>" ` -u "<source user id>" ` -p "<source passsword>" ` -c "C:\Cinchy CLI v4.0.2" ` -d "C:\CLI Output Logs" ` -g "8C4D08A1-C0ED-4FFC-A695-BBED068507E9" ` -v "1.0.0" ` -o "C:\CinchyDXD_Output" `
7. Enter the export parameters into the Powershell window
8. Hit Enter on your keyboard to run the export command
Powershell will begin to process the export. Once the export is complete, Powershell will provide you with an export complete message.

STEP 4: Validate Export

Now that the export is completed, be sure to validate the following:
    1.
    DXD Export Folder is populated
2. Data Experience Release table is populated in the source environment
3. Data Experience Release Artifacts table is populated in the source environment

Install the Data Experience (DX)

The install of a Data Experience is executed in a different environment than that of the export. Please ensure that before moving forward with the following instructions you have an environment to install the data experience into. The install of a data experience MUST be done in the same version i.e. your source and target environment version MUST be the same (e.g. Source Version = 4.11 | Target Version = 4.11)
Below are the details that will be required for the installation environment
    Source: <cinchy target url>
    UserID: <target user id>
    Password: <target password>

STEP 1: Install the Data Experience

Using Powershell you will now install the Data Experience you have exported out of Cinchy.
1. Open File Explorer, navigate to the DX exported folder (see Step 4: Validate Export [e.g. Currency Converter folder])
2. In the folder path URL for the exported data experience type in powershell (to launch Powershell for that path).
3. Hit Enter on your keyboard, the powershell window will appear
4. In the Powershell window type in cin and hit tab on your keyboard
5. In the Powershell command line hit next to .\CinchyDXD.ps1 type install
6. Hit Enter on your keyboard
The Powershell window will provide you with the required and optional components to install the DX.
7. Let’s now set up our mandatory install parameters
NOTE: the parameters executed in Powershell can exist on one line in powershell, however for legibility (below) the parameters have been put on separate lines. If you are putting your parameters on separate lines you will be required to have backticks quote ` for the parameters to execute
Sample: .\CinchyDXD.ps1 install` -s "<target Cinchy url>" ` -u "<target user id>" ` -p "<target password>" ` -c "C:\Cinchy CLI v4.0.2" ` -d "C:\CLI Output Logs" `
Note: be sure that the user(s) and group(s) required to install a DX are existing in your target environment. If they are not, Powershell will generate an error message when you attempt to install the DX.
8. Enter the install parameters into the Powershell window
9. Hit Enter on your keyboard to run the install command. Once the Data Experience has been installed you will get a message in Powershell that the install was completed.

STEP 2: Validate Install

Now that the install is completed, be sure to validate the following:
    1.
    Models Table in populated in the Target environment with the model that was installed
2. Currency Exchange Rate tale exist in the Target environment
3. Currency Converter query exist in the Target environment
4. Data Experience Definitions table is populated with the DX parameters that were setup in the Source environment
5. Data Experience Releases table in the target environment is populated

Update the Data Experience (DX)

There are a few updates that are required in the Data Experience that has been created in our source environment. We do not want to have to repeat the updates in both the source and target environments. In the upcoming sections we will look at how to update the data experience in the source environment, re-package (re-export) the data experience and reinstall the data experience in the target environment.

Table Updates

    1.
    Log back into the source environment using the following: URL: <cinchy source url> User ID: <source user id> Password: <source password>
    2.
    Make the following changes to the Currency Exchange Rate Table:
Column Details
Values
Column 1
Current Column Name Value = Currency 1
New Column NameValue = From Currency
All other settings remain the same
Column 2
Current Column Name Value = Currency 2
New Column NameValue = To Currency
All other settings remain the same
Be sure to save your change before leaving the table.

Query Updates

    1.
    Update the Currency Converter query to reflect column name changes that were made in the Table Updates section (above)
Reminder: Be sure to update the @Currency_1 and @Currency_2 labels to better reflect the input fields
2. Test the query to validate that it is still functioning
3. Be sure to save your query

Re-Package the Data Experience (DX)

Now that you have made any necessary changes to the DX it is time to re-export the package out of our source environment and re-install it into our target environment.
It is important to note that you should not create a new record in the Data Experience Definition table though the evolution of your DX, the initial record will be used to expand the definition of the DX. For example, if new objects (tables, queries, applets) integrations and UDF’s are added to the DX, the DX definition will need to be updated. To see the historical definitions of the DX, please see the Collaboration Log.

STEP 1: Update Data Experience Table

If you have added or removed any of the following you will need to update the Data Experience Definition table:
    Name
    Tables
    Integrated Clients
    Data Sync Configurations
    Reference Data
    User Defined Functions
    Models
    Groups
    System Colours
    Saved Queries
    Applets
    Literal Groups
    Builders
    Builder Groups
    Sync GUID

STEP 2: Update Reference Data Table

If you have added or removed any of the following you will need to update the Data Experience Reference Data table:
    Name
    Ordinal
    Filter
    New Records
    Changed Records
    Dropped Records
    Table
    Sync Key
    Expiration Timestamp Field
    Sync GUID

STEP 3: Re-Run CinchyDXD Export

Using Powershell you will now export the Data Experience you have defined within Cinchy.
    1.
    Launch Powershell and navigate to your CinchyDXD folder
Reminder: you can launch Powershell right from your file explorer window in the CinchyDXD file, saving you an extra step of navigating to the CinchyDXD folder manually in Powershell.
2. In the Powershell window type in cin and hit tab on your keyboard 3. In the Powershell command line next to .\CinchyDXD.ps1 type in export 4. Hit Enter on your keyboard
Reminder: If you do not remember the mandatory parameters, you can click the enter on your keyboard after typing in .\CinchyDXD.ps1 export, Powershell will provide you with the required and optional components to export the data experience.
5. Let’s now enter the mandatory export parameters
NOTE: the parameters executed in Powershell can exist on one line in powershell, however for legibility (below) the parameters have been put on separate lines. If you are putting your parameters on separate lines you will be required to have backticks quote ` for the parameters to execute
NOTE: you will need to update your version number Sample: .\CinchyDXD.ps1 export ` -s "<source Cinchy url>" ` -u "<source user id>" ` -p "<source password>" ` -c "C:\Cinchy CLI v4.0.2" ` -d "C:\CLI Output Logs" ` -g "8C4D08A1-C0ED-4FFC-A695-BBED068507E9" ` -v "2.0.0" ` -o "C:\CinchyDXD_Output" `
6. Enter the export parameters into the Powershell window
7. Hit Enter on your keyboard to run the export command
Powershell will begin to process the export. Once the export is complete, Powershell will provide you with an export complete message.

STEP 4: Validate Export

Now that the export is completed, be sure to validate the following:
    1.
    DXD Export Folder is populated
2. Data Experience Release table is populated in the source environment
3. Data Experience Release Artifacts table is populated in the source environment

Re-Install the Data Experience (DX)

STEP 1: Re-Run CinchyDXD Install

Using Powershell you will now install the Data Experience you have exported out of Cinchy.
    1.
    Open File Explorer, navigate to the exported folder (see Step 4: Validate Export)
2. In the folder path URL for the exported data experience type in powershell (to launch Powershell for that path)
3. Hit Enter on your keyboard, the powershell window will appear
4. In the Powershell window type in cin and hit tab on your keyboard and type install
5. Enter the install parameters into the Powershell window
NOTE: the parameters executed in Powershell can exist on one line in powershell, however for legibility (below) the parameters have been put on separate lines. If you are putting your parameters on separate lines you will be required to have backticks quote ` for the parameters to execute
Sample: .\CinchyDXD.ps1 install -s "<taget Cinchy url>" ` -u "<target user id>" ` -p "<target password>" ` -c "C:\Cinchy CLI v4.0.2" ` -d "C:\CLI Output Logs" `
6. Hit Enter on your keyboard to run the install command. Once the Data Experience has been installed you will get a message in Powershell that the install was completed.

STEP 2: Validate Install

Now that the install is completed, be sure to validate the following in the target environment.
    1.
    Models Table in populated in the Target environment with the model that was installed
2. Currency Exchange Rate table exist in the Target environment with the new column names
3. Currency Converter query exist in the Target environment with the new column names and labels
4. Data Experience Definitions table should not change unless you have added or removed column details within this table
5. Data Experience Releases table in the target environment is populated with the new release version number from the install (e.g. 2.0.0)
Last modified 7mo ago