asked on

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 -

Miriam0000

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
http://msdn2.microsoft.com/en-us/library/ms174420.aspx

_Scotch_

ASKER

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.

ASKER CERTIFIED SOLUTION

_Scotch_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial