Time hierarchy in SSAS (dyslexia)

2
votes

Time dimension have hierarchies:

  • Year – month – date
  • Year – week – date
  • Year - month

I query them so:

LASTPERIODS( -12, StrToMember("[Time].[Year -  Week -  Date].[Week].&[" + Format(now(), "yyyy-MM-" + "01T00:00:00") + "]"))

returns null

LASTPERIODS( -12, StrToMember("[Time].[Year - Month].[Month].&[" + Format(now(), "yyyy-MM-" + "01T00:00:00") + "]"))

returns future 12 months

If i query like this: 

select non empty([Time].[Year -  Week -  Date].[Week].members) on 0

it will give me the week members..

Am I missing some parameter?

1
sql-serverssasmdx

1 Answers

0
votes

It's really down to how your keys are made. By using the &, you're saying to use the underlying key, not the name.

In your first example, you're asking for weeks that have an underlying key in the format of "yyyy-MM-01T00:00:00", which seems more like a start-of-month than a start of week.

When you run your second query, does it give you elements like the query you're trying to write?