Add a dataset based on a server

You can add a new dataset using data from various data sources. This topic describes how to create a dataset based on a database. For details, see Data sources.

If you want to base your dataset on a file, see Add a dataset based on data files.

Prerequisites

  • You have all the necessary role permissions granted by an administrator in Access Manager.

    dataprep.access

    dataprep.dataset.create

    dataprep.data-connection.create (if you need to add files or your own connections)

    dataprep.dataset.extract (if you need to create an extract dataset)

  • You have access to a server or database that you want to use.

  • To connect to data, you need to have one of the following:

    • A saved or shared data connection

    • Connection details to a server

Procedure

  1. On the sidebar, click Datasets.

    The Datasets pane appears.

  2. In the Datasets pane, click New dataset.

    The Step 1 – Connect page opens.

  1. You have two options to proceed with dataset creation:

    To save time, you can use already created connections.

    Click To a saved connection, and then click the tile for the connection you want to use for your dataset.

    By using this option, you establish a new connection to a server and then create a dataset based on it. The connection will be saved on the Data connections pane.

    1. Click To a server.

    2. Click a server that you need.

    3. In the Add data connection dialog, define the connection to the server.

      The available fields depend on the type of data source that you selected.

      Specify the data connection details as follows:

      • URL – Specify the package URL.

      • Namespace – Specify the namespace for the package.

      • Username – Specify the username for the package.

      • Password – Specify the password for the package.

      • Package – Click Search and select the package.

      • Options – Specify a package path if you do not want the system to rebuild the schema each time the data is queried. By specifying a package path, you improve the overall performance of this connection.

      • Ask user for credentials – Switch to On if you want to share this connection or a dataset based on it without sharing your credentials. In this case, recipients will need to provide their credentials to access data from this connection.

      • Connection name – Specify a name for this connection.

      1. Select the authentication mode:

        • Username and password

        • Cognos namespace, username, and password

        • CAM passport – To authenticate with Cognos BI CAM Passport. The CAM passport is required when TM1 uses Cognos authentication, and Cognos uses OpenID Connect identity provider.

      2. Specify the connection details as follows:

        • URL – Specify the cube's URL.

        • Cognos URL – Specify the Cognos URL.

          This field is available only if you selected CAM passport as the authentication mode.

        • Namespace – Specify the namespace for the cube.

          This field is available only if you selected Cognos namespace, username, and password or CAM passport as the authentication mode.

        • Username – Specify the username for the cube.

        • Password – Specify the password for the cube.

        • Cube name – Click Load list to see the list of cubes, and then select the cube name.

        • Measure dimension – Select the dimension that contains the measures for the dataset.

        • Ask user for credentials – Switch to On if you want to share this connection or a dataset based on it without sharing your credentials. In this case, recipients need to provide their credentials to access data from this connection.

        • Connection name – Specify a name for this connection.

      1. Select the authentication mode:

        • Username and password

        • Cognos namespace, username, and password

        • CAM passport – To authenticate with Cognos BI CAM Passport. The CAM passport is required when TM1 uses Cognos authentication, and Cognos uses OpenID Connect identity provider.

      2. Specify the connection details as follows:

        • URL – Specify the address of the server where the cube view is stored, followed by the port, for example, http://10.12.13.221:8001.

        • Cognos URL – Specify the Cognos URL.

          This field is available only if you selected CAM passport as the authentication mode.

        • Namespace – Specify the namespace for the cube view.

          This field is available only if you selected Cognos namespace, username, and password or CAM passport as the authentication mode.

        • Username – Specify the username for the cube view.

        • Password – Specify the password for the cube view.

        • Cube name – Select the cube view name. Click Load list to see the list of cube views.

        • View type – Select which type of view you want to select: Public or Private.

        • Ask user for credentials – Switch to On if you want to share this connection or a dataset based on it without sharing your credentials. In this case, recipients need to provide their credentials to access data from this connection.

        • Connection name – Specify a name for this connection.

      1. Select the authentication mode. You can choose one of the following options:

        • Username and password

        • Windows authentication

      2. Specify the connection details as follows:

        • Server – Specify the address of the Microsoft SQL server.

        • Port – Specify the port used for the database.

        • Database – Specify the name of the database.

        • Username – Specify the username for the database.
          This field is available only if you selected Username and password as the authentication mode.

        • Password – Specify the password for the database.
          This field is available only if you selected Username and password as the authentication mode.

        • Schema – Specify the name of the database schema. Click Load list to view available schemas.

        • Ask user for credentials – Switch to On if you want to share this connection or a dataset based on it without sharing your credentials. In this case, recipients need to provide their credentials to access data from this connection.

        • Connection name – Specify a name for this connection.

      1. Select the authentication mode. You can choose one of the following options:

        • Username, password, and SID

        • Username, password, and service name

      2. Specify the connection details as follows:

        • Server – Specify the address of the server where the Oracle data source is stored.

        • Port – Specify the port used for the database.

        • Service ID – Specify the service ID for the database.
          This field is available only if you selected Username, password, and SID as the authentication mode.

        • Service name – Specify the service name for the database.
          This field is available only if you selected Username, password, and service name as the authentication mode.

        • Username – Specify the username for the database.

        • Password – Specify the password for the database.

        • Schema – Specify the name of the database schema. Click Load list to view available schemas.

        • Ask user for credentials – Switch to On if you want to share this connection or a dataset based on it without sharing your credentials. In this case, recipients will need to provide their credentials to access data from this connection.

        • Connection name – Specify a name for this connection.

      Depending on the database type, the following fields may be available:

      • Server – Specify the address of the server where the database is stored.

      • Port – Specify the port used for the database.

      • Database – Specify the name of the database.

      • Username – Specify the username for the database.

      • Password – Specify the password for the database.

      • Schema – Specify the name of the database schema. Click Load list to view available schemas.

        The field is available for the PostgreSQL, DB2, Apache Derby, and Google Cloud SQL sources.

      • Host – Specify the host for the database.

      • Instance – Specify the instance of the database.

      • Universe – Select a universe for SAP BusinessObjects. Click Load list to view available universes.

      • Cube name – Specify the name of a cube (for Microsoft SQL Server Analysis Services).

      • Ask user for credentials – Switch to On if you want to share this connection or a dataset based on it without sharing your credentials. In this case, recipients will need to provide their credentials to access data from this connection.

      • Connection name – Specify a name for this connection.

    4. Click Test Connection.

    5. If the connection to the data source was successful, click Create dataset.

  2. Select data for your dataset:

    1. In the Choose tables to import page, under Tables, double-click the data source to expand it.

      TM1 source example

      Expand the folders that you want to search in, then type your text, and press Enter.

      The total number of matches found is displayed under the field. Press Enter to navigate the matches one by one. The match in focus is highlighted in blue; all the other matches are highlighted in yellow.

      To preview data in a table, point to the table, and click View data. This option in the Tables pane is not available for Cognos sources and cubes.

      You can add the same columns as a second data source to create multiple-role type joins between the tables from the same data connection.

      When adding the same column or table, you need to provide a new name for the data source.

    2. In the tree, click the measures and dimensions that you want to include in the dataset.

      All the items that you click appear under Selected items.

      If you click a folder, all the items from that folder are added with one click.

      To preview data in a table, point to the table, and click View data. This option is available in the Selected items pane for all data sources.

      To rename all the columns in a table, point to a table, and then click Rename all columns.

      In the Rename all columns dialog, specify how to rename the columns:

      • Add prefix – Add a data source name or a custom string in front of the column name.
      • Add suffix – Add a data source name or a custom string after the column name.
      • Format – Capitalize the column names: only first letter, all uppercase, or all lowercase.

      • Replace – Enter the text that you want to replace and specify a new text. For example, you can replace the underscore with the space character.

      To rename a column in a table, point to the column, and then click Rename column.

      In the Rename columns dialog, specify a new name for the column and click OK.

      To remove an item from the Selected items section, point to a column, and then click Delete. To remove all selected items, click Clear all columns at the bottom of the page.

  3. In the upper-right corner of the page, click Next.

    The Step 2 – Refine page opens.

