Time dimensions: Month order in local cubes 1

[ZaphodB]

I am creating local cubes in SQL server 7 OLAP services using VB Script in a DTS package. WHen I create the time dimension - year quarter month - the months appear in alphaorder - eg. Qtr 1 = Feb Jan March Qtr 2 = April June May and so on.

I have researched this and found a suggested fix (from the Microsoft site)is to incorporate this piece of code in your VBscript to create the months
in the right order


CREATECUBE=CREATE CUBE LocalCube
(
DIMENSION [Account Months] TYPE TIME,
LEVEL [All Account Months] TYPE ALL,
LEVEL [Year] TYPE YEAR,
LEVEL [Quarter] TYPE QUARTER,
LEVEL [Month] TYPE MONTH OPTIONS(SORTBYKEY),

. etc
.
.

INSERTINTO=INSERT INTO LocalCube
(
[Account Months].[Year].NAME,
[Account Months].[Quarter].NAME,
[Account Months].[Month].NAME,

etc.

However running this on SQL Server 7 creates the cubes but still left with the same problem.

If I run the following on the Sales sample cube it works! The months are not named by numbered 1 = Jan 2 = Feb and so on.

DIMENSION [Time] TYPE TIME,
LEVEL [All Time] TYPE ALL,
LEVEL [Year] TYPE YEAR OPTIONS(SORTBYNAME),
LEVEL [Quarter] TYPE QUARTER OPTIONS(SORTBYNAME),
LEVEL [Month] TYPE MONTH OPTIONS(SORTBYKEY),

...

To compound the problem another source (Russ Whitney from SQL Mag) says the following should work:

CREATECUBE=Create CUBE [Sales](
DIMENSION [Customers],
LEVEL [All Customers] TYPE ALL,
LEVEL [Country],
LEVEL [State Province],
DIMENSION [Time] TYPE TIME,
LEVEL [Year] TYPE YEAR,
LEVEL [Quarter] TYPE QUARTER,
LEVEL [Month] TYPE MONTH OPTIONS(SORTBYKEY),
MEASURE [Sales Count] FUNCTION COUNT);
INSERTINTO=INSERT INTO [Sales](
[Customers].[Country],
[Customers].[State Province],
[Time].[Year],
[Time].[Quarter],
[Time].[Month].KEY,
[Time].[Month].NAME,
Measures.[Sales Count])

SELECT
[Sales].[Customers:Country],
[Sales].[Customerstate Province],
[Sales].[Time:Year],
[Sales].[Time:Quarter],
[Sales].[Time:Month!KEY],
[Sales].[Time:Month!NAME],
[Sales].[measuresales Count]
FROM [Sales];


but it doesn't work as SQL 7 doesn't like !KEY

Any ideas? ZAPHODB

 

[issahar]

Hi, ZAPHODB!
I don't know if I help you with this but...
I had problems with the part of the interfaces of DSO - e.g. getting the collection of dimensions inside the cube...when I used them in DTS package VBScript.
I have heard that DSO doesn't support VBScript. The same code worked in Visual Basic...So, if you saw the examples of Microsoft but it is written on VB and not on VBScript - it will not help you at all because VB and VBScript are entirely different...except the lexic style...

My best regards, Issahar

 

[hudi]

Hi!

About your problem!
Why don't you create a procedure in your DTS as SqlExecute Task, which will generate you a time table!
year-quarter-month-week-day
And then you can use VBscript to name all the days and months...!!

It's easier!!

Then your problem can be solved in Analysis manager, where you choose key source column (numeric column of month) and name source column (column with names of months)!!
And all you have to do is to set Order by on Key!!

This is it!!
It's the way I did and I don't have any problems and I can create time dimensions without problems!!

I can send you a DTS (storage file) in which this is all created!
If it's so, send me email!!

bye!!

Andrej

 

[tomgao]

his asking for local cubes