Link to home
Start Free TrialLog in
Avatar of mr_nadger
mr_nadgerFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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).
Avatar of ValentinoV
ValentinoV
Flag of Belgium image

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!
Avatar of mr_nadger

ASKER

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.
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)
ASKER CERTIFIED SOLUTION
Avatar of ValentinoV
ValentinoV
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.