Versions Compared

Version Old Version 1 New Version Current
Changes made by

Sarabjeet Kaur

Sarabjeet Kaur

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

With the Quick Data Modeling feature, you can connect to the data source and select the data to register; Kyvos automatically validates your registered data and creates relationships and semantic model design, thereby eliminating the effort and time needed for designing the OLAP model.

On the Quick Data Modeling page, you can select the semantic model type as:

  • Multidimensional Semantic Model: Choose this option if you work with large datasets stored in multiple tables. This option creates a dataset, establishes relationships, and processes the semantic models.

  • Single Table Semantic Model: Choose this option to use semantic model in a tabular way (single table). This option creates a dataset and generates a semantic model. You can also define Semantic Model view type as flat or smart.

    • Flat: The semantic model will be designed as a flat table, where all source data columns are included as attributes within a single dimension.

    • Smart: The system will intelligently identify measures and dimensions and create a more business-friendly semantic model view. This model will organize source data fields into measures, attributes, and hierarchies based on their data types.

...

Using Multidimensional Semantic Model
Anchor
Multidimensional
Multidimensional

Let us consider a manufacturing use case for analyzing publically publicly available SSB data.

  1. On the Kyvos home page, click Quick Data Modeling from the top-right of the home page.

  2. On the displayed wizard, provide an Analysis Name. Kyvos will create all the folders, relationships, and semantic model with this name. In this example, we have named it Kyvos.

  3. Click Start.

    Image Removed

  4. On the displayed Create Dataset tab, select your data Connection from the Datasource column.

  5. To modify an existing connection according to your environment, select it from the list and click the Edit link. 

...

