Sql-server – Duration measure in fact table

Having the date and time separate will allow you to do aggregates by time much easily. for eg: if you want to run a query to find what time period of the day is most busy. This is much easily performed using a separate time dimension.

Also, you should just have one timekey. Decide on either GMT/ EST time - then use this in the fact table. If you need to run reports based off the other timezone, just convert it in your application or query.

As you correctly noted this is what happens when you try to display measures across a dimension to which they don't have a relation.

You basically have 2 options

1. Use IgnoreUnrelatedDimensions
2. Use an MDX solution

I would suggest you try the IgnoreUnRelatedDimensions first, as the measures would be aggregated better, and NonEmptyCrossJoins would be able to benefit from the Bitmap Indexes used when Attribute Relationships are defined.

The MDX solution is more a last resort because of possible performance issues, and you would also need to define a calculated measure for each measure you have. The MDX solution would most likely be either complex or mess up your totals/show incorrect data.

IgnoreUnrelatedDimensions

When looking at the Adventureworks 2012 cube you have this situation where Employees are not linked to the 'Internet sales' but they are linked to the 'Reseller sales'.

When browsing the cube the result is the same as yours when you add measures from both measure groups across the employee dimension.

You can solve this by setting the IgnoreUnrelatedDimensions property to False on the Measure Group.

After reprocessing the cube and refreshing the browser the result now looks like this:

There are a few caveats with this though, such as an issue with setting DefaultMember

If the DefaultMember is not the All level on one of the attributes of the unrelated dimensions you don't get aggregated data since the default member still acts as a filter even if it's not included in your pivot table:

For example after setting the DefaultMemberproperty on the gender attribute of the employee dimension like this.

This is the result when no slicers are included in the report. Of course, you would want to see the total here:

I seem to recall some other edge cases with calculated measures but I don't remember the specific situation.

MDX Solution

If you run into any of the edge cases you would have to resort to an MDX solution such posted by @GregGalloway, but that could end up being tedious and very specific to a single report or use case:

CREATE MEMBER CURRENTCUBE .[Measures].[Max Current CP With Units] AS
  IIf (
    NOT IsEmpty ( [Measures].[Units] ),
    [Measures].[Max Current CP],
    NULL
  ) ;

The drawback of that solution would be that you would be messing up the way totals look and possibly present incorrect data but that might be acceptable in your specific case of price information with MIN/MAX aggregation but would totally break for sums, and will probably show false data to your users.

For example when running this query

WITH MEMBER [Measures].[Filtered internet order count] as
'
IIf (
    NOT IsEmpty ([Measures].[Reseller Order Count] ),
    [Measures].[Internet Order Count],
    NULL)
'
SELECT {[Measures].[Filtered internet order count],[Measures].[Reseller Order Count]} ON 0,
[Employee].[Employees].allmembers ON 1
FROM [Adventure Works]

Produces this result

Which looks equally strange, since the total is the same as each individual member. But that may be acceptable in your specific case of min/max pricing, I think it's mostly showing false data as there are no internet sales for those employees.

I guess if you control the reports it would be possible. For the added pivot table in your edit I guess it would produce OK results.