Creating an aggregated table

• Kazmerelda

  SSCarpal Tunnel

  Points: 4667

  More actions

  November 14, 2016 at 3:33 am

  #147819

  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!

• Brian Carlson

  SSCarpal Tunnel

  Points: 4170

  More actions

  November 14, 2016 at 4:58 am

  #1912210

  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.