Aura tab collection
paramsJTdCJTIyZ2VuZXJhbCUyMiUzQSU3QiUyMnRhYlNwYWNpbmclMjIlM0EwJTJDJTIydGFiV2lkdGglMjIlM0ExMDAlMkMlMjJ0YWJIZWlnaHQlMjIlM0E1MCUyQyUyMmRpcmVjdGlvbiUyMiUzQSUyMmhvcml6b250YWwlMjIlN0QlMkMlMjJjb250ZW50JTIyJTNBJTdCJTIyYmFja2dyb3VuZENvbG9yJTIyJTNBJTdCJTIyY29sb3IlMjIlM0ElMjIlMjNmZmYlMjIlN0QlMkMlMjJib3JkZXIlMjIlM0ElN0IlMjJzdHlsZSUyMiUzQSUyMnNvbGlkJTIyJTJDJTIyd2lkdGglMjIlM0ExJTJDJTIydG9wJTIyJTNBdHJ1ZSUyQyUyMmJvdHRvbSUyMiUzQXRydWUlMkMlMjJsZWZ0JTIyJTNBdHJ1ZSUyQyUyMnJpZ2h0JTIyJTNBdHJ1ZSUyQyUyMmNvbG9yJTIyJTNBJTdCJTIybGlnaHQlMjIlM0ElMjIlMjNjY2NlY2YlMjIlN0QlN0QlMkMlMjJwYWRkaW5nJTIyJTNBJTdCJTIydG9wJTIyJTNBMTAlMkMlMjJyaWdodCUyMiUzQTEwJTJDJTIyYm90dG9tJTIyJTNBMTAlMkMlMjJsZWZ0JTIyJTNBMTAlN0QlN0QlMkMlMjJhY3RpdmUlMjIlM0ElN0IlMjJiYWNrZ3JvdW5kQ29sb3IlMjIlM0ElN0IlMjJjb2xvciUyMiUzQSU3QiUyMmxpZ2h0JTIyJTNBJTIyJTIzZjU4MjI3JTIyJTdEJTdEJTJDJTIydGV4dCUyMiUzQSU3QiUyMmZvbnRTaXplJTIyJTNBMTYlMkMlMjJjb2xvciUyMiUzQSU3QiUyMmxpZ2h0JTIyJTNBJTIyJTIzMDAwMDAwJTIyJTdEJTJDJTIydGV4dEFsaWduJTIyJTNBJTIybGVmdCUyMiUyQyUyMmZvbnRXZWlnaHQlMjIlM0ElMjJib2xkJTIyJTdEJTdEJTJDJTIyaG92ZXIlMjIlM0ElN0IlMjJiYWNrZ3JvdW5kQ29sb3IlMjIlM0ElN0IlMjJjb2xvciUyMiUzQSUyMiUyM2RmZTFlNiUyMiU3RCUyQyUyMnRleHQlMjIlM0ElN0IlMjJmb250U2l6ZSUyMiUzQTE4JTJDJTIyY29sb3IlMjIlM0ElMjIlMjM1ZTZjODQlMjIlMkMlMjJ0ZXh0QWxpZ24lMjIlM0ElMjJsZWZ0JTIyJTJDJTIyZm9udFdlaWdodCUyMiUzQSUyMmxpZ2h0ZXIlMjIlN0QlN0QlMkMlMjJpbmFjdGl2ZSUyMiUzQSU3QiUyMmJhY2tncm91bmRDb2xvciUyMiUzQSU3QiUyMmNvbG9yJTIyJTNBJTIyJTIzZjRmNWY3JTIyJTdEJTJDJTIydGV4dCUyMiUzQSU3QiUyMmZvbnRTaXplJTIyJTNBMTYlMkMlMjJjb2xvciUyMiUzQSUyMiUyMzVlNmM4NCUyMiUyQyUyMnRleHRBbGlnbiUyMiUzQSUyMmxlZnQlMjIlMkMlMjJmb250V2VpZ2h0JTIyJTNBJTIybGlnaHRlciUyMiU3RCUyQyUyMmJvcmRlciUyMiUzQSU3QiUyMnRvcCUyMiUzQXRydWUlMkMlMjJsZWZ0JTIyJTNBdHJ1ZSUyQyUyMnJpZ2h0JTIyJTNBdHJ1ZSUyQyUyMmJvdHRvbSUyMiUzQXRydWUlMkMlMjJ3aWR0aCUyMiUzQTElMkMlMjJzdHlsZSUyMiUzQSUyMnNvbGlkJTIyJTJDJTIyY29sb3IlMjIlM0ElN0IlMjJsaWdodCUyMiUzQSUyMiUyM2NjY2VjZiUyMiU3RCU3RCU3RCU3RA==
Aura tab
summaryFile
paramsJTdCJTIydGl0bGUlMjIlM0ElMjJGaWxlJTIwJTIyJTdE

  1. Click the Register data using file link.

  2. On the Add Dataset dialog, provide a Name for the file.

  3. For AWS-based deployments, select the name of your S3 Bucket from the list.

  4. Select the file. Click the Actions menu (...) to navigate to the file.

  5. Specify the File Type such as Character Separated Values, Parquet, or Sequence.

  6. Specify the line separator, field separator, escape characters, enclosed by, skip top lines, and encoding type options.
    See the Created dataset settings table section for details.

  7. For Compression, select None or LZO.

  8. If the data includes header information, select Contains Header Row so that the column headings will not be included as part of the data.

  9. Select Ignore Empty Rows to have empty rows ignored.

  10. Select Key as Column to set the field name to be the column description or select File name as column to set the file name to the column description.

  11. If the source data contains primary key or foreign key, the system automatically marks it.

  12. To use this file for lookup purposes, select the Lookup file checkbox. See Using lookupLookup to learn more.

  13. Click Preview to preview your data.

  14. Click Add to add the file.

    image-20240226-094755.png
Aura tab
summaryTable
paramsJTdCJTIydGl0bGUlMjIlM0ElMjJUYWJsZSUyMiU3RA==

  1. From the Database drop-down list, select the data source. The databases available in the data source are listed. 

  2. Click the Choose tables as datasource link. The tables available in the database are listed. 

  3. Click the Add Table link corresponding to the table that you want to add to your analysis.
    For example, we have selected the ssb database and corresponding tables.

    image-20240226-095433.png

  4. You can mark tables as Dimensions or Facts also.

  5. You can also use Filters to filter data and columns from your tables.

  6. From the Added table list, click the table and the corresponding column name to define Column Properties.

  7. You can Hide columns, select Primary Key and Foreign Key, change Field name, and define the format for data here.
    If the source data contains primary key or foreign key, the system automatically marks it.

  8. Optionally, you can use the incremental identifier checkbox to specify the column you want to use to identify and process new data during an incremental build. The data should be sorted on this column or a date column.

    image-20240226-095633.png
