Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Follow these instructions to create a new table within Cinchy.
From the home screen, select Create in the top left hand corner to get started.
A spatial table allows you to create geography and geometry column types, as well as geospatial indexes. You will not be able to create partitions on a spatial table.
A standard table will not allow you to create geography and geometry columns. (Any existing tables created before installing Cinchy Platform v4.19.0 are standard tables).
You cannot convert from one type to another and will have to either recreate your table or link to another table with geospatial columns.
Mandatory field. Must be unique within the Domain.
I.e. You can have a [Football].[Teams] table and a [Basketball].[Teams] table, but not two tables named [Teams] under the Football domain.
You can optionally pick an icon, as well as color for your table. This will be displayed on the home screen.
You need to select a Domain your table will reside under. As an admin, you can also create new domains in this screen.
You can give your table a description. This description will be displayed on the home screen to users browsing the marketplace.
Your table must have at least one column to start. See Column Types to decide what type of column you should create and how to create it. Depending on the type, you will create it either under the columns or the links tab.
You can now click Save to create your first table!
You need to select a Domain your table will reside under. As an admin, you can also create new domains in this screen.
You must upload a .csv file. The column names must not conflict with System Columns.
When creating a table via Import a CSV, a few settings will be set by default. These can be modified after the table is imported through the Design Table tab.
The name of the file will be used as the name of the table (a number will be appended if there is a duplicate - ex. uploading Teams.csv will create a table named Teams 1, then Team 2 if uploaded again). You can always rename the table after it has been created.
The icon defaults to a green paintbrush.
Columns by default will be created as a text field, with a maximum length of the longest value in the column. If a column has only numeric values in it, it will be created as a numeric column.
When you first create a table, a default view called All Data will be created for you under Manage Data.
You can create additional views or edit the All Data view under Managing Data.
Once you create a table, it will be added to your bookmarks by default. Other users (or if you un-star the table from your bookmarks) will see it in the Marketplace if they have permissions to.
You can click the table in the home screen to get back to the Data Management screen for your table.
Cinchy contains system columns used to perform various functionality. These columns cannot be modified directly by a user. You cannot create a column with the same name.
Cinchy Id is a unique identifier assigned automatically to all records within a normal table. The Cinchy Id is associated with the record permanently and is never reassigned even if the record is deleted.
Version and Draft Version are used to track changes and versions.
Any changes made to a record increments the Version
. Draft Version
is always 0.
Any data approval increments Version
and resets Draft Version
to 0. Any proposed changes increments the Draft Version
.
Legacy column - always blank.
Created By
is a linked column to the [Cinchy].[Users]
table, of the user who created the record.
Created
is the time when the record was created.
Created By
and Created
will be the same for all records with the same Cinchy Id.
Created By
and Created
is based on the first user to make changes on an approved record. So the user where Draft Version
= 1.
Modified By
is a linked column to the [Cinchy].[Users]
table, of the user who last modified the record.
The last user to modify the record, and when it happened.
The last user to either modify the record (Draft Version
!= 0) or approve the record (Draft Version
= 0). The timestamp for when that version was generated.
If a record is deleted, it will show up in the Recycle Bin.
A deleted record will have Deleted By
and Deleted
filled in.
Deleted By
and Deleted
are based on the user/time when the Delete Request was created, not when it was approved.
There is always only one latest/up to date record at a time. Anytime changes are made to a record, a new version (normal or draft) is created, and the previous version is updated with a Replaced
timestamp.
Any record where Replaced
is empty is the current version of that record.
Within Cinchy, you can change the structure of your table through the UI instead of at a database level.
To change the structure of an existing table (i.e. Rename columns, add new columns, change data type) go to Design Table in the left navigation.
To create a new table, please see creating your first table.
All possible column types in Cinchy.
Each column must have a unique name. They must also not conflict with system columns (even if you are not using Maker/Checker on the table).
Each column has a data security classification. This defaults to blank, and can be set to one of 4 pre-configured settings (Public, Internal, Restricted, Confidential) or additional options created in the [Cinchy].[Data Security Classifications]
table by an administrator.
Currently there is no functionality tied directly to Data Security Classification - the tagging is just for internal auditing purposes. Future security settings will be tied to Data Security Classifications, rather than simply done at a column level.
Each column can optionally have a description. The description is displayed when you hover on the column header in Data Management.
Checked by default. After saving your changes this will add the column to be displayed in the default table (All Data by default). Generally it makes sense to be checked since there should be a view where all columns are displayed.
If you need to hide a column from certain users or groups you can do so in table controls. It is usually best to have a view where all table columns are displayed.
Makes the column a mandatory field. You will not be able to save or alter a record in a state where a mandatory field is blank.
Requires all values in the column to be unique. Adding a new record or modifying a previous record into a state where it is a duplicate of another record will cause an error and cannot be saved.
If you need uniqueness across multiple columns instead (ex. First Name does not need to be unique, but First Name + Last Name needs to be unique), you can create an index in Design Table, add those columns and set the index to unique. If it needs to be more complicated, you can also create a calculated column and set that column to unique.
Some fields can also be set to multi-select.
For example, the column Players
in [Football].[Teams]
can be a multi-select field since each team will have multiple players.
Checked by default. This allows other tables to use the column as a link/relationship.
See Linking Data to get more context on how they are used.
You want to pick identifying columns for linking, such as IDs or Name. Generally you want to use unique columns, but in some cases it is a better user experience to pick an almost unique field for readability.
I.e. Full name may not be unique, but it is much easier to understand than Employee ID.
Checked by default. Some columns may not make sense for linking but can be useful to display when someone is choosing an option.
See Linking Data to get more context and tips.
If Data At Rest Encryption is enabled, you will see the option of Encrypt for columns. If this is checked, the column will be encrypted within the database. This is useful for hiding sensitive information so that people with access to the database directly do not see these fields.
There is no difference in user experience within the Cinchy platform. The data is displayed in plain text on the UI or via the query APIs.
Text columns have a maximum length, set to 500 by default.
These are equivalent to VARCHAR(n)
data type in SQL.
You can choose from 3 display formats for number - regular, currency, and percentage. You can also decide how many decimal places to display (0 by default). Note that these are both display settings, and will not affect how the number is stored.
These are equivalent to FLOAT(53)
data type in SQL (default, 8-byte field).
There are several Date column type display format options available in Cinchy:
MMM DD, YYYY (e.g. Oct 31, 2016)
YY-MM-DD (e.g. 16-10-31)
DD-MM-YYYY (e.g. 31-10-2016)
DD-MMM-YY (e.g. 31-Oct-16)
Custom Format
Custom data formatting provides additional flexibility in how dates (and times) can be displayed within a Date column type:
Please Note: the "Default Value" field is not mandatory and should be left blank (best practice). However, if populated you will not be able to clear the default date value provided to a "blank" data (no date). You will only be able to overwrite it with another date value.
These are equivalent to DATE()
data type in SQL.
You must select a default value of yes (1) or no (0) for yes/no fields.
These are equivalent to bit
data type in SQL.
A calculated column is evaluated using other fields on the record. It will also have a result type - which is the form in which the calculated results will be stored.
For example, you can have a column [Full Name]
that is CONCAT([First Name], ' ', [Last Name])
.
These are equivalent to computed columns in SQL.
You can create a choice column (single or multi-select) in Cinchy. In this scenario, you specify all your choices (1 per newline) in the table design. A user will only be able to select from the options provided.
If you created a spatial table, you will have access to the geography and geometry column types. These columns also have the option to be indexed via Index in the advanced settings on the column itself.
In the UI, this takes a well-known text (WKT) representation of a geometry object. You can modify or paste the WKT representation directly in the editor on the UI. Geometric functions can be performed on this column through CQL and calculated columns.
In the UI, this takes a well-known text (WKT) representation of a geography object. You can modify or paste the WKT representation directly in the editor on the UI. Geographic functions can be performed on this column through CQL and calculated columns.
Link columns allow you to establish inherent relationships with other records in other tables. See Linking Data for more details.
Hierarchy columns are simply link columns referencing the current table. Some example uses of hierarchies:
Related Software Changes
Manager
This page outlines indexing and partitioning when building tables
Indexing is used to improve query performance on frequently searched columns within large data sets. Without an index, Cinchy begins a data search with the first row of a table and then follows through the entire table sequentially to find all relevant rows. The larger the table(s), the slower the search.
If the table you are searching for has an index for its column(s), however, Cinchy is able to search much quicker.
In the below example, we will set up a query for a Full Name field. When you create an index for that field, an indexed version of your table is created that is sorted sequentially/alphabetically.
When you run your query on this index, that table will be searched using a binary search.
A binary search will not start from the top record. It will check the middle record with your search criteria for a match. If a match it not found, it will check whether the found value is larger or smaller than the desired value. If smaller, it reruns the data check with the top half of the data, finding the median record. If larger, it reruns the data check with the bottom half of the data, finding the median record. It will repeat until your data is found.
In this example, we have a table with employee names (Image 1). We want to search for "John Smith", using the Full Name column.
To set up your index, select Design Table from the left navigation tab.
2. Click Indexes (Image 2).
3. Select "Click Here to Add" and fill out the following information for a new index. Click save when done (Image 3):
Index Name.
Select the column(s) to add to your index. For our example we have selected the Full Name column to be indexed.
You can select more than one column per index.
Select the Included column(s) to add to your index, if applicable.
The difference between regular columns and Included columns is that indexes with included columns provide the greatest benefit when covering your query because you can include all columns your query may reference, such as columns with data types, numbers, or sizes not allowed as index key columns.
For more on Included Columns, click here
4. We can now query our full name column for John Smith and receive our results quicker than if we hadn't set up our index (Image 4).
Note that there is no UI change in the query builder or your results when running a query on an indexed column. The difference will be in the speed of your returned results.
A full-text index is a special type of index that provides index access for full-text queries against character or binary column data. A full-text index breaks the column into tokens and these tokens make up the index data.
Click on Design Table > Full-text Index
Add in the desired column(s) and click save when done (Image 5).
Partitioning data in a table is essentially organizing and dividing it into units that can then be spread across more than one file in a database. The benefits of this are:
Improved efficiency of accessing and transferring data while maintaining its integrity.
Maintenance operations can be performed on one or more partitions more efficiently.
Query performance is improved based on the types of queries most frequently run.
When creating a partition in Cinchy, you use the values of a specified column to map the rows of a table into partitions.
In this example we want to set up a partition that divides our employees based on a Years Active column (Image 6). We want to divide the data into two groups: those who have been active for two years or more, and those who have only been active for one year.
Click on Design Table > Partition
Fill in the following information and click save when done (Image 7):
Partitioning Column: this is the column value that will be used to map your rows. In this example we are using the Years Active column.
Type: Select either Range Left (which means that your boundary will be <=) or Range Right (where you boundary is only <). In this example we want our boundary to be Range Left.
Add Boundary: Add in your boundary value(s). Click the + key to add it to your boundary list. In this example we want to set our boundary to 2.
Once set up, this partition will organize our data into two groups, based on our boundary of those who have a Years Active value of two or above.
2. You can now run a query on your partitioned table (Image 8).
Note that there is no UI change in the query builder or your results when running a query on a partitioned table. The difference will be in the speed of your returned results.
For more formation on creating, modifying or managing Partitioning, please visit Microsoft's Partitioned table and Indexes documentation.
Table of Contents |
---|
Data Controls allow you to set up permissions for who can view, edit, or approve data within a table.
Data Controls can be selected in the left navigation menu.
Currently anyone in the Cinchy Administrators
group has access to perform any action on any objects.
You can use multiple rows to provide entitlements to a user.
In the above scenario, John Smith is part of the Developers group. He is able to view all columns via the entitlement to the Developers group, and he is able to edit both the First Name and Last Name column through different entitlements.
Click Table Level Entitlements for a detailed description of the available entitlement options.
These are entitlements that apply to the entire table.
Marketplace allows a user to see the table within the marketplace. They can search for the table on the My Network screen.
This entitlement also allows a user to see and interact with the Manage Data screen for that table. There are specific scenarios where you may want a user to be able to access the data but not be able to do so via the Manage Data screen.
This permission allows a user to export data from the table via the Manage Data screen.
This permission allows a user to query the data from the table directly in the Query Builder.
This permission allows a user to alter the structure of the table.
This is a builder/administrative function and should not generally be granted to end users.
This permission allows a user to change the permissions on the table.
This is a builder/administrative function and should not generally be granted to end users.
These are entitlements that apply to specific rows. Used in conjuncture with Column Level entitlements this allows for granular cell level entitlements.
This permission allows a user to create new rows in the table.
This permission allows users to delete rows in the table.
This is a CQL fragment that applies a filter to which rows will be viewable or editable. Think of the column entitlements and the fragment as a SQL statement applied to the table.
SELECT {Edit Selected Columns}
WHERE {Editable Row Filter}
Most of these examples will be with the editable row filter so it is easy to see the underlying data for comparison. However this can be done for viewable row data as well.
Edit Specific Columns: Age Editable Row Filter: [Age] > 30
View Specific Columns: First Name, Last Name
Viewable Row Filter: [End Date] IS NULL OR [End Date] > GetDate()
View Specific Columns: All Edit Specific Columns: First Name, Last Name, Age Viewable Row Filter: [First Name] = 'John' Editable Row Filter: [First Name] = 'John'
For the All Users group:
View All Columns: Check
Edit Selected Columns: First Name, Last Name
Editable Row Filter: [User Account].[Cinchy Id] = CurrentUserId()
To allow a user to edit certain fields of their own data, you will need an association from a user to the [Cinchy].[Users]
table. You can then use the following function to allow edit for that user, where [...]
is the chain of link columns to get to the Users table.
[...].[Cinchy Id] = CurrentUserId()
Data erasure allows you to permanently delete data in Cinchy. As the data owner, you can set an erasure policy on your table if you need to delete data for compliance reasons.
The actual erasing of data happens during the maintenance window. Please check with your system administrators to confirm when maintenance is scheduled.
Once data is erased, any links pointing to erased data will look like this:
The time is counted based on the record's modified time stamp, not the deleted time stamp. This means for change approval records it is the time when the pending delete request was approved and moved to the Recycle Bin, not when the delete request was made.
If you need to manage space within your database, you can set a data compression policy. Currently we allow you to permanently delete versions in the collaboration log. Be aware that the current version of compression is a LOSSY process (data will be permanently deleted). Take that into consideration when configuring a policy.
We err on the side of keeping more versions rather than less versions. You can think of the above as keep any versions newer than 180 days and keeping the most recent 50 versions. So as long as a version satisfies one of the two keep conditions, we keep it. Using the example above:
A version that’s from 205 days ago but is amongst the most recent 50 versions (e.g. version 22 of 60) will be kept, because it satisfies at least one condition of being in the most recent 50 versions.
A version that’s from 163 days ago but is version 65 of 80 will be kept, because it satisfies at least one condition of being less than 180 days old.
A version that’s from 185 days ago and is version 65 of 80 will be deleted because, it doesn’t satisfy either of the conditions.
The actual compression of data happens during the maintenance window. Please check with your system administrators to confirm when maintenance is scheduled.
The number of versions is based on the major version and not the minor version. This means for a record on version 35.63 with a keep most recent 10 versions, it will keep all versions 26.0 +, rather than all versions 35.44+.
These are entitlements that apply to specific columns.
This permission allows a user to view all columns within the table.
Note that this applies to any new columns that are added to the table after providing this permission as well.
This is a drop down where you can select the specific columns you want to grant view access to for users.
This permission allows a user to edit all columns within the table.
Note that this applies to any new columns that are added to the table after providing this permission as well.
Giving a user edit permission will also give them view permission.
This is a drop down where you can select the specific columns you want to grant edit access to for users.
Giving a user edit permission will also give them view permission.
This permission allows a user to approve all columns within the table. This also allows users to approve Create and Delete requests.
Note that this applies to any new columns that are added to the table after providing this permission as well.
Approve permissions only apply with Maker/Checker turned on.
Giving a user approve permission will also give them view permission.
This is a drop down where you can select the specific columns you want to grant approve access to for users.
Approve permissions only apply with Maker/Checker turned on.
Giving a user approve permission will also give them view permission.
Link columns require both permission to the column within this table, as well as the column in the link column itself.
When you create a column within Cinchy, you can choose to create a link column. A link column allows you to establish inherent relationships with other tables.
Linking is done by the Cinchy ID, which is unique. When you create a link column, you select a column to link to. This is simply a decision on which field to show from the linked record. You should pick a unique field to link on to avoid confusion if possible. Once a record is created, its Cinchy ID never changes. This means that modifying the row of data in the linked table will not change the relationship in your table to that row. This also means that if you did not use a unique column, even though the UI looks the same, you are actually linking to different rows.
In general, you should only use unique columns as the linked column. This needs to be balanced with readability in other tables. For example, Full Name might not be unique to every employee, but it is a lot more readable and understandable than Employee ID. In other cases, it makes sense to link via an ID and simply add a display column to show relevant information.
To help other builders follow best practices of only linking to unique (or close to unique, such as Full Name) columns, you should un-check the Allow Linking checkbox for non-unique columns so they will not be able to use it for linking.
If this option is unchecked, it prevents users from showing this column in another table. For example, if you have an ID card # within an employees table, you may not want to display it to the rest of the company because it simply would not be relevant when they are linking to employees and want to see additional information (such as department, title, location). Arguably, a lot of these columns are also taken care of by access controls (since most people will not have access to view that column).
Generally unchecking this box should be done sparingly, as it does not impact the security of your data, only how convenient it is to see it.
When you select a record to link to on the Manage Data screen, it can be useful to see additional information about the records to ensure that it is the record you want to link to. You can add additional display columns in the advanced options for link columns.
The link filter filters out records from the drop down list. This is useful for reducing the options to only the typical use case. Commonly used for filtering the drop down to a list of active users or other resources, while not preventing someone from entering missing records with inactive resources.
Note that this is simply a display filter, it does not prevent other values from being entered as long as they are valid records in the linked table.
You can define 1 to 1, 1 to many, and many to many relationships.
Generally it is rare to link 1:1 relationships since they should usually be in the same table. For example, you would not have a separate table for Employee Phone Number and Employee Address, they would simply be two columns within the Employees table. However there are cases nonetheless where it makes sense, for example, a Keycard tracking table where each keycard has 1 assigned employee.
To enforce a 1:1 relationship within Cinchy, you set the unique constraint and leave it as single-select when creating a link column.
A common relationship to have is a one to many relationship. For example, one customer can have multiple invoices.
To enforce a 1:many relationship within Cinchy, you want to create a link column in the table on the “many” side of the relationship (in the above example, in the invoices table) and leave the link column as single select.
You can also have a many to many relationship. For example, you can have multiple customers, and multiple products. Each customer can order multiple products, and each product can be ordered by multiple customers. Another example is books and authors. An author can write multiple books, but a book can also have multiple authors. There are two ways to express many to many relationships.
In the case of multiple customers and multiple products, we want to use orders as an intermediary table to create indirect relationships between customers and products. Each order has 1 customer, and each order has multiple products in it. We can derive the relationship between customers and products through the orders table.
To create a many:many relationship through a different entity, you want to create a table for orders. Within orders, you want to create a single-select link to customers and a multi-select link to products.
In the case of books and authors, it makes sense to create a multi-select link column in the Books table where multiple authors can be selected.
To create a multi-select link column in Cinchy, you simply check off the Multi-Select option when you create a new link column.
When you type in the cell, all displayed columns will be searched through, not just the Linked Column. (Green does not have a B in it, but #00B050 does so the Green record shows up)
You can apply conditional formatting rules. Our first iteration of this is done directly in the Formatting Rules table. A future iteration will add a UI within a table to create them.
This follows the same syntax as a view filter query.
Order in which the formatting rules will apply on the same table. Ordinal 1 will show up above ordinal 2.
Color to highlight the cell. If you want to add your own colors you can do so within System Colours and check off highlight under usage.
Table in which to apply the conditional formatting.
Columns to apply the conditional formatting rules to. You do not need to include any row condition columns within the highlight columns.