Metaxim's advice is sound. Take it.

I have a similar issue with certain queries although most are exponentially faster than SqlServer. And I have way larger dimensions and facts (facts up to 1 billion rows) so as metaxiom said, it highly depends on how you have it setup and how it aligns with your data.

Sounds like your issue is likely 1) not indexed correctly, 2, not distributed correctly, and 3) need to update stats. And of course like they said DW100c is very small.

For small dimensions I use replicated tables across nodes. For larger dimensions I use clustered indexes on the dimension id. For facts I use clustered columnstores with hash distribution.

Below are 2 queries you need:

1. Check distribution. I included script below
2. Update stats. I included script below

-- Hash distribution problems query

select two_part_name, distribution_policy_name, distribution_column, distribution_id, row_count
from dbo.vTableSizes
where two_part_name in
    (
    select two_part_name
    from dbo.vTableSizes
    where row_count > 0
    group by two_part_name
    having (max(row_count * 1.000) - min(row_count * 1.000))/max(row_count * 1.000) >= .10
    )
    and distribution_column is not null
order by two_part_name, row_count;

DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += 'UPDATE STATISTICS [' + s.name + '].[' + t.name + '] WITH FULLSCAN; ' + CHAR(13)
FROM sys.tables AS t
JOIN sys.schemas AS s ON t.schema_id = s.schema_id
WHERE t.is_ms_shipped = 0; -- Excludes system tables
EXEC sp_executesql @sql;