Creating unlimited Nested categories via Stored Procedure

I am trying to create a loop in stored procedure to forumulate a parent/chil
d
( unlimited relationships ). I do not know how to go about doing this in a
stored procedure. I can do it on the frontend but I rather not do it this
way. The following is a sample of the database table:
Categories
---
| CATID | NAME | ParentId |
---
1 Electronics 0
2 Computers 1
3 Cameras 1
4 Sony Cameras 3
5 Clothing 0
6 White TShirt 5
I would like the output to like the following
Electronics
Electronics -> Computers
Electronics -> Cameras
Electronics -> Cameras -> Sony Cameras
Clothing
Clothing -> White TShirt
How can i get this to work via a stored procedure?Something like this would work...
Drop Table #temp
Create Table #temp
(
CatID int,
[Name] varchar(20),
ParentID int
)
insert into #temp values (1, 'Electronics', 0)
insert into #temp values (2, 'Computers', 1)
insert into #temp values (3, 'Cameras', 1)
insert into #temp values (4, 'Sony Cameras', 3)
insert into #temp values (5, 'Clothing', 0)
insert into #temp values (6, 'White TShirt', 5)
Drop Table #temp2
Create Table #temp2
(
CatID int,
[Name] varchar(255),
ParentID int
)
Insert Into #temp2
Select CatID, [Name], ParentID
>From #temp
While @.@.Rowcount > 0
Update t
Set [Name] = t1.[Name] + ' -> ' + t.[Name],
ParentID = t1.ParentID
From #temp2 t Inner Join #temp t1
On t.ParentID = t1.CatID
Select * From #temp2
HTH
Jason|||Procedure nesting is limited to 32 levels in SQL Server (the same goes for
triggers). If your requirements exceed this limit, you'll have to create the
hierarchy in your application.
ML|||Loops!!' Procedural code' !! God! How non-relational!
Get a copy of TREES & HIERARCHIES IN SQL and look up the Nested sets
model.

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 Mathematical Formulas and Calculations

I've created a sql statement that retrieves number data from various table joins. The number data is then grouped according to various categories. What I need to do is to calculate the average of all the number data in a particular group. How do i go about this? Once calculated, the average needs to be displayed under the data.

For example, the report will list say five numbers (some sets may have more, it depends on how much data is returned based on the query), then under the five numbers, the average is given

2

4

3

6

0

Average: 5

What technique is best? Do I have to calculate the numbers in sql or do I need to configure the report to calculate the average? If so, how? Can someone show me step by step how to do averages for a set of data in the reporting services?

I am completely new to doing reports, I'm just a hobbyist, and I've only used databases to retrieve basic data, but not make manipulations for reports.

Any help will be appreciated.

You can use the "Avg" function in reporting services to do this calculation. Average returns the average of the numbers.