how do you include text fields in ssas cubes?

Posted on 2011-05-09
Last Modified: 2016-02-14
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).
Question by:mr_nadger
    LVL 37

    Expert Comment

    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!
    LVL 7

    Author Comment

    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.
    LVL 7

    Author Comment

    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)
    LVL 37

    Accepted Solution

    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

    Or... a Junk dimension?  See
    LVL 7

    Author Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. (htt…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now