Aura tab
summarySQL
paramsJTdCJTIydGl0bGUlMjIlM0ElMjJTUUwlMjIlN0Q=

  1. Click the Register data using SQL query link.

  2. On the SQL editor, enter your SQL query and click Preview to verify your data.

    image-20240226-101643.png

  3. As you enter text, suggested statements, functions, operators, and so on, are displayed. Click the item to auto-fill it.

    1. Use the Undo and Redo icons as needed as you create your calculated expression.

    2. To find or replace content, click the Find/Replace icon, and enter what to find.

    3. Select Match Case if needed.

    4. If you want to replace the found value, enter the text to replace and specify whether to replace all.

    5. To delete the entry for that item, click the X at the end of the line.

    6. Click the Next button. The system validates all your files and tables and displays errors if your data is invalid.

    7. The Define Relationships tab page is displayed with a set of relationships automatically created by the system.
      These relationships are created on the basis of data selected in the previous step and information about master data, fact data, primary key, and foreign key.

    8. If the system does not generate any relationships, or to define your custom relationships, you can manually define relationships. For this, click the Add Relationship link.

Tip

TipsTip

  • Mark at least one fact table. This will help in the auto-creation of relationships and semantic model. You should mark that file as Fact, which you want to use as a fact table in relationships.

  • Mark the primary key and foreign key. This will help in the auto-creation of relationships and semantic model in the next stage. Dimensions/measures will not be created for primary/foreign keys.

  • Hide columns that you do not need in your analysis.

  • Update format if needed.

  1. Choose the Preview Table icon image-20240226-102409.png to see sample data , and click Filter data to refine the data that you want to bring in for your analysis.

  2. Click the Next button. The system validates all your files and tables and displays errors if your data is invalid.

  3. The Define Relationships tab page is displayed with a set of relationships automatically created by the system.
    These relationships are created on the basis of data selected in the previous step and information about master data, fact data, primary key, and foreign key.

  4. If the system does not generate any relationships or to define your custom relationships, you can manually define relationships. For this, click the Add Relationship link.

Tip

TipsTip

  • Create a relationship from fact to dimension by keeping fact on the left side of the relationship.

  • Each table should participate in at least one relationship. In case the table does not have any relations, it will not be used in auto-semantic model creation in the next stage.

  • It is recommended to mark the node that you want to use as fact as a fact table using right-click options.

  1. Click Apply.

  2. The system validates relationships for correctness, and if everything is valid, and generates a semantic model design. The validation status is displayed at the bottom of the screen.

  3. The Design semantic model tab displays the dimensions and measures created by the system.
    By default,  the  the name of the datasource data source table from which the first measure is created in the measure group is taken as the  measure the measure group name. However, if you have created a measure group with a custom name, then dragging a measure in it would not impact the name of the measure group.

Panel
panelIconIdatlassian-note
panelIcon:note:
bgColor#DEEBFF

Note
At any stage in the workflow, if you are not able to complete the workflow because of either the complexity of the use case or if you need some advanced options, which are not available in this interface, you can quit the workflow and can go directly to the standard interface to continue  your continue your work. All the entities created up to the previous steps will be available for users to continue working on the next stage  following following the Designer Guide.

For example, if you have created the relationship design, but quit at semantic model design, then your work is done till the relationship design is saved.

  1. You can further modify the design to add dimensions, measures, define dimensions and measure properties, and so on. Refer to Working with Semantic Models to know more.

  2. Click the Next button.

  3. The Review and Process tab shows the entities designed till now.

  4. Here, you can choose process semantic model to create workbooks or connect semantic model to BI tools. Choose any of the following options:

    • I want to execute the test process now: Select this option to run the test build and click Process Now.
      You can further choose:

      1. Job Connection: Select a connection for job execution.

      2. Input Data: Here, you can

    select

      1. specify the number of

    records or

      1. fact data partitions to be

    processed in

      1. used for the test

    build

      1. data process.

    Minimal Aggregation: Select the checkbox to minimize aggregations. Aggregates are precalculated summaries that improve query response time. However, semantic model size increases with aggregation

      1. Process: Select Metadata and Data or Metadata only. This is applicable only when the semantic model is processed with Kyvos Compute.

        • Metadata and Data: Selecting this option will populate both the dimension metadata cache and data (Kyvos Analytical Store). Queries will be served from Kyvos Analytical Store.

        • Metadata Only: The system will populate only dimension metadata cache. Your queries will be submitted directly to the underlying data source.

      2. Keep: Click one of the following options tokeep the Aggregated data or Raw Data. This is applicable only when the semantic model is processed with Kyvos compute and raw data querying is enabled. Additionally, the Both option must be selected when you schedule Process Full Data for a semantic model.

      3. Aggregated Data: The system will keep the aggregated data (and update it in subsequent semantic model processing as per the query patterns) in Kyvos Analytical store. This data will be used to serve the aggregates queries.

      4. Raw Data: The system will keep the raw data in Kyvos Analytical store. This data will be used to serve those queries for which smart aggregates are not available in the Kyvos Analytical store.

      5. Both: The system will keep aggregated and raw data both.

    • I want to execute the build later: Select this option if you want to review your design and entities before proceeding with the build. In this case, your design is saved, and you can access the semantic model from Toolbox at any time.

  5. Click Process Now.

  6. Once you have launched the test builddata process data, click View Build Job Progress to see the build job status.
    The system shows the build job status in the semantic model designer, as shown in the following figure.

    At the successful completion of your test builddata process, you can start creating visualization and then generate recommendations to further optimize your design.

