How to create Time Dimension in Azure Data Factory

Question

I want to create a time fact table on azure data factory with dates ranging from 01/01/2010 to now Thank you

Answer 1

Sources below:
CTAS: create Date dimension table as SELECT;
LINKED SERVICE: Link to a table or service (SaaS TimeTable)
LINKED SERVICE --> INTEGRATION DATASET: Link to a TIMETABLE
PIPELINE --> Utilize another Language or software: --> Example: Workbooks (PYSPARK) , Databricks, Stored PROC, Azure Functions.

• CTAS: with CTAS you use T-SQL To create a DATE DIM TABLE ; this can be tricky as PROC is used; its actually a DBO, and I've not been able to use traditional SQL to do this.
• Linked Service: Research needed but there are SaaS for this. Link to their DateTime Tables; API
• Integration DataSet: Create an Excel sheet of Calendar. It will be a large one for like 30 years; Especially for MINUTE. which could be about a GIG or so. Use integration to put it in synapse.
• Host the Excel sheet in GEN2 Blob; put the calendar in a folder on your DataLake. You will have issues because most synapse will want to "Parquet" this data. But if you have a Single Column Date.Excel sheet; in ISO Format; going by the minute for 1 million rows. is like only 2 years. you would need to create about 30 excel sheets. and DataBrick them later. This would not be practical.
• Use PowerBI: Create a date time table in PBI, Dataset will be clouded. Link the DataSet in synapse. There is a 3PL called 'DAX Bravo' it creates a complex Date Table by the SQLBI team. I have not had good luck with this and opened several bug on their GIT. (2022). but these may be vetted by now and its worth a look. DateTime Intellegence by PowerBI works for small things but thats why your looking to put a proper date table in synapse.
• Using Azure functions or Stored Procedures: this is not researched on my end; seems promising, requires higher privilege's. SYS and PROC are used for the Calendar Creation.
• Pipeline: also expert experience and cost analysis required; but create a workbook use PySpark, Python.Pandas, to create your Calendar.

https://learn.microsoft.com/en-us/sql/relational-databases/tables/creating-a-system-versioned-temporal-table?view=sql-server-ver16