Snowflake or star? Which one is the better approach for designing a project from scratch?

It's a very old question and certainly a very important one before starting a new project from scratch. Though there are lot of factors which must be considered before coming to the final decision, here are some insights on the same

Microstrategy prefers snowflake schema over star. Parent child relationship is one of the most important part of schema design and it's always easier to design when each dimension has it's own lookup table. Though de-normalized dimensions works without any issues and avoids lot of joins, but if you have prompts in your reports then it's always better to stick with snowflake.

Lets take an example- suppose you have a prompt on month. Now in star schema you have date and month in the same table but in snowflake you'll have a month table separately. Now in the month table you have 365 rows and in month table you have 12 only. The prompt basically gets the values by firing a select distinct in the lookup table. You can understand the performance impact of distinct query when the number of rows is more. It may not effect much in this example but when your lookup table has thousands of records then it's huge. Hope this helps

The key specified is the true key for the warehouse table

It's a very common issue when the aggregation just doesn't show up in the query. For example, lets say a report is having one attribute and one metric and the aggregation defined in the metric is "sum". When you view the SQL you see just the fact column with no aggregation. What could be the reason for this?

There is a small trick for this to work. We have a setting in the logical table editor which might be causing the issue. The screen shot below illustrates the setting "The key specified is the true key for the warehouse table" in the logical view of a warehouse table:



This setting is only applicable when the attributes on the template are comprised of all the attributes listed as table keys in the logical view of the fact table. Therefore, when checked, this setting forces the Structured Query Language (SQL) to not aggregate the data and to pull only from the fact tables directly.

you can just uncheck the check box and the issue will be resolved