First installment of best practices for MicroStrategy focusing on data warehouse. Thumb rule of best BI implementation,n not just MicroStrategy, is the optimized for setting up the data warehouse correctly. Here are few from me, few from MicroStrategy tech notes lost in deep. I shall post the best practices for MicroStrategy soon.
1. Data warehouse design should be snowflake schema.
2. Don’t give direct access to tables. Create view and provide access to them. Don’t create custom views.
3. Login/Password used for creation of DNS used to configure the MicroStrategy should be secret. DBA must not tell them to anyone and should be present to input L/P when MSTR Admin is configuring the I-Server.
4. Unique key of table should not be dependent on another other column of same table irrespective of such a column part of unique key to not.
5. Everyone of MicroStrategy team must have individual access to an interface to run SQL queries against the DEV WH with create access.
6. Everyone of MicroStrategy team must know the PK, FK and composite key of each table in OLTP (source) system.
7. All leading and training white spaces in table cells must be cleansed. Using SQL in MSTR (for same) is not a great idea. White spaces decrease SQL speed and it is visible for large warehouses.
8. Only fact tables should be partitioned.
9. To keep the ETL simple, you might want to build your re-normalized structure first, and afterward “generate” the higher level look-ups from the lowest-level look-up table.
Look forward for addition and criticism for above.
Late Edit: Every statement above has a blog post to justify. [:)]