Re: How to work with a time dimension
- From: "Christophe Niel" <Christophe.Niel@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 31 Dec 2007 18:12:18 +0100
Thanks for all your information.
I got back to work from (a long)chritmas break and tried various things, finally i made a few adjustment and had my "file date" attribute hierarchy sorted out, and now my cube is working like it did on msas2K (except the fact that there is 10 times the data in the database !!)
I'm going to go and look for a time based analysis or at least a way to keep track of the evolution of the data... (right now I simply reload the database once a week, reprocess the cube and lose the previous version of the data)
I think it a lot more complicated than what I did but that seems a fun thing to do when I'm bored with my job :)
(actually, I'm really doing it for my job since it's the only way we have to keep track of our files server, but I like a chalenge once in a while)
best regards and Happy new year !
"entaroadun" <johnny.c.kwan@xxxxxxxxx> wrote in message news:6ef8f5f3-3659-4d4e-b19a-00f26fa673ff@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Start with a table with the date field only.
Add it to the data source view.
Add these named calculation fields (assumes SQL Server functions):
- month - YEAR(date) * 100 + MONTH(date)
- year - YEAR(date)
Create a time dimension using the wizard pointing to this table.
Set the year, month, and date fields.
In the dimension editor, for each attribute, set the name source
property to None.
Set the order by property to Key.
This standard time dimension only enables simple analysis, not time
series. That type of design is FAR more complicated.
On Dec 19, 2:46 pm, "Christophe Niel"
<Christophe.N...@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
thanks for your reply,
I'm gonna try to change the attribute to Month, but in your experience what
could be the "best" way to make a 3 level dimension based on a SQL Date?
I'm going to look for on-line resources but the example often cover
complicated stuff like financial analysis which I don't understand at all.
My application is an analysis of the disk space used on my files sercers and
my values are the count and sum of size of the files, the dimensions are
- a parent-child dimension for the folder
- file extension
- file owner
- creation date
- last access date
- last write date
As I said with AS2000 it was easy, I had a view to filter the fact a bit
(like strange date : 1601-01-01 or 2048-12-31) and a few drag-n-drop later I
had a cube.
I don't time based analysis of the evolution of the disk space (maybe I
could try), what I need is a simple thing I will use in excel and filter
which ".doc" files were not modified in the last 3 year. (that's the kind of
analysis I make)
(and no, treesize doesn't come even close to what I could do with excel :) )
So, any advice on what I should do (or not do, maybe there are some bad way
to do it) while I go look for BOL?
Thanks in advance
Christophe"entaroadun" <johnny.c.k...@xxxxxxxxx> wrote in message
news:d3b46111-3e82-41db-8249-f12b1dbd7d85@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> In general, you need to understand the difference between an attribute-
> based dimension and hierarchy-based. AS2K5 is attribute-based.
> There's plenty of resources online.
> Regarding the sort order, you set the sort order on the attribute. Go
> to your month attribute (it sounds more like a "month of year"
> attribute from your description), and change the sort order from
> "Name" to "Key". The "Name" property is automatically created by the
> time dimension wizard and just converts your column to WChar. That's
> why the sort order is wrong. If you sort by Key, and your Key
> datatype is Int, then the sort will be correct.
> You really need to change your attribute from Month of Year to Month,
> though.
> On Dec 18, 4:06 am, "Christophe Niel"
> <Christophe.N...@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
>> Hi all
>> I'm still trying to understand how MSAS 2005 is different from MSAS >> 2000,
>> and I'm stuck with the date-time dimension.
>> With msas 2K, all I needed to create a time dimension was to tell the
>> wizard
>> that I needed a time dimension based on a datetime SQL Fields, choose >> the
>> level of granularity I needed, and voila! a nice three level dimension
>> with
>> year, month and day.
>> now I try to work with visual studio 2005 and I don't understand the >> way
>> the
>> date concept is working.
>> I tried a few things, and I don't seems to make it work like I want : >> A 3
>> level hierachy with year, month, day.
>> The best I could came up with was to create 3 sql field in a view, one
>> for
>> year, one for month, and one for day, link this view to my fact table,
>> and
>> create a hiearchy out of this 3 fields.
>> It almost worked except I keep getting data in the wrong sort order :
>> 2007
>> +1
>> +10
>> +11
>> +12
>> +2
>> +3
>> ....
>> So is there a "simple" way to go from a datetime field to a hierarchy
>> like I
>> want?
>> Or am I stuck with my three fields and tweaking the sort order by >> adding
>> "0"
>> to the single digit value to have the correct sort order?
>> Thanks in advance
>> Christophe- Hide quoted text -
- Show quoted text -
.
- References:
- How to work with a time dimension
- From: Christophe Niel
- Re: How to work with a time dimension
- From: entaroadun
- Re: How to work with a time dimension
- From: Christophe Niel
- Re: How to work with a time dimension
- From: entaroadun
- How to work with a time dimension
- Prev by Date: How many fact tables?
- Previous by thread: Re: How to work with a time dimension
- Next by thread: Re: Dimension processing TNS Names do Not Resolve
- Index(es):
Relevant Pages
|
Loading
