Creating Special Relative Date Categories

I need to re-create special relative date categories in SSAS similar to the functionality offered by Cognos/Powerplay/Tranformer. My problem is that we are replacing Cognos PowerPlay/Transformer with SSAS. With Cognos, you can create relative time categories very easily. The relative time categories are part of the time dimension. When setting up these special time categories, you tell Cognos how to determine the relative time based on the current date or some other calculated date. As a result, the Cognos relative time categories like YTD, MTD, Yesterday etc. are all based off this date. Every time you refresh the cube, the relative date categories change. The big difference between SSAS and Cognos is that SSAS apparently requires you to bring the date hierarchy into a row or column. Whereas, in Cognos, the relative date categories are independent.

For example, I have reports that have several relative date categories in columns like -- Yesterday, WTD, MTD, YTD, Prior YTD etc. Under these columns, I have Sales Dollars, Sales Units, Cost, etc. In rows, I have divisions and products. Cognos knows that Yesterday was January 1, and WTD represents Sunday through Tuesday etc. The relative dates are not dependent on me placing the time hierarchy on the grid.

Is it possible to duplicate this functionality in SSAS? That is, can I create calculated “relative dates” that will change based on the current date or some lag from the current date? Thus, when I add these relative dates to the report, they will always reflect an offset from the current date. It sounds like this can be done through some MDX statement based on the current date but I'm not sure how to do this. Can anyjone provide me with some guidance on this?

Thank you.

David Greenberg

First, it is correct that you will have to feed SSAS2005 with attributes for date, month, quarter and year, but it is fairly simple to this directly in your dimension table or in the datasource view for the cube.

You can use the TSQL DATEPART(), YEAR(), QUARTER()-functions for this. Have a look in Books On Line for date-functions.

When you build the time dimension in Business Intelligence Developer Studio, you are assisted with a guide that will help you will building user hierarchies in the time dimension.

After that you can either create your MDX-time calculations yourself, in the calculation tab of the cube editor, or use the Business Intelligence wizard on the time dimension to get assistance with the MDX.

Here are some useful links

http://www.sqljunkies.com/WebLog/mosha/archive/2006/10/25/time_calculations_parallelperiod.aspx (a little bit advanced)

http://www.sqlmag.com/articles/index.cfm?articleid=46157&

http://www.databasejournal.com/features/article.php/3593466 . Look for William E. Pearson

HTH

Thomas Ivarsson

|||

Hey David,

I have the same problem with the relative dates, I am trying to migrate/re-create our cubes from Cognos to SSAS, when I got to the relative date part I got stuck and I couldn't figure out how to proceed.

I bought 2 books one of them is purely MDX Scripts and none of the books talk about a relative date!!!!!

Were you able to figure out how to incorporate the relative date in SSAS?

Thank you

John Ghannam

|||

Hi John,

I finally figured this out after several months of trying. I even purchased two MDX books myself. I tried a solution to code the MDX with the current date but it just didn't work properly. My solution doesn't use MDX but you can extend the functionality with MDX.

What I developed works very well in terms of query performance and flexibility. My solution requires a view over the date dimension table. Using T-SQL, I wrote a bunch of SQL statements that will return all of the relative dates used in PowerPlay Transformer like YTD, Last 12 Months, Last Month, MTD, WTD etc. All of these statements are based on supplying a starting date that is based off of getdate(). I needed to set the "current date" to be today's date minus one day since we want to track our shipments through the last completed day. The process of figuring this out was tedious but my custom solution works better and faster than using a calculated member because the data is stored in the cube rather than derived at query run-time. With this solution, I was able to figure out a complete process for converting our Cognos environment into SSAS.

The T-SQL statements to derive the relative dates are a bit tricky but you can write me at david@.appliedbusinessintelligence.com if you need help.

David Greenberg

Creating Special Relative Date Categories

I need to re-create special relative date categories in SSAS similar to the functionality offered by Cognos/Powerplay/Tranformer. My problem is that we are replacing Cognos PowerPlay/Transformer with SSAS. With Cognos, you can create relative time categories very easily. The relative time categories are part of the time dimension. When setting up these special time categories, you tell Cognos how to determine the relative time based on the current date or some other calculated date. As a result, the Cognos relative time categories like YTD, MTD, Yesterday etc. are all based off this date. Every time you refresh the cube, the relative date categories change. The big difference between SSAS and Cognos is that SSAS apparently requires you to bring the date hierarchy into a row or column. Whereas, in Cognos, the relative date categories are independent.

For example, I have reports that have several relative date categories in columns like -- Yesterday, WTD, MTD, YTD, Prior YTD etc. Under these columns, I have Sales Dollars, Sales Units, Cost, etc. In rows, I have divisions and products. Cognos knows that Yesterday was January 1, and WTD represents Sunday through Tuesday etc. The relative dates are not dependent on me placing the time hierarchy on the grid.

Is it possible to duplicate this functionality in SSAS? That is, can I create calculated “relative dates” that will change based on the current date or some lag from the current date? Thus, when I add these relative dates to the report, they will always reflect an offset from the current date. It sounds like this can be done through some MDX statement based on the current date but I'm not sure how to do this. Can anyjone provide me with some guidance on this?

Thank you.

David Greenberg

First, it is correct that you will have to feed SSAS2005 with attributes for date, month, quarter and year, but it is fairly simple to this directly in your dimension table or in the datasource view for the cube.

You can use the TSQL DATEPART(), YEAR(), QUARTER()-functions for this. Have a look in Books On Line for date-functions.

When you build the time dimension in Business Intelligence Developer Studio, you are assisted with a guide that will help you will building user hierarchies in the time dimension.

After that you can either create your MDX-time calculations yourself, in the calculation tab of the cube editor, or use the Business Intelligence wizard on the time dimension to get assistance with the MDX.

Here are some useful links

http://www.sqljunkies.com/WebLog/mosha/archive/2006/10/25/time_calculations_parallelperiod.aspx (a little bit advanced)

http://www.sqlmag.com/articles/index.cfm?articleid=46157&

http://www.databasejournal.com/features/article.php/3593466 . Look for William E. Pearson

HTH

Thomas Ivarsson

|||

Hey David,

I have the same problem with the relative dates, I am trying to migrate/re-create our cubes from Cognos to SSAS, when I got to the relative date part I got stuck and I couldn't figure out how to proceed.

I bought 2 books one of them is purely MDX Scripts and none of the books talk about a relative date!!!!!

Were you able to figure out how to incorporate the relative date in SSAS?

Thank you

John Ghannam

|||

Hi John,

I finally figured this out after several months of trying. I even purchased two MDX books myself. I tried a solution to code the MDX with the current date but it just didn't work properly. My solution doesn't use MDX but you can extend the functionality with MDX.

What I developed works very well in terms of query performance and flexibility. My solution requires a view over the date dimension table. Using T-SQL, I wrote a bunch of SQL statements that will return all of the relative dates used in PowerPlay Transformer like YTD, Last 12 Months, Last Month, MTD, WTD etc. All of these statements are based on supplying a starting date that is based off of getdate(). I needed to set the "current date" to be today's date minus one day since we want to track our shipments through the last completed day. The process of figuring this out was tedious but my custom solution works better and faster than using a calculated member because the data is stored in the cube rather than derived at query run-time. With this solution, I was able to figure out a complete process for converting our Cognos environment into SSAS.

The T-SQL statements to derive the relative dates are a bit tricky but you can write me at david@.appliedbusinessintelligence.com if you need help.

David Greenberg

Creating something similar like SCD wizard interface

HI,
I created a script component that is doing some transformations and it works very well. Now, I want to use the same script component on other dataflows in order to use it with other tables. Since my script component uses three outputs and some of my dimensions may have sometimes 50-60 columns, and the fact that I need to customize the column names used in my script, I would like to be able to have some kind of wizard like the SCD wizard to create columns into the outputs based on a list of columns that the user would and and input some columns (historical attributes, etc) into my script component logic .

Has anybody did that before, or is there some articles/samples where I could look in order to get me started in doing this?

Thank you,

Ccote

You would probably want to write a custom component, rather than trying to implement this in a ScriptTx. BOL has a ton of info, as well as samples and tutorials on how to do this. Also see the tutorial and custom components on this site:

http://msdn.microsoft.com/sql/bi/integration/downloads/default.aspx

As an aside SCD is a special case that uses advanced services to create several child components on the design surface. In RTM code you could not emulate this behavior, however in SP1 (soon to be released on the web) we have exposed these interfaces. The IDtsPipelineEnvironmentService service lets custom data flow components have programmatic access to the parent Data Flow task.

|||

I'd be a bit stronger, as soon as you start asking the question how can I resue a script task or component, the answer should actually be to write a custom component.

The SCD interface is different to most in two ways.

First it uses a Wizard style of form layout and behaviour, which you could do today in a task or component.

The second is that it spans several data flow components, not just the core SCD component you will see in the middle after completing the wizard. The ability to see outside of the current component, accessing the parent data flow and then other sibling components is what you cannot do until we get IDtsPipelineEnvironmentService in SP1.

|||

You are right by saying that what I want to do is different someways to the SCD wizard.
We have both standard type 1 dimension and hybrid dimensions (type1 columns and type2 columns) that need to be processed. Since the SSIS lookup cache is not refreshed (unless cache is not used which has perfomance issues), I had to create an asynchrounous script component that suit our needs. SSIS is great for this by the way. My script component can handle type 1, type 2 as well as hybrid dimensions.

What I would like to do is to build a user interface that would modify the package
definition is a sense that it would take columns in output (like the script component does right now), do some script modifications and produce 3 outputs:

1- Type 1 columns update
2- Type 2 inserts based on some other columns
3- Type 2 current flag indicator update to 0 of the row that was already current before the load.

To produce all of these outputs and modify the script accordingly by hand will be feasible but teadious and error prone. I do know that there is sections in the package (.dtsx) where this info appears. I would like to create a wizard type form that would allow me to add outputs and script changes whitout doing this manually in the script component. The wizard would generate the script component for me, which would be faster to do and much less error prone.

Thank you all for your help,
Ccote

|||

You could do this in one of two ways I think. What you cannot do however is add your own UI to the script component.

1 - Create a VS Addin that configures the package. I am not sure that this is feasible, would you be able to get the package context and would changes be reflected in the UI are two points to check first of all.

2 - Create a custom component. When you drop this new component onto a package it would invoke your component's custom UI (implemented in a wizard style etc). From there you could get access to the package, and should be able to add the script component and confgure it to your requirements. Unless you can then self delete you would be left with the redundant component in your package, but I cannot see any other way. You cannot write a custom UI for the script component. This really makes me think you would be better of writing a custom component to do the work itself rather than messing around with the Script Component.

|||

HI Darren, thank you for your answer. I think that answer two is the way to go. Is there any documentation that could get me started. Basically, I need to create a component that would behave like an asynchronous script component, perform some logic based on some columns (identified by user) and add some outputs. I have Wrox Professionnal SSIS book, I see that there is a few chapters in there. Do you have other suggestions?

Thank you very much for your help,

Ccote

|||The is an asynch component sample that ships wth the product. There are also quite a few samples available from MS Downloads. Try these as well.

creating multiple tables?

Hello,
I need to create around 1500 similar tables.

Does anyone know how to create them all at once instead of one-by-one?


thanks

If these tables currently reside in another RDBMS then you MAY be able to generate sql for them. I am a pure MS SQL SERVER geek so I am unsure of, but regardless you would have to convert the scripts to use TSQL's create table statement.

If your tables are similiar enough and you must create these fresh you do some thing like the following (use dynamic sql):

declare @.SQL nvarchar(1000)

declare @.i int

select @.i = 0, @.SQL = ''

while @.i <= 1499

begin

set @.SQL = 'CREATE TABLE ' + [table name algorithm goes here] + [table definition goes here

exec sp_executesql @.SQL]

set @.i = @.i + 1

end

TSQL Create Table Syntax:

CREATE TABLE table_name

( { < column_definition > | < table_constraint > } [ ,...n ]

)

< column_definition > ::=

{ column_name data_type }

[ { DEFAULT constant_expression

| [ IDENTITY [ ( seed , increment ) ]

]

} ]

[ ROWGUIDCOL ]

[ < column_constraint > [ ...n ] ]

< column_constraint > ::=

[ CONSTRAINT constraint_name ]

{ [ NULL | NOT NULL ]

| [ PRIMARY KEY | UNIQUE ]

| REFERENCES ref_table [ ( ref_column ) ]

[ ON DELETE { CASCADE | NO ACTION } ]

[ ON UPDATE { CASCADE | NO ACTION } ]

}

< table_constraint > ::=

[ CONSTRAINT constraint_name ]

{ [ { PRIMARY KEY | UNIQUE }

{ ( column [ ,...n ] ) }

]

| FOREIGN KEY

( column [ ,...n ] )

REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]

[ ON DELETE { CASCADE | NO ACTION } ]

[ ON UPDATE { CASCADE | NO ACTION } ]

}|||thank you very much

I will post again in this thread if I have any troubles|||

Hi I'm having some trouble, I made this query:

declare @.SQL nvarchar(1000)

declare @.i int

SELECT @.i = 0, @.SQL =

WHILE @.i <= 32228

begin

set @.SQL = 'CREATE TABLE' + tbl_i_quotes + (

QuoteDate nchar(20),

QuoteTime nchar(20),

BidPrice float,

AskPrice float,

BidSize float,

AskSize float)

exec sp_executesql @.SQL

set @.i = @.i + 1

end

and i got this as an error message:

Msg 156, Level 15, State 1, Line 7

Incorrect syntax near the keyword 'WHILE'.

Msg 102, Level 15, State 1, Line 12

Incorrect syntax near 'nchar'.

thanks again

|||

this works:

declare @.SQL nvarchar(1000)

declare @.i int

SELECT @.i = 0, @.SQL = ''

WHILE @.i <= 32228

BEGIN

set @.SQL = 'CREATE TABLE' + '[tbl_' + CONVERT(nvarchar(5),@.i) + '_quotes] (

QuoteDate nchar(20),

QuoteTime nchar(20),

BidPrice float,

AskPrice float,

BidSize float,

AskSize float)'

exec sp_executesql @.SQL

set @.i = @.i + 1

end

|||it works! thank you so much!


I'd hate to bother you more, but you seem so informative...

do you know much about bulk inserting a folder full of flat files into select tables?
maybe using integration services? i've tried BCP and have had pretty negative results.


thanks again, yer a life saver.|||your welcome :) glad it helped. I used DTS in sql2000 ALOT, but have had almost 0 exp. with SSIS in 2005 as of yet. However, SSIS (like DTS) has always been a great tool for importing and exporting data and I would look at leveraging it for loading a flat file. BCP/BULK INSERT is good to.

Creating indexes (details required)

I need some idea of how SQLServer 2000 creates indexes, particularly
clustered indexes.
I imagine that creating a clustered index is rather similar to defragmenting
a hard disk. Data has to be read from disparate areas, stored "somewhere"
until there is a large enough area and then written to that area in a
contigious manner.
If that premise is correct, where is the temporary storage area? Is it in
the database itself or is it in the TempDB?
The reason I ask is because SQLServer is crashing my machine when it builds
indexes (for details see my posting "SQLServer crashing server!" in
"microsoft.public.sqlserver.server".
Thanks in advance
Griff
When indexes are created the temporary storage for the duplicate ( if
replacing indexes), etc is on the SAME filegroup as the index will be on..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Griff" <Howling@.The.Moon> wrote in message
news:%232qla4KaEHA.2216@.TK2MSFTNGP10.phx.gbl...
> I need some idea of how SQLServer 2000 creates indexes, particularly
> clustered indexes.
> I imagine that creating a clustered index is rather similar to
defragmenting
> a hard disk. Data has to be read from disparate areas, stored "somewhere"
> until there is a large enough area and then written to that area in a
> contigious manner.
> If that premise is correct, where is the temporary storage area? Is it in
> the database itself or is it in the TempDB?
> The reason I ask is because SQLServer is crashing my machine when it
builds
> indexes (for details see my posting "SQLServer crashing server!" in
> "microsoft.public.sqlserver.server".
> Thanks in advance
> Griff
>

Creating indexes (details required)

I need some idea of how SQLServer 2000 creates indexes, particularly
clustered indexes.
I imagine that creating a clustered index is rather similar to defragmenting
a hard disk. Data has to be read from disparate areas, stored "somewhere"
until there is a large enough area and then written to that area in a
contigious manner.
If that premise is correct, where is the temporary storage area? Is it in
the database itself or is it in the TempDB?
The reason I ask is because SQLServer is crashing my machine when it builds
indexes (for details see my posting "SQLServer crashing server!" in
"microsoft.public.sqlserver.server".
Thanks in advance
GriffWhen indexes are created the temporary storage for the duplicate ( if
replacing indexes), etc is on the SAME filegroup as the index will be on..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Griff" <Howling@.The.Moon> wrote in message
news:%232qla4KaEHA.2216@.TK2MSFTNGP10.phx.gbl...
> I need some idea of how SQLServer 2000 creates indexes, particularly
> clustered indexes.
> I imagine that creating a clustered index is rather similar to
defragmenting
> a hard disk. Data has to be read from disparate areas, stored "somewhere"
> until there is a large enough area and then written to that area in a
> contigious manner.
> If that premise is correct, where is the temporary storage area? Is it in
> the database itself or is it in the TempDB?
> The reason I ask is because SQLServer is crashing my machine when it
builds
> indexes (for details see my posting "SQLServer crashing server!" in
> "microsoft.public.sqlserver.server".
> Thanks in advance
> Griff
>

Creating indexes (details required)

I need some idea of how SQLServer 2000 creates indexes, particularly
clustered indexes.
I imagine that creating a clustered index is rather similar to defragmenting
a hard disk. Data has to be read from disparate areas, stored "somewhere"
until there is a large enough area and then written to that area in a
contigious manner.
If that premise is correct, where is the temporary storage area? Is it in
the database itself or is it in the TempDB?
The reason I ask is because SQLServer is crashing my machine when it builds
indexes (for details see my posting "SQLServer crashing server!" in
"microsoft.public.sqlserver.server".
Thanks in advance
GriffWhen indexes are created the temporary storage for the duplicate ( if
replacing indexes), etc is on the SAME filegroup as the index will be on..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Griff" <Howling@.The.Moon> wrote in message
news:%232qla4KaEHA.2216@.TK2MSFTNGP10.phx.gbl...
> I need some idea of how SQLServer 2000 creates indexes, particularly
> clustered indexes.
> I imagine that creating a clustered index is rather similar to
defragmenting
> a hard disk. Data has to be read from disparate areas, stored "somewhere"
> until there is a large enough area and then written to that area in a
> contigious manner.
> If that premise is correct, where is the temporary storage area? Is it in
> the database itself or is it in the TempDB?
> The reason I ask is because SQLServer is crashing my machine when it
builds
> indexes (for details see my posting "SQLServer crashing server!" in
> "microsoft.public.sqlserver.server".
> Thanks in advance
> Griff
>