INTRODUCTION

Omni, the powerful business intelligence (BI) tool, seamlessly blends data integration and customizable queries, offering an expansive array of choices in an easy-to-use visual analytics interface. This innovative tool tackles data hurdles, empowering both individuals and organizations to unlock the full potential of their data. Not only does Omni deliver the speed and adaptability characteristic of analytics tools, but it also ensures governance through a well-structured data model.

A standout aspect of Omni is its similarity to Looker, supporting a semantic layer and intricate data customization. Moreover, it features an intuitive visualization platform reminiscent of Tableau. In the domain of data visualization, Omni employs a workbook framework, where a single workbook functions as a report and contains multiple sheets known as tiles within the reports.

CONNECTION

Navigate to the Admin section, then select “Add Connection” followed by “New Connection.” From there, opt for the dialect you wish to include.

Every dialect comes with its authentication setup. The Display Name impacts the connection’s name in Omni, which can be modified later. Database Details encompass information regarding the data warehouse.

OMNI DATA MODEL

Upon successful addition of a connection, models are automatically generated. Each model comprises two layers. The initial layer, called the schema model, mirrors the raw database tables, views, dimensions, and measures.

The subsequent layer, known as the shared model, extends the schema model, allowing developers to define data model definitions, modify existing joins and relations, and create new derived dimensions and measures. This shared model reflects all updates and changes made by developers, serving as an extension of the schema model. This includes new fields, relationships, joins, and formatted labels.

The shared data model operates with two layers: the schema model as the first layer and the extension model as the second layer. When the schema undergoes a refresh in the database, Omni updates its field references to align with the current database schema. Any new columns added or removed will be incorporated into the schema model during this update. These changes might trigger invalid references, prompting the model validator to flag them.

To view both the schema model and its extension simultaneously, just switch on the ‘Combined’ toggle.

Topics

The process of linking multiple tables or views and generating an explorer is referred to as “Topics” in Omni. When a valid join is established among tables, it automatically becomes part of a topic.

Omni operates on a tree structure defined by table joins:

• Direct joins exist between ds1_inventory_items and ds1_users with ds1_order_items.
• ds1_products are linked to ds1_inventory_items.
• ds1_distribution_centers connect with ds1_products.
• ds1_users and ds1_distribution_centers necessitate bracket pairs due to the absence of child views.

For aliasing a topic, the base_view syntax can be utilized to assign a different name to the topic.

In the prior example, we utilized base_view to rename the highlighted topic, changing its name from ds1_order_items to order_items_w_users.

Regarding Relationship between Views:

Within the relationship file, Omni, by default, includes all non-fanout tables that are joinable to a topic. These tables establish Many-to-One and One-to-One relationships with other tables. Users can modify Omni’s default relationship code or create user-defined relationships by adding or removing code.

The syntax within the relationship file comprises several components:

1. join_from_view — The left table we defined in the join_from_view argument.
2. join_to_view — The right table we defined in the join_to_view argument.
3. join_to_view_as — To provide an alias name for the joined table. We use join_to_view_as an argument. In this case we will refer to the dimension/measures of that view from the new alias name. For example: Users table needs to join with Orders table with 2 different aliases as buyers and as sellers. In such cases we will use join_to_view_as an argument.
4. join_to_view_as_label — To update the label name in Topics., we use join_to_view_as_label argument.
5. join_type — These are join types. always_left, inner, full_outer, cross
6. on_sql — We define the keys of both tables by which we are joining the tables.
7. relationship_type: one_to_one, many_to_one, one_to_many, Many_to_many, assumed_many_to_one — Omni generates default joins with two tables by the naming convention. For example, if we have an Orders table with Inventory_id and Inventory table with id or inventory_id. Omni will judge these keys and implement the default join as assumed_many_to_one.

9. reversible — The ‘reversible’ attribute within the relationship code enables bi-directional functionality for joins. Typically, a left join with a Many-to-One relationship is recommended. However, when a right join with a One-to-Many relationship is necessary, setting ‘reversible’ to true in the relationship code is required. This configuration facilitates dataset fan-out in such instances.

It’s important to note that One-to-One joins are inherently reversible, and Many-to-Many joins are inherently reversible by default.

DIMENSION

A dimension serves as a data row attribute used primarily for segmentation and grouping in queries. It encompasses date/time, string, boolean, or numerical values and accommodates nested dimensions like JSON. Unique names are required, using lowercase letters (a-z), numbers (0–9), or underscores, commencing with a letter. The schema model generates default dimensions for each database column automatically.

Dimensions in the Omni code provided represent specific attributes:

1. Date: Used for time-based analysis.
2. Product Category: Aiding in product categorization.
3. User ID: Identifies individual users.

These dimensions within the Omni code delineate the attributes that shape the rows within the result set.

DIMENSION ARGUMENTS

1. Label: It acts as the dimension’s name, functioning as its identifier within the query. Label will override the field name for all UI appearances of the field

2. Convert_tz: Specifies whether to convert the time zone for date/time dimensions.

3. Description: Offers a textual description or explanation of the dimension’s purpose or content. Metadata about the field, made available in the workbook UI. Omni expects unquoted text

4. display_order: Determines the order in which dimensions are displayed in the UI. This will override the sort order for the field picker, inside the field’s grouping

5. Format: Defines the presentation format for the dimension’s data. Sets default formatting for numbers in Omni. Each format is two decimal places by default, except id, which does not include decimals

To set decimal length explicitly on a format, simply add {number_of_digits}, up to 4 digits

Examples: format: number_4, format: big_1, format: usdaccounting_0

6. Group_label: Assigns a label to group related dimensions in the user interface. this will nest a group of fields in the field picker for curated

