Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1492
  • Last Modified:

how do you include text fields in ssas cubes?

I have to build an SSAS cube to hold a data table with two look up tables; the main table holds a list of systems, with organisation codes,  licences and costs, linking to the organisation list (holding full names and types), and to a usage table.
I've got a cube where I can list the organisation details in the rows, but the only measure I'm getting to display is a count of the system names - the system names and comments fields aren't available.
How can I get at least the system names to show? (this is only my second attempt at a cube, the last one was just a lot of costs, and was dead simple).
  • 3
  • 2
1 Solution
ValentinoVBI ConsultantCommented:
To build a good cube, you should first sketch out what your facts and dimensions are. Using the info above I'd come up with something like:

DimOrganisation: Name, Type and other attributes belonging to the "organisation"
FactCost: Cost

As the info is very limited, the above is just something to get you started.  Numbers like "cost" are your measures and belong in a Fact table.  Descriptions, comments, and so on are usually attributes of a dimension, as I've tried to indicate.
You'll probably have/need more than one dimension. DimLicense perhaps - without knowing what "license" means it's difficult to tell.  Or maybe DimSystem?
DimDate is usually also one of the dimensions, in most cases you'll want to see your numbers filtered on certain date intervals.

If you want to pick up a good book on data warehouse design, have a look at the books by Kimball, for instance "The Microsoft Data Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft Business Intelligence Toolset" and "The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)".

I hope this gets you started in the right direction!
mr_nadgerAuthor Commented:
sorry, will try to clarify a little.
I've been using SSRS to build drill down reports, where I could simply insert the application name and comments as columns in a group, and now it's been decided we need this data available via a cube as the amout of data is becoming rather large.

The table I'm moving into the fact data is as follows:
organisation code (int,FK),
application name (freetext varchar),
application purpose code (int,FK),
number of licenses (int),
support & maintenance costs (money),
additional comments (freetext varchar)

There are look up tables for the organisation details, and the application purpose, using the appropriate codes.
Unfortunately, the data collection method used allowed people to enter the application names as freetext, so we have the usual spelling mistakes, abbreviations, and inconsistencies you'd expect.

I was wondering if I needed to move the application name out and have it as a new dimension, but then that still leaves the freetext additional comments, and it doesn't seem right to have those as a dimension too.

Thanks for the help, much appreciated.
mr_nadgerAuthor Commented:
I should add that the number of licences, support costs and application count are the measures I'm trying to pull out in this order
organisation,application,purpose, number of licences, support cost (comments, if possible)
ValentinoVBI ConsultantCommented:
The freetext fields are indeed not at the right place if they are currently stored in your fact table.

How about a DimApplication that contains both those fields?  Or a DimLicense? (or both?)
ApplicationName would surely be a good attribute of DimApplication but as I don't know what those "additional comments" are referring to, that's a bit more difficult to tell.
In any case, AdditionalComments doesn't seem like something you'll want to filter your measures on, so you should set the AttributeHierarchyEnabled for that attribute to False.  
See also http://technet.microsoft.com/en-us/library/ms166717.aspx

Or... a Junk dimension?  See http://en.wikipedia.org/wiki/Dimension_%28data_warehouse%29
mr_nadgerAuthor Commented:
Junk dimension? Sounds like the town I grew up in...

Thanks Valention, just wanted to be sure I wasn't going about it in completely the wrong way.

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now