Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
As part of creating the end-to-end flow of moving data to landing and creating data marts, I am facing issue in creating the DataMart. In the transform section, I am creating 4 sql based datasets which would be my dimension tables. When creating the DataMart, I am getting "Unknown execution error - Numeric value '' is not recognized." The error is being encountered on 2 dimension tables and 1 fact table. Below is the sql that is being used to create the dimension tables.
Dimension Tables:
select ${Product}."ProductID",${Product}."SellStartDate",${Product}."SellEndDate",${ProductModel}."ProductModelID",${ProductModel}."Name"
from ${Product}
join ${ProductModel} on ${Product}."ProductModelID"=${ProductModel}."ProductModelID"
select Product."ProductID",Product."Name", ProductCategory."category_Name",ProductSubcategory."subcategory_Name"
from ${Product} Product
join ${ProductSubcategory} ProductSubcategory on Product."ProductSubcategoryID"=ProductSubcategory."ProductSubcategoryID"
join ${ProductCategory} ProductCategory on ProductCategory."ProductCategoryID"=ProductSubcategory."ProductCategoryID";
A similar dimension table is being created using SQL which is similar to the one above and I am not encountering any issues there. Below is the SQL for the successful dimension table.
select ${Product}."ProductID",${Product}."Name",${Product}."StandardCost",${Product}."DaysToManufacture",${Product}."ProductLine",
${Vendor}."BusinessEntityID",${Vendor}."AccountNumber",${Vendor}."Vendor_Name",${Vendor}."ActiveFlag",${ProductVendor}."AverageLeadTime",${ProductVendor}."StandardPrice",${ProductVendor}."MinOrderQty",
${ProductVendor}."MaxOrderQty" from ${Product}
join ${ProductVendor} on ${Product}."ProductID"=${ProductVendor}."ProductID"
join ${Vendor} on ${Vendor}."BusinessEntityID"=${ProductVendor}."BusinessEntityID";
Pl, suggest on what I am missing and why am I encountering this issue. Thank you!
Hello - are you getting this error when running the Transform, during prepare or another step?
If when running the transform and its a SQL error coming from Snowflake - are all the join columns the same data type? It could be you have a varchar joining to a numeric - hence the 'numeric value is not recognized'. Many cloud DW env's perform implicit conversions when you join columns like this - if one row has a '' (blank) value that could be causing the issue. (As you can see below, I've simulated this by joining an integer column with a string) . If you take the SQL that is failing and try to run it the SELECT manually in your target DW - does it run or fail?
If this is during runtime - you can see the sql statement that is failing by opening the task and navigating to the Monitor view - select the load process (eg. Full load) from the drop down
Select the table with error and then click "Monitor Details"
One additional comment on creation of dimensions -
While you can certainly create dimensions in this manner in Qlik Cloud Data Integration, there is also a model based approach, where you describe the joins using metadata and the Model tab, and let Qlik Cloud Data Integration automatically generated the SQL with the joins you have above. I'll provide an example in a follow-up post for you.
This is important to understand because the data mart object supports automation and the model is key in relating fact tables to dimensions, but also automating denormalization with incremental processing.
Regarding the 'Model-driven' approach - in Storage and Transform tasks, you will see a Model tab.
The Model does NOT impact the target structures created, nor the parallel processing of the data.
It depicts a logical representation of relationships (PK-FK) which can then be used by a Data Mart task to automate data mart processing. Models can be created in Storage or Transform tasks, however currently the task immediately prior to the data mart must contain the data model.
e.g. If you have Landing>Storage>Transform>Mart, the transform must contain the model.
Now lets provide an example of your scenario. I have created mock-ups of a few of the tables in your SQL statements in my env. Navigating to the Model tab provides me a set of Recommended Relationships. These are currently defined based on column name and data type matching.
Selecting the Recommend Relationships gives me a draft of my Model (clicking Apply will Apply the selected relationships).
Selecting Details and highlighting a dataset in the model or the relationship line provides additional metadata (from the dataset screen). For example - in the below, I have highlighted the relationship between Product and ProductSubCategory to see the columns used in the relationship.
If you have relationships to add manually (e.g. column names do not match like BillToCustomerID and CustomerID), you can easily add relationships by the ... menu on each dataset or dragging the dot from the 'child' to the 'parent' .
One the model has been defined in the Transform task, you can create a data mart using the Data Mart Task -
Here I am adding a Mart after the Transform_2 task in my pipeline
Inside the data mart, you can select Add Dimension and configure the tables used in the dimension.
In the below - I have selected Product as the grain of my dimension, provided a name, a dimension Type (Type 1 or Type 2 SCD) and also selected ProductSubCategory and ProductCategory. The Data Mart task will know how to denormalize those tables based on the relationship metadata in the model.
You can repeat this for any number of dimensions.
Navigate to the Dataset tab to curate the dimension - column names, calculated expressions across the tables selected etc.
If you select the ... menu on the dataset - you can now look at the code the data mart task will auto-generate for you to process FULL LOAD of data, as well as incremental.
eg - (you will see the use of CTE's in our generated code - in the event you use the table multiple times with multiple / different join conditions)
Hope this helps as an alternative way to use a metadata driven approach to automated your data mart.
