November 14, 2016 at 3:33 am
Hi All,
So I am an SSAS noob, I have done some tutorials in my distant past, and can work out how to create projects etc.
I have a table that I need to create, and I was already thinking about how we could use SSAS for this versus creating it via several stored procedures.
So what I need is a table that has in it a month/year date column, an area column and then a sub area column. From here, there are then aggregated values based on the sub area and area and this can be split by date.
So in my mind I thought about using the tabular modelling rather than a multidimensional cube (but correct me if I am way off!). I have started by pulling in one of the tables that will give me the aggregate values (ie a count of) that contains both the area and sub area so this can connect to a lookup table for these that we have and also a date which I can then reference back to a top level date table again a lookup.
So far, I have my main table and how it connects to the other ones, but then I get stuck. I am ploughing through tutorials but is there a good way of doing this? What I want to look like is this (apologies for crudeness!):
Month IDAreaSub AreaAggregate value 1Aggregate Value 2
201610 UK AA 10 15
201610 UK AB 2 4
201610 UK AC 15 78
201610 UK AD 6 6
201610 UK AE 80 3
201610 UK AF 9 1
Any pointers are appreciated, I can do it with a lot of coding but there has to be a simpler way using AS I feel.
Thanks in advance!
November 14, 2016 at 4:58 am
Tabular does sound like the way to go from what you are describing. Are area & sub area a hierarchy? If so, you'll want to model them in one dimension table. Since tabular requires one key, you'll need to make a surrogate key for that. Other than that, I'm not sure what you're having a problem with. Describe more what the problem is that you're having and I'll see if I can help.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply