Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi experts,
I have a question related to filtering on lookups in cases where you use CDC ETL.
Lets say you have a Replicate task with store changes on Table A, and another replicate task with store changes on Table B.
In Compose, table A is used as main table for an Entity defined and from Table B you want to join (Lookup) some data that you want to use for filtering/removing some of the rows of Table A (that you plan to use as a Fact table in a Data mart). To do this, Table B is added as another Entity in the Model step of Compose and the lookup is then defined in the Data mart fact table.
Am I correct in assuming that Compose will not be able to handle the the following scenarios with CDC ETL:
The only way to update Fact table correctly is to do full rebuild of datamart (but you can do CDC ETL up till DWH level) ?
@bilzekek , Just confirming that,the only way to update the Fact table correctly is to do a full rebuild of Data Mart. However, talking to our internal team, there are a couple of a work-around for this...
Option #1 :
Use the filter column in the fact table. (e.g if you are filtering an order by its shipperName) - you could put shipperName in the fact table.
Option #2:
Yyou could use shipperName in an expression - eg. CASE WHEN ${shipperName} is null then 1 else 0 end. Just by using the $shipperName in an expression, Compose will figure out changes to shippers.
Option#1 would be the simplest way to do this.
If you use the option#2, the case statement can include any filter columns and you get code like below (see highlighted statement)
If you need assistance with this, please open a case with support and we will assist you in implementing this.
Thanks,
Nanda
@bilzekek I believe the only way to update the Fact table correctly is to do a full rebuild of DataMart, but I am checking with the internal team on this. We will get back to you on this.
Thanks,
Nanda
@bilzekek , Just confirming that,the only way to update the Fact table correctly is to do a full rebuild of Data Mart. However, talking to our internal team, there are a couple of a work-around for this...
Option #1 :
Use the filter column in the fact table. (e.g if you are filtering an order by its shipperName) - you could put shipperName in the fact table.
Option #2:
Yyou could use shipperName in an expression - eg. CASE WHEN ${shipperName} is null then 1 else 0 end. Just by using the $shipperName in an expression, Compose will figure out changes to shippers.
Option#1 would be the simplest way to do this.
If you use the option#2, the case statement can include any filter columns and you get code like below (see highlighted statement)
If you need assistance with this, please open a case with support and we will assist you in implementing this.
Thanks,
Nanda
Hi, OK will look into that thanks.