What are best practices to build a data model in TimeXtender?

Dear @thagin ,

This is not an easy question: this is asking people to write down all our work experiances. Which, to be honest, would be a great idea.

Just on top of my head:

As TX is a blank slate when you start working with it, sticking to your Way of Working is very important. Discipline and perseverance within your WoW is very important. Some of my best practises are:

• Use TX (every version) as the ETL / DWH tool it is. Set is up in that way as well. I keep using my ODX (E), DSA (T) and my MDW (L) layers
• Set up the DSA and MDW as if you where in the cloud (even if you are not). So 1 database and seperate the layers with schemas (DSA and MDW schema)
• To make sure you can find your tables in SSSMS: Create two more schema's (zzETL_DSA, zzETL_MDW) and set these as default schema in the schema behavior. Then set the DSA and MDW schema's as valid schema's. Now your ‘view’ in SSMS is nice and clean
• Stay as far away as possible from custom stuff. When you need to create a view, stored procedure or custom table insert you should, but only if it cant be done in TX ‘Native’
• Keep you end goal in mind: Creating STAR schema's. Building a ‘modular’ DWH is in my opinion still the best way. It is fast, it is clear. So don't go joining complete tables if you dont have to. Split you Facts and dimensions even though the table in the DSA contains both fields
• 100% referential integrity: The dimension tables create the keys for the models. I like to use supernatural keys for the generation of keys and use lookups to get the keys in my fact tables. I have a SQL snippet to make the key -1 wheen the key is empty. in the dimension tables I add a -1 custom data record with '’ Empty’ so each fact record has a dimenstion record even though it is my dummy record. but now I can easyly report on all these facts without dimensions.
• Only transformations and translations in the DSA. So you know where to find your transformations and translations and all of them are in one spot.
• Rename the tables and columns as the business would like to see them in the dashbaord/report.  So you dont have to go an rename each time you create a semantic layer endpoint and it make
• Dont rename records in views / Sp's or custom inserts. This saves a lot of time mapping the columns and tables. Now you can use the automapping function. Rename when you materialize the table.

And read the datawarehouseing toolkit by Ralph Kimball e.o.!

Hope this helps

= Daniel

Use the description feature on an object when using anything custom like pre/post scripts, they can easily be forgotten

Your question satisfies my gut feel of: “the shorter the question, the more elaborate a good answer needs to be” :-)

Clarity is key, only optimise if required. A clear implementation doesn't waste anyone's time when they try to understand what is going on. It is better to be consistently “wrong” than have different approaches scattered around.

Don't be afraid to process the same data twice if you have differing groups of users with different requirements. It is easier to have and maintain FACT_Orders_Sales and  FACT_Orders_Finance than deal with conflicting requirements and develop yourself into a corner.

There's probably a million other things, but I am too young to be writing my memoirs yet...