Best Practice SQL, SSAS, SSRS

SQL

1.      Build partitions based on month

2.      Add non clustered index based on month

3.      Add non clustered index based on month + portioning strategy in cube

SSAS

1.      Ensure all reference dimensions are materialized

2.      Only have measures that are used in the cube

3.      Only have attributes in dimensions that are used

4.      Split partitions into 2-20million sizes

5.      Build usage based aggregations based on most common queries

6.      Do not create dynamic sets in cube [Can build measure in background sql to load]

7.      Ensure many to many are <=1 million rows max [latest period only]

 

SSRS

1.      Build MDX efficiently:

a.      Do not have huge sets, build in SSAS

b.      Remove filters – instead have exists or flag attribute in cube

c.      Remove any measures not required in query

d.      Try to push top count into cube

2.      Remove lookups and build into MDX

3.      Remove unnecessary  parameter datasets not required

4.      Try to merge datasets if possible