Multiple joins vs. 1 lookup table (alternatives)

Transact-SQL
1

Hi,
In a DataMart analysis for a CRM system, about 10 Dim tables are joined with FactTable to see if the account is also in the table (inner join). This takes a long time (even with an index on KeyCol) for large amounts of data (1 million rows per DIM). If it were not better, I would prepare a table with 10 columns to look up in this table by lookup or join, if the account exists.
Is the idea good - or are there better solutions?
Thanks
Regards
Nicole

2

Are you attempting return all the data in the DM? Try returning aggregations for one period. These systems are used to analyze the data to answer a business questions. Returning millions of rows from any database will take time. Does this system perform well when you return aggregate results?