How to create Time Dimension in Azure Data Factory

Consultant1 1 Reputation point
2021-07-13T15:18:55.637+00:00

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

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,525 questions
{count} votes

1 answer

Sort by: Most helpful
  1. L, Christopher 1 Reputation point
    2022-09-21T17:00:43.097+00:00

    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.

    243504-image.png

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

    0 comments No comments