Using Single Table Semantic Model
Anchor
singlesemanticmodel
singlesemanticmodel

You can now quickly process the semantic model with minimal input, enabling you to generate a semantic design by simply selecting the required table.

To use single semantic model, perform the following steps.

  1. On the Kyvos home page, click Quick Data Modeling from the top-right of the home page.

    image-20241205-114651.pngImage Added

  2. Select semantic model type as Flat or Smart.

  3. On the displayed wizard, provide an Analysis Name. Kyvos will create all the folders, relationships, and semantic model with this name. In this example, we have named it Kyvos.

  4. Click Start.

  5. On the displayed Create Dataset tab, select your data Connections from the Datasource column.

    image-20241205-114846.pngImage Added

  6. By default, the Table is selected as Input Type.

  7. From the Database drop-down list, select the data source. The databases available in the data source are listed. 

  8. Click the Choose tables as datasource link. The tables available in the database are listed. 

    image-20241205-115339.pngImage Added

  9. Click Next, and the system has automatically created dimensions and measures. Kyvos recommends reviewing the semantic model design before moving forward.

  10. In the Properties section, click Conversational Analytics Metadata link. Additionally, you hide or show metadata summary.

  11. On the displayed Design Semantic Model tab, by default, Raw Data Querying is enabled, and the System Default option is selected for Raw Data SQL Connection to select the default SQL connection for raw data querying.

  12. Click Review.

    image-20241205-115718.pngImage Added

  13. On the displayed Review tab, click the datasets or semantic models link to open in a new window.

    image-20241205-124133.pngImage Added

  14. Here, you can process semantic model to create workbooks or connect semantic model to BI tools. Choose any of the following options:

    • I want to execute the test process now: Select this option to run the test build and click Process Now.
      You can further choose:

      1. Job Connection: Select a connection for job execution.

      2. Input Data: Here, you can specify the number of fact data partitions to be used for the test data process.

      3. Process: Select Metadata and Data or Metadata only. This is applicable only when the semantic model is processed with Kyvos Compute.

        • Metadata and Data: Selecting this option will populate both the dimension metadata cache and data (Kyvos Analytical Store). Queries will be served from Kyvos Analytical Store.

        • Metadata Only: The system will populate only dimension metadata cache. Your queries will be submitted directly to the underlying data source.

      4. Keep: Click one of the following options tokeep the Aggregated data or Raw Data. This is applicable only when the semantic model is processed with Kyvos compute and raw data querying is enabled. Additionally, the Both option must be selected when you schedule Process Full Data for a semantic model.

      5. Aggregated Data: The system will keep the aggregated data (and update it in subsequent semantic model processing as per the query patterns) in Kyvos Analytical store. This data will be used to serve the aggregates queries.

      6. Raw Data: The system will keep the raw data in Kyvos Analytical store. This data will be used to serve those queries for which smart aggregates are not available in the Kyvos Analytical store.

      7. Both: The system will keep aggregated and raw data both.

    • I want to execute the build later: Select this option if you want to review your design and entities before proceeding with the build. In this case, your design is saved, and you can access the semantic model from Toolbox at any time.

  15. Click Done.

  16. Once you have launched the test data process data, click View Job Progress to see the job status.