Sql-server – SSAS tabular browse Excel relationships not working

Thanks to @ConcernedOfTunbridgeWells for pointing me in the right direction on this.

The issue was with the status expression.

Since it ALWAYS returns a 1, 0 or -1 it didn't account for NULLs.

I added a check at the beginning of the expression:

CASE WHEN IsEmpty(KpiValue("MyKPI")) THEN NULL

...which correctly leaves those fields empty.

Here are my answers:

1. Define relationships based on your surrogate keys and not your business keys. This is because this is what the SQL engine will use to optimize queries and this is the reason you have surrogate keys. Since surrogate keys are often integers, this simplifies the joins from using different data types such as strings which are less efficient or even using composite keys. It might be worth defining nonclustered indexes on your business keys for lookups during your ETL process, but queries against the data warehouse should utilize your surrogate keys.
2. Here's how I would approach your fact tables. If the different grades are the same granularity and have most if not all of the same dimensions in common, I would put them in the same fact table. Otherwise, I would put them in separate fact tables. You can always define cubes with multiple measure groups or put a view in place that combines the different fact tables into one that makes sense down the road.
3. You definitely can link one dimension to multiple fact tables. This is referred to as a conformed dimension and is usually more common that linking a dimension to a single fact table.
4. Typically, I create a date dimension that is at a single day granularity and link it to any fact table that has a date field in it. However, in an education context, you might have the need for a different granularity such as just assigning a fact to a quarter dimension instead. If that's the case, you could build out a separate dimension table at the desired granularity. However, for your example of an exam, you would just link that exam record to the date dimension based on that specific day. Furthermore, you should build out your date dimension to have fields such as quarter, school year, etc. One other thing to consider in your example is that it might be useful to calculate the duration of the exam based on the start and end dates if they are in a date time format and you want to know how long it took student to complete a specific exam and to see if duration correlates with results in some way.

Let me know if you have any follow up questions!