On this page, you can refine the data that you extracted from a data connection and add more data sources.

  1. Review the connections and data sources details:

    • Connection to the data source
      The icon indicates the type of data source (server or file).

    • Data sources (tables)

      If the source contains only one table, this table is displayed on a tab as a unique data source. The name of the table (data source) is the same as the file name. If you have more than one table, each is placed on a separate tab.

    • Columns selected for the dataset
      You can click Preview data to view the data in columns. The preview is not available for some data sources.

  2. Manage the connections and data sources as needed:

    Click Add more data and follow the required steps for adding a new data source or a new connection. If you want to add a file (for example, an Excel spreadsheet), see Add a dataset based on data files.

    Note: If you have more than one data source, then after data refinement, you will review data joins to ensure relationships among data sources are identified as needed. More details are in Step 3 – Join and Preview.

    Click the connection, and then click Remove next to it.

    Important: Deletion is performed without confirmation and cannot be undone.

    The following options are available:

    • Rename – Click Rename data source and specify a new name for the data source.

      Important: The name of the first data source is used for the new dataset. If you want to create several datasets from the same data source, specify a different name for each dataset. You cannot create several datasets with the same name.

    • Remove – Click Remove next to the name of the data source.

    • Readd a data source – Click the arrow next to the connection name, and then select the data source.

    You have several options for adding a calculation to a dataset. For details, see About calculations.

    If no security filters are added, then all the data in a shared dataset is visible to all its recipients. With a security filter, some recipients have access only to a subset of data that is relevant to them. For example, each department can see the data related to the respective department and not the whole dataset.

    For details, see Row-level security filters.

  3. Manage the columns of each data source as needed:

    The following options are available:

    • Rename – Click column options, and then click Rename. Edit the name as needed.

    • Exclude – Point to the column, click column options, and then click Exclude.

    • Split – Point to the column, click column options, and then click one of the following options:

      • By delimiter – Select or enter the separator at which to split the column into up to 3 columns.

      • By length – Specify the number of characters to repeatedly split the text column into up to 3 columns. For example, split into two columns every 5 characters.

    • Clean – Point to the column, click column options, and then click one of the following options:

      • Format

        • Make uppercase – Make all characters uppercase.
        • Make lowercase – Make all characters lowercase.
        • Capitalize first letter – Make the first letter uppercase and turn the rest to lowercase.

      • Remove

        • Punctuation – Remove selected types of punctuation in a string column.

        • NULLs – Add a filter to remove all rows with the NULL value in a column.

        • Blanks – Add a filter to remove all blank rows in a string column.

        • Zero values – Add a filter to remove all rows with zero value in a numeric column.

        • Negative values – Add a filter to remove all rows with a negative value in a numeric column.

      • Replace

        • NULLs – Specify a value to replace all NULL values in a column.

        • Value – Specify a new value to replace a specified old value in a column.

      • Trim

        • Leading & Trailing spaces – Remove spaces at the beginning and at the end of the string in a column.

        • Leading spaces – Remove spaces at the beginning of the string in a column.

        • Trailing spaces – Remove spaces at the end of the string in a column.

        • All spaces – Remove all spaces in a column.

        • Leading character – Remove the specified character at the beginning of the string, in a column.

        • Trailing character – Remove the specified character at the end of the string, in a column.

      • Concatenate – Join two strings into one, with or without a separator in between.

    • Readd – Click the arrow next to the name of the data source. Then, select the column that you want to readd.

    • Sort alphabetically – Point to the column, and then click sort column.

    • Add new column – (Server data sources only) Click the arrow next to the name of the data source. Then, click the Add column and select the columns in the Add column dialog.

    For each data source, check if the data roles are identified correctly. To adjust them, see Change the data role.

    Check the aggregation type for the columns. Adjust the aggregation if needed. The aggregation type that is defined at the time of a dataset creation is a default type for that measure in a visualization.

    For details, see Change the data aggregation.

    You can select a specific format for a numeric column. Point to a column, click column options, and then click Format.

    In the Column format dialog, select the format type to apply, customize its options, and click OK.

    You can customize the following format options:

    Decimal places

    Select the number of decimal places to display in the number. For example, depending on the decimal places, a number may look like 3.14 or 3.14159.

    Decimal separator

    Select the separator to show for decimals: Comma (,), Period (.), Space (), or Custom to provide another separator symbol.

    Thousands separator

    Select which separator to show every thousand, for example, 10,000 or 10.000.
    You can select Comma (,), Period (.), Space (), or Custom to provide another separator symbol. To show the number without any separator, select Custom and leave the field empty.

    Negative values

    Select how to display negative values: -1234, (1234), or 1234-.

    Display units

    Select a specific unit to abbreviate the numbers: None, Auto, Thousands (K), Millions (M), Billions (B), Billions (G), and Trillions (T).
    For example, if the number is 47,000 and the display units are Thousands (K), the number is shown as 47K.

    Prefix

    Specify characters that precede each displayed number.

    Suffix

    Specify characters that follow each displayed number.

    You can customize the following format options:

    Decimal places

    Select the number of decimal places to display in the number. For example, depending on the decimal places, a number may look like 3.14 or 3.14159.

    Decimal separator

    Select the separator to show for decimals: Comma (,), Period (.), Space (), or Custom to provide another separator symbol.

    Thousands separator

    Select which separator to show every thousand, for example, 10,000 or 10.000.
    You can select Comma (,), Period (.), Space (), or Custom to provide another separator symbol. To show the number without any separator, select Custom and leave the field empty.

    Negative values

    Select how to display negative values: -1234, (1234), or 1234-.

    Display units

    Select a specific unit to abbreviate the numbers: None, Auto, Thousands (K), Millions (M), Billions (B), Billions (G), and Trillions (T).
    For example, if the number is 47,000 and the display units are Thousands (K), the number is shown as 47K.

    Currency

    Select a currency from the list or specify the custom currency.

    Display currency

    Select how to display currency: as a symbol ($) or as an international symbol (USD).

    Prefix

    Specify characters that precede each displayed number.

    Suffix

    Specify characters that follow each displayed number.

    You can customize the following format options:

    Decimal places

    Select the number of decimal places to display in the number. For example, depending on the decimal places, a number may look like 3.14 or 3.14159.

    Multiply by 100

    Select whether to multiply the number representing a percentage. For example, the value of 0.05 will be formatted as 5%.

    You can select a specific format for a date column. Point to a column, click column options, and then click Format.

    In the Column format dialog, select a format that you need, and click OK.

  4. If you added another data source, you need to join them.

    Click Next to go to the Step 3 – Join and preview page.

  5. (Extract Creators only) By default, new datasets use a live connection. You may choose to create an extract. For details, see Extract data for a dataset.

  6. Click Finish.

    Click OK in the notification message.

    The dataset is created.

    Note: If you established a connection to the server when creating this dataset, the data connection is saved to the Data connections pane.

On this page, you can review and adjust the relationships (joins) between tables, add or remove columns, preview the data source column data, and save your dataset.

  1. Check the join types added between the tables. To adjust the joins, see Define joins between data sources.

  2. If needed, you can manage data sources and columns in this step too. For details, see Manage data sources and columns in a dataset (Step 3).

  3. (Optional) Click Preview data to preview the dataset. You can resize the Data preview pane by dragging the pane header.

    You can also manage the columns from the preview table.

  4. If needed, change the name of your dataset.

  5. (Extract Creators only) By default, new datasets use a live connection. You may choose to create an extract. For details, see Extract data for a dataset.

  6. Click Finish.

    Click OK in the notification message.

    The dataset is created.

    If you established a connection to a server when creating this dataset, the data connection is saved to the Data connections pane.

Note: You can return to the dataset and edit columns at any time later. For details, see Edit dataset.