7. Hidden: Determines whether the dimension is hidden from the query results by default.

8. Ignored: Indicates whether the dimension should be ignored in certain contexts or calculations.

9. Links: Provides links or references to additional information associated with the dimension.

10. Order_by_field: Specifies a field for ordering the results when the dimension is used in queries.

11. Primary_key: Identifies whether the dimension serves as a primary key, uniquely identifying rows.

12 Sql: Enables the creation of custom SQL expressions or logic tailored to the dimension.

13. View_label: Specifies the label for the view in which the dimension is used.

These dimension parameters offer a spectrum of choices to customize and regulate the behavior, appearance, and utilization of dimensions within the Omni platform. They provide flexibility in shaping the user experience and refining data analysis processes.

MEASURES

Measures describe how to aggregate data in Omni. They can be either:

MEASURES ARGUMENTS

1. Aggregates of dimensions: Like calculating the maximum of profit or the sum of revenue.

Current aggregates offered: count, count_distinct, sum, min, max, average, median

2. display_order: Controls the order in which the measure appears in the field picker.

3. drill_fields: Specifies the fields to be used when drilling down on the measure.

4. Filters: Allows you to filter the data used to calculate the measure. Filtered measures can be built using aggregation alongside a dimension filter. Multiple filters can be added in the filter clause, and multiple values for a given filter can be added using array syntax

VISUALISATION

Omni offers an array of visualization choices to enrich comprehension of data. Ranging from adaptable bar graphs and pie charts to interactive diagrams and mind maps, Omni guarantees a holistic visual depiction. Whether assessing trends, relationships, or hierarchies, its diverse visualization tools enable tailored data presentations, catering to unique requirements and augmenting understanding.

Visualizations

1. X-Axis: Map a field to the x-axis.

Additional configuration options:

• Title — change title of x-axis
• Data type — nominal, quantitative, ordinal, temporal

2. Y-Axis: Map a field to the Y-axis.

Additional configuration options:

• Title — change title of x-axis
• Data type — nominal, quantitative, ordinal, temporal

3. Color: Omni’s color tools provide adaptable options:

• Select from predefined or personalized color palettes for visual consistency and significance.
• Allocate colors to distinct data categories, amplifying comprehension.
• Utilize color gradients to depict data variations, particularly in heatmaps.
• Highlight specific data points or trends by employing unique colors.
• Fine-tune color transparency to unveil overlaps or accentuate specific elements.

4. Size: Size by a field. Most common mapping is a measure. Select Auto Size option to remove the series from this channel.

Additional configuration options:

Title — change title of size-by legend

Group / Stack / Stack Percentage

The illustration showcases a grouped chart, exhibiting the x-y axis arrangement in a grouped format.

The above illustration depicts a stacked chart, showcasing the configuration of the x-y axis in a stacked fashion.

The above diagram illustrates a stacked percentage chart, presenting the x-y axis configuration in a percentage-based manner.

Tooltips manage how fields and values appear in the pop-over when hovering over a data point. You have control over the visibility of specific fields by toggling them on or off within the Tooltip section.

Different Type of Charts

Table: The table width is adjustable, spanning either the full page width or condensed to match the largest data cell.

Summary Value: Customization allows selection from any series in your query for individual values. When using a single-value selection, it picks the top row of the specified column, or the leftmost position if the data is pivoted. The ‘Summary label’ feature facilitates label adjustments.

Vega: Omni offers pre-built visualizations with common types and settings. However, substantial customization is possible via VegaLite. Through the Custom Visualization Editor, you can tailor visualizations to your preferences, exploring detailed VegaLite specifications and examples for guidance.

CUSTOM VISUALIZATION

1. Custom with Table: In this, we select Field — Add calculation — Create column with filter

2. Custom with Field

3. Custom with SQL

Edited the SQL code with DISTINCT so that we can observe the results.

Omni Table Calculations

Table calculations in Omni Analytics are a unique form of calculated fields that compute localized data. These calculations operate solely on the current visualization and do not account for any measures or dimensions filtered out of the visualization.

STEPS TO CREATE TABLE CALCULATIONS

Within Omni, upon accessing a Topic, you may encounter one or multiple Explores associated with each topic. Upon selecting an Explore, you can choose the Fields you wish to visualize.

For every field selection, Omni will Generate SQL Query ( As seen Below Img)

In the image above, I’ve chosen Orders Status, Created Date, and Number of Orders from the views. If I aim to display the day of the year for the order Creation date without creating a field within Omni, I can generate it at the Visual level field by including it in the SQL.

Note: When invoking calculations in SQL, functions will start with an OMNI_ prefix and can refer to fields in the query using the ${} syntax (as shown below). It’s essential to execute the SQL (located at the top right corner) once any custom field is created.

As observed in the preceding image, without generating a new field in the Explore, it’s possible to display the day of the year in the visual through table calculations.

More table Calculation Features in omni: –

1. OMNI_RUNNING_TOTAL

2. OMNI_PERCENT_OF_PREVIOUS

3. OMNI_RANK: This Function will provide the rank based on value from Highest to Lowest.

4. OMNI_OFFSET: This function will find a corresponding cell down a column or across pivot.

CONCLUSION

In summary, the Omni Tool stands out as a versatile, comprehensive solution equipped with a wide array of features that enhance efficiency and productivity across diverse fields. Its adaptability and user-friendly interface cater to both enthusiasts and professionals, consolidating various applications and simplifying complex processes within a single platform. As technology evolves, the Omni Tool remains an essential and dynamic resource, delivering users the convenience of a unified solution for all their requirements.

Curious and would like to hear more about this article ?

Contact us at [email protected] or Book time with me to organize a 100%-free, no-obligation call

Follow us on LinkedIn for more interesting updates!!