3

I have a Table with a HASH on a Employee_Key, it also Hold a Date_From and Date_To.

To build the facts I need to change it on a by_date column.

But what is the best practice in Azure DataWarehousing with the less DataMoving as possible.

I've tried with a INNER JOIN to a Dates table that was in ROUND_ROBIN, or tried to put all the dates in a View (with a trick that only sys.objects is called), .. All working but ... not sure what is the better way on performance due to the Data-Movements between the 60 nodes ...

Any suggestions ?

Here some parts of the tables ...

CREATE TABLE [edw_hr_temp].[DIM_EMPLOYEE]
(
    [EMP_UID] [BIGINT] NULL,
    [EMP_BK] [CHAR](8) NULL,
    [EMP_DATE_FROM] [DATE] NULL,
    [EMP_DATE_TO] [DATE] NULL,
)
WITH
(
    DISTRIBUTION = HASH ( [EMP_BK] ),
    CLUSTERED COLUMNSTORE INDEX
)

1st Option a Real Table for Dates

CREATE TABLE [bi_msft].[ALL_DATES]
(
    [ADATE] [DATE] NULL
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN,
    CLUSTERED COLUMNSTORE INDEX
)

2nd option is in the Stored Procedure user this as a subquery of store it in a #tempTable

  SELECT d
    FROM (SELECT d = DATEADD(DAY, rn - 1, @FROM_DATE)
            FROM (SELECT TOP (DATEDIFF(DAY, @FROM_DATE, @TO_DATE)) 
                         rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
                    FROM sys.all_objects AS s1
                         CROSS JOIN sys.all_objects AS s2
                   ORDER BY s1.[object_id]
                 ) AS x
         ) AS y;

So basically my question is, when using the 2nd variant, would this query run on each node and so no data movement needed of will it run on one node and will all be moved....

  • Joins involving ROUND_ROBIN tables always incur data movement. Otherwise, sorry, it's not really clear what you are asking. Can you give some table definitions, sample data and expected results? – wBob Apr 11 '17 at 9:39
  • Added code and hopefully clarified the question ... Its a pure performance issue. This is a simple case, but in our environment we would several of these constructions ... – Harry Leboeuf Apr 11 '17 at 10:52
2

For Azure SQL Data Warehouse, unless you have have millions of records, then it's unlikely you will really benefit from clustered columnstore indexes. In fact this could even be detrimental to performance. For smaller volume tables, you would probably be better off with either heap (for fast load) or clustered index. ROUND_ROBIN will at least ensure even distribution. If you do plan to hash distribute your table, ensure your chosen hash column will give even distribution, ie has a large number of distinct values. What is EMP_BK for example?

I would generally recommend having a table to store dates, rather than some trick construct. The table is likely to scale better. As the datatype is date, it again seems unlikely there will be enough rows in this table to justify clustered columnstore. Try this with clustered index instead.

Have a read through the 'Hash distribute large tables' section in this article:

https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-best-practices

Just as an observation, all columns in your tables are nullable, which would be bad for a dimensional model (you cannot build cubes on NULLs) and generally does not represent good data modelling.

If you have a genuine performance problem, consider posting an EXPLAIN plan and give an idea of the number of rows in your table and the type of data. As mentioned, for your larger tables, to avoid data movement, hash distribute your tables on the same column.

  • 1
    At this moment everything is still in POC, but finally facts tables will have billions of rows. I wast just wondering for these special dimensions that don't have a lot of rows, such like a date dimension what would be the better. This trick could have been handy if a select on a sys.table is done on each node, in that case there would be no moving for such a table. EMP_BK was the business_key for the field. – Harry Leboeuf Apr 11 '17 at 17:04
0

Just FYI - replicated tables are now available to you, so this will probalby be your best bet for smaller dimension tables like these.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.