0

I am working on migrating the SQL Server Databases to Azure Data Warehouse. I had dimension tables in earlier DB which had dim ids (basically integer values) and those were referred in fact tables for easy data fetch. These dim ids were generated using identity columns.

Now, in Azure Data Warehouse, the identity column values are randomly generated and I am not sure whether to use these dim ids here. If these ids are not used then text columns can be used, but that will increase the seek time in turn hitting the performance.

So, can anyone suggest how I should handle these dim ids in Azure Data Warehouse?

Regards,

Pratik

Pratik Somaiya
  • 695
  • 5
  • 18

1 Answers1

3

You're fine using identity columns in Azure SQL Data Warehouse. Those values are not "random", they're just not sequenced the same way that you'd see on an SMP version of SQL Server.

https://learn.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-identity

If you'd prefer an alternative, you can use a calculation like this to assign a surrogate key during row insertion:

cast((select max(dim_sk) from dw.dim_xxx) + row_number() over (order by getdate()) as bigint) as dim_sk
Ron Dunn
  • 2,971
  • 20
  • 27
  • Thanks for your reply. Does it follows the star schema method. I want to link the dim and fact tables using identity columns as foreign keys. – Pratik Somaiya Nov 26 '18 at 05:24
  • 1
    Yes and no. You can join tables and index columns to support your outcome, but there is no referential integrity ... no direct support for primary key / foreign key concepts such as deleting rows or nulling values. You don't want that overhead in a very large DW. – Ron Dunn Nov 26 '18 at 10:30
  • Yes, DW doesn't have primary and foreign key concepts. What do you recommend us to use to connect dim and fact table. Ids or a text column? – Pratik Somaiya Nov 26 '18 at 10:43
  • 1
    Assuming that you are using ASDW because you have a large volume of data, BIGINT is probably your best data type for IDENTITY columns. You'll notice that the alternative algorithm is also cast to a BIGINT. Integer values, in general, are the most compressible and fastest comparable data types, which mean you get best performance from your DW. – Ron Dunn Nov 26 '18 at 13:01