DAX queries

ttvv

Hello Vertica experts,
we are working on BI project that involves dynamic fact tables creation. Schema is not known in advance and tables are created at runtime.
Beside that, customer will need to be able to extend model.
In SQL Server we are solving this with DAX queries that customer can write. Is there any equal solution in Vertica?
Thanks

LenoyJ

One option is using Flex Tables.

You can create a flex table with or without a schema or real columns.

See: https://vertica.com/docs/10.1.x/HTML/Content/Authoring/FlexTables/FlexTableHandbook.htm

ttvv

Thanks @LenoyJ but maybe I wasn't clear.
We are reading schema at runtime and create table and all columns with standard CREATE TABLE statement. This schema is based on information which modules user selects (behind the scene those are tables and relations) . We use this input to create DWH.
After that, we incrementally load data into those tables from data source with scheduler service.
Customer is also able to adopt tabular model with DAX queries that he enters in the process of creating DWH.
This give us two advantages:
1) We don't have to write additional logic for creating tables
2) And most important, we don't have to load data into those new tables
As far as I understand Flex tables are tables without schema that still needs to be created. In SQL Server we don't have to create and maintenant additional tables, because we are querying and returning data with DAX by table expressions.
Vertica looks very promising, but this could be obstacle for us.
is there an elegant solution to overcome this?
Thanks

Vertica_Curtis

It's not entirely clear to me what the value of a "DAX" query is. It's likely a paradigm used in things like PowerBI that I'm just not familiar with. At it's heart "Evaluate Customers" is synonymous with "Select * with Customers". If you want the results as a table, any select can be written as "CREATE TABLE thing AS SELECT stuff from table", and then you have the necessary table.
Beyond that, it's not clear to me what you're trying to accomplish. Vertica doesn't support the EVALUATE function, so you'll have to use alternative approaches to do this here.

Connyrt-emp1

@ttvv are you using any particular third party tools in your ETL solution or is it a custom application? Vertica integrates with several ETL and BI tools. Each tool has specific capabilities to achieve some of the functionality that you describe like dynamic table and column creation as well as incremental data loading. I am wondering if it is possible to reproduce the functionality using a partner integration and how flexible is your current solution.