CODEWITHSUNDEEP
ssas
Bee
Bee

Reputation: 341

Creating an SSAS cube based on single table or view

I am trying to create an SSAS cube based on a single table. Unfortunately, running the services in tabular mode is not an option since we already have the instances running in Analysis server mode.

I read a couple of other posts where folks mentioned it is indeed possible to create a cube based off a single table or view.

Question though is: How and where do I specify the dimensions and measures when creating based off single source?

I created a data source view, but wasnt sure how I would specify the dimensions and measures. Thought I'd ask here for help.

Thanks in advance, Bee

Upvotes: 0

Views: 1238

Answers (2)

SebTHU
SebTHU

Reputation: 1405

For your dimension keys to be unique, you'd have to base your dimensions (for example, a Country dimension) on a 

SELECT DISTINCT Country[,... other things] FROM TheSingleTable

query.

There are disadvantages to this method. The dimension is never materialised outside the cube itself, which can make it very difficult to analyse problems when the dimension data seems to be wrong. For the same reason, creating a slowly-changing dimension is impossible.

The advantage is that you don't have to do any ETL work to detect new dimension members and populate a dimension table when new data comes in. The disadvantage is that, unless every dimension is based on a single column only from the table, it'll become incoherent.

If a dimension is based on more than one column, as in:

SELECT DISTINCT CountryCode,CountryName FROM TheSingleTable

then, if there are no constraints on the single table or the ETL process to guarantee that the two columns range of values map exactly 1 to 1 to each other, then new data will upset your dimension membership.

For example, you have an existing member with CountryCode "USA", CountryName "United States". If just one new fact row comes into the table, with CountryCode "USA", CountryName "Uited States" (or NULL), this will be interpreted as a new dimension member. You'll either get dimension processing duplicate key errors (if you're lucky), or spurious multiple dimension members.

It's so easy for new data to upset this kind of design, that I'd recommend against it unless it's a really small, simple project, and you have time to intensively analyse incoming updates and think through what consequences they might imply.

Upvotes: 1

Kyle Hale
Kyle Hale

Reputation: 8120

Just create them like you create normal dimensions.

  1. Right-click on Dimensions in Solution Explorer, select "New Dimension" and then "Use an existing table."
  2. Choose your single table, and choose your key columns. Since you don't have surrogate keys, you'd have to choose whatever columns would make your dimension rows unique. So if you have a Location dimension with Country->State->City data, maybe City is unique, maybe City/State, maybe you need all three.
  3. Choose additional attributes you want to include in the dimension.
  4. Define your attribute relationships, hierarchies, etc. as normal.
  5. When you create a new cube, select your SingleTable as the measure group and choose the measures, then choose the new dimensions you created to include in the cube.

Kind of hard to maintain, obviously, but it's certainly possible.

Upvotes: 1

Related Questions