Dispalying AS2005 Parent-child hierarchy with RS2005

Hello,

in AdventureWorksDW at Dimemployee there is a parent-child dimension.

If I use the browse Tab in VS, I can browse all the levels of that hierarchy.

My question is How do I produce this kind of report with RS2005 sp1?

I need the report to behave the same as the browser of VS, meaning that the level depth can change.

I looked all over the web, but didn't find any solution.

Thanks,

Yoav.

Your problem is to fetch all data of parent-child dimension in a single query oi order to fit it in sinlgle SSRS report. I suppose that such solution won't satisfy you. You schould develop report getting data from SSAS by demand, not all-in-one query.|||I had the same problem. From I've been reading Reporting Services has limited functionality when dealing with Analysis Services including the parent-child relationship.

In analysis services I never designated a parent-child relationship, I just simply put the appropriate keys into the cube. That way Reporting Services can do the hierarchy just like it does for aregular SQL query.

The query should return an ID and a Parent ID. In RS create a table with just the Details row (just now at least). In the properties put the group on being the ID and the Parent being the parent ID.

For more info checkout:
http://www.codeguru.com/csharp/.net/net_data/sortinganditerating/article.php/c12223/|||

If this not is working with a SSAS2005 cube you can query the data mart/source system with a TSQL CTE(Common Table Expression). I know that this is an OLAP forum but if nothing else works try the examples i SSRAS2005 that you find on this link: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql_05TSQLEnhance.asp

HTH

Thomas Ivarsson

|||

Hi,

thanks for the answer .

I asked if there is an option to use the hierarchy of AS?

I know how to produce RS parent-child reports.

is this the only option?

Discussion about dynamic creation of partitions in AS2005

I have searched around and found very little about dynamically creating (and processesing) partitions in AS2005.

In AS2000 it is actually quite easy to manage partitions dynamically using a metadata table and DSO, but i had hoped that they came a little further in AS2005. Basically it's all about dynamically creating a partition for each month of a year or just partitioning over years. Does anyone have some good input about how to do it in AS2005 ?

Furthermore does someone have a comment on which prefferable option to choose (or maybe a completely other way) concerning partitions.

Option 1:

Partition "FACT_SALES_2005" points to a view called "v_FACT_SALES_2005" that is a "Select col1,col2 from FACT_SALES where period_year = 2005". The FACT_SALES table has a clustered index on period_year.

This way it's easy to manage views using a stored_procedure (create new view when we enter a new month etc.) and all the data resides in one single table which eases the ETL (in my opinion)

Option 2:

Partition "FACT_SALES_2005" points to a table called "FACT_SALES_2005".

This way it connects directly to the table, which someway is ok when it's partitioned in years, but what if you want to partition in months or weeks (52 weeks a year for 5 years of data). Then it becomes hard to manage.

Option 3:

Use partitioned tables and somehow point to a specific partition in the partitioned table from AS2005.

This i havent tried. But how will this perform. If you have a partitioned table that is partitioned in months, how do you reffer to let's say April 2006 in that partitioned table (FACT_SALES) from AS2005. And how about setting the Slice value of that partition....

Hi there,

i've had the same Problem and written a Assembly in .Net. This Assembly do all the things you can do from the GUI.

So i can process, create, ... Partitions from a stored Procedure in SQL. It works very nice. If you have detail Questions, aske me

Kind Regards

Andy L?wen

|||

You might take a look at:
http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=Partition

That function isn't in the latest release, so you'll have to download the source code and compile it yourself from:
http://www.codeplex.com/ASStoredProcedures/SourceControl/ListDownloadableCommits.aspx

|||

It is definitely worth having a look at the code that furmangg points to. You should be able to use this "out of the box" or if not it will give you a good starting point.

There is one variation that is probably the easiest to manage which is similar to option 1. And that is to have all your data in a single table and then use a query as the data source for the partition to only select the data for a specific period. (essentially storing the view definition in the partition rather than creating a separate view object in the relational database)

And just a quick note on option 3. When you have a partitioned table it appears to all the other applications as if it is a single table, the data is partitioned based on specific data values, so selecting data from a particular partition is simply a matter of using a where clause that matches the partitioning scheme.

|||

Nice, i'll have a look at it.

I just think it's funny the Microsoft hasn't put any more attention into partitioning setup/handling since it's the most obvious thing to use when working with large cubes, which again is what SQL Server 2005 is profiling itself to handle very well.

sql