[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

SSAS: How to work with time ?

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 -
  • 2
1 Solution
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
_Scotch_Author Commented:
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.

_Scotch_Author Commented:
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.

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

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