SQL 2008 SSAS Time Dimension Question

dsmrtn
dsmrtn used Ask the Experts™
on
Had the class over a year ago.  Have mostly been using the wizard to define measures and dimensions.  We have 12 dimension tables and 1 measures table.  This is the only table that has a datetime column and I cannot get the dimension wizard or tool to recognize this date time column as a time dimension.  I have WROX Book Professional Microsoft SQL Server Analysis Services 2008 with MDX, buit I'm not getting out of it what I want.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
hi

you have 1 field holding date in your measure(fact) table
and you have a data dimension table.

is that so.

let me know your structure and data type you are using  and will get this sorted out.

Author

Commented:
Thanks.  Will try to figure out best way to post structure -  the DIM maps are nice, but not sure if it can be exported.  I'm on vacation this week with strict rules (from wife) that I can only check mail, so this might have to wait a few days.
Top Expert 2010

Commented:
Take your time and enjoy your vacation with family.

you can post screen shot if its easier.

Regards
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
tbl_History is the primary measure table, and only table to have a date.  It has a primary key made up of many columns.  I have created named columns that create join columns to other tables -
convert(char(3),market_code) + state_abbr + Convert(char(1),area_code) and doing the same to the tbl_Market table.

All columns have same datatype on each table - except one, and it has a named column to fix it.
SSAS-Dims.doc
Top Expert 2010
Commented:
dsmrtn:

you need to create a table tbl_datedim

in which you store the dates, like see example below in image

its just sample of the data in the DateDim, basically its all the dates from the year you began saving data in history fact till 10 or 20 years ahead

you can store all days and add more columns so that you get more hierarcies while viewing the data from date point of view.

after adding the DateDim, you can link it to your Fact table datefield.

i suggest converting your datefield in fact to Isofield and link it to Isofield in DateDim.

or you can create a unique key if you like its even more reliable.

Let me know if you need more explaination

Regards
DateDim.png

Author

Commented:
I have been trying to get the correct name for the System Time Dimension - I assume a default or free Time Dimension.  Would it work as well?

Top Expert 2010
Commented:
what do you mean by System Time Dimension.

there is no default one, but its just easily to create

check this
(creating Time dimension  without a Data Source)
http://www.ssw.com.au/ssw/Standards/Rules/CreatingATimeDimensionIn10EasySteps.aspx


--
(Create and Populate Time Dimension) and then use it as a dimension
http://www.sqlservercentral.com/scripts/Data+Warehousing/30087/

there are many other ways but these will do and give you the convept

Author

Commented:
This is what I meant Time Dimension without a data source.  I'll look into this.  So, so far, it seems okay that my only measure table also contains the column used to associate with a Time Dimension?

Commented:
Take a look at the article I wrote to Microsoft Portugal and download the attached files to create the DimDate.
The article is in portuguese, but see the figures and attached files!
Helped?
Pedro

Author

Commented:
I'll try it.  Thanks.  I had had some data issues - missing keys ion dimension table.  Fixed those and built cube using just 2 dimension tables and history measure table.  I had built Date dimension using system, then added that as dimension table, and oddly, received bad date errors on measure table about 41M rows in.  Date column is defined as datetime and NOT NULL.  Hadn't had time to research this yet.

Commented:
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial