SSAS: How to work  with time ?

Posted on 2007-08-09
Last Modified: 2016-02-12
Consider a table (tblStuff) with the following columns:
Color: red, white, blue, etc
Size: small, medium, large
Weight: light, medium, heavy
CustomerID: FK to customer table
DateSold: datetime

I have built a cube with a single measure "tblStuff Count" and am able to slice the counts according to color, size and weight, etc., but I want to also slice the data by fiscal months, quarters and years.  I have no idea what I'm doing ...

I think I need to create a server time dimension but have no idea how to connect it to the DateSold field in tblStuff.

Can somebody fill in the missing details, please ?

Thanks -
Question by:_Scotch_
    LVL 2

    Expert Comment

    Not sure if this is what you need, but in sql server 2005:

    datepart ( ) gives you a piece of a date

    For example:
    datepart (mm,datesold)                                returns the month
    datepart (qq, datesold)                                returns the quarter
    datepart (yyyy, datesold)                            returns the year.

    documentation is here
    LVL 3

    Author Comment

    Thanks Miriam0000:.  I know about datepart - with the help of this forum I've become half decent at SQL but in SQL Server Analysis Services (SSAS) the process is different.  For example, I have:

    1) Created my data sources
    2) Created my data source views
    3) Created my single measure (count)
    4) Created the dimensions I need
    5) Deployed the cube to an SQL Server Analysis Services Server

    When you browse the cube from bi studio or SSMS you can then drag-n-drop your measures and dimensions onto a workspace and see the answers to the queries that SSAS generates for you.

    Lets say I have 100 records in tblStuff and 10 of them specify Color: Green.  If I drag "tblStuff count" onto the work space it creates a cell called "tblStuff count" and sets it to 100.  If I then drag Color onto the workspace to where it says "drop row fields here" then it creates a row on the workspace for each color in the table along with the counts of records that specify the color.  Green would be 10, red another 10 or whatever.  It will also create subtotals, totals and grand totals on the fly.

    SSAS also has the ability to break thing down by time by using a "server time" dimension but I have no idea how to connect such a dimension to a table field.

    LVL 3

    Accepted Solution

    I've answered my own question - for posterity, here it is:

    First, if you have a date-time field in an SQL column then you can't use it.  You have to modify that field somewhat to remove the time component - convert(datetime,convert(varchar(30),date-time-field,101)) worked for me.  This converts things like 2007-07-04 12:34:56.78 to 2007-07-04 00:00:00.00.  This is important because SSAS uses the date as a key and can only match on the date.  If you have a time component you'll get "attribute key not found" errors.

    Then you add a server time dimension choosing whatever calendars you want.

    Then you add this dimension to your cube and from the dimension usage pane you have to map this dimension to your (modified) date-time field in the sql fact table.

    Zone Mods:  Please PAQ this question for me and refund the points.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
    When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    779 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

    10 Experts available now in Live!

    Get 1:1 Help Now