Data fields are not allowed in the Page Header section

How can I build the page header (Sales for John Smith) where John Smith is
returned in the query results? Data fields are not allowed in the Page
Header section.
All responses greatly appreciated.
--
Any and all contributions are greatly appreciated ...
Regards TJYou can use read-only parameter with default value from query (assuming that
you render separate instance of report for every person)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJ" <nospam@.nowhere.com> wrote in message
news:O2Mt1aNoEHA.3564@.tk2msftngp13.phx.gbl...
> How can I build the page header (Sales for John Smith) where John Smith is
> returned in the query results? Data fields are not allowed in the Page
> Header section.
> All responses greatly appreciated.
> --
> Any and all contributions are greatly appreciated ...
> Regards TJ
>
>

data being repeated.

Hello, I have merged a master and detail tables into a fact table in order to create a cube, the master table let's say it's a sales table, and the detail the products of the sale, I merged both tables into a single fact table, so the data for one sale is being repeated once for each detail, this is the way I want. So if my sale has a total, let's say $100, and the detail has 3 products, when I visualize the cube the total for the sale is $300, and obviously this is incorrect, and let's say too, if there are many sales with different totals and differents details, the calculation can not be a simple MAX function or AVG, so, somebody know how I can show the data as I want?

Thanks in advance!

odin_dark@.yahoo.com.mx

If you're using AS 2005, you could consider the new many-many dimension feature:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5snapshotisol.asp

>>

Many-to-Many Dimensions in Analysis Services 2005

Richard Tkachuk
Microsoft Corporation

June 2005

Applies to:

Microsoft SQL Server 2005 Analysis Services

Summary: See an example of using the Many-to-Many dimension in SQL Server 2005 Analysis Services to analyze sales data, and get ideas for other uses such as treating medical conditions, software testing, and more.

>>

There is an example, in Adventure Works, where Sales Reason relates to Internet Sales as a many-many dimension, via the intermediate measure group: Sales Reasons.

So, in your case, the main measure group could be based on the master table, and the intermediate measure group could be based on the detail table. And dimensions like Product, which relate directly to the detail table, could be configured as many-many dimensions. In your example of $100 total sale (on the main measure group), $100 would be attributed to each of the 3 individual products, but the contribution at the All Product level should still be $100, not $300.

|||

Hi,

U can u many to many concept.But this can be solved if u proper joins while joining the table.

Some time the relation between the table is not set properly which result in repeated data.

from

Sufian

|||

Hello all, thanks for your reply, unfortunately I'm using analysis services 2000, so is there another way?

and.. wich kind of joins are u talking about, because I want the data to be repeated in the fact table to improve performance, but in the cube I want to fiter this data correctly.

thanks in advance!

|||

Hi,

By repeting the data in the fact table will not increase the performance but it will effect the performance in negative way.

When the cube is processed the it will take more time and more space to store ur aggregates and data.

I am talkin about the joins between the facttable and dimension table.

Some time when u create the cube through desing view analysis services creates the join where it fines refrence of relation keys.

HTH

from

Sufian

|||Unfortunately, I don't know of any way to achieve exactly what you want with AS 2000. In master-detail scenarios, I typically set up separate fact tables and cubes for the master and detail, but combined them in a virtual cube. Still, I had separate measures from the 2 cubes.|||

I've already tried using separate fact tables and combining them into a virtual cube, but I do need the measures "united", not separated, because, how I can know to which "master" registry corresponds the "details" registries?

|||

thanks all!, but I'm still having the problem, mohd sufian could you please send me an example about the joins between tables you're talking about? because i think i have the proper joins but the data is being repeated..

thanks in advance!

|||

Dear ,

In SSAS2005 can have more then one fact table.

So when u going the fact table with the dimension table .The join shoud be Facttable primary key is used as a refrence key in the dimension table.

The same methord is applied in all.

In this way AS will not repeat the data .

HTH

Regards

Sufian

data being repeated.

Hello, I have merged a master and detail tables into a fact table in order to create a cube, the master table let's say it's a sales table, and the detail the products of the sale, I merged both tables into a single fact table, so the data for one sale is being repeated once for each detail, this is the way I want. So if my sale has a total, let's say $100, and the detail has 3 products, when I visualize the cube the total for the sale is $300, and obviously this is incorrect, and let's say too, if there are many sales with different totals and differents details, the calculation can not be a simple MAX function or AVG, so, somebody know how I can show the data as I want?

Thanks in advance!

odin_dark@.yahoo.com.mx

If you're using AS 2005, you could consider the new many-many dimension feature:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5snapshotisol.asp

>>

Many-to-Many Dimensions in Analysis Services 2005

Richard Tkachuk
Microsoft Corporation

June 2005

Applies to:

Microsoft SQL Server 2005 Analysis Services

Summary: See an example of using the Many-to-Many dimension in SQL Server 2005 Analysis Services to analyze sales data, and get ideas for other uses such as treating medical conditions, software testing, and more.

>>

There is an example, in Adventure Works, where Sales Reason relates to Internet Sales as a many-many dimension, via the intermediate measure group: Sales Reasons.

So, in your case, the main measure group could be based on the master table, and the intermediate measure group could be based on the detail table. And dimensions like Product, which relate directly to the detail table, could be configured as many-many dimensions. In your example of $100 total sale (on the main measure group), $100 would be attributed to each of the 3 individual products, but the contribution at the All Product level should still be $100, not $300.

|||

Hi,

U can u many to many concept.But this can be solved if u proper joins while joining the table.

Some time the relation between the table is not set properly which result in repeated data.

from

Sufian

|||

Hello all, thanks for your reply, unfortunately I'm using analysis services 2000, so is there another way?

and.. wich kind of joins are u talking about, because I want the data to be repeated in the fact table to improve performance, but in the cube I want to fiter this data correctly.

thanks in advance!

|||

Hi,

By repeting the data in the fact table will not increase the performance but it will effect the performance in negative way.

When the cube is processed the it will take more time and more space to store ur aggregates and data.

I am talkin about the joins between the facttable and dimension table.

Some time when u create the cube through desing view analysis services creates the join where it fines refrence of relation keys.

HTH

from

Sufian

|||Unfortunately, I don't know of any way to achieve exactly what you want with AS 2000. In master-detail scenarios, I typically set up separate fact tables and cubes for the master and detail, but combined them in a virtual cube. Still, I had separate measures from the 2 cubes.|||

I've already tried using separate fact tables and combining them into a virtual cube, but I do need the measures "united", not separated, because, how I can know to which "master" registry corresponds the "details" registries?

|||

thanks all!, but I'm still having the problem, mohd sufian could you please send me an example about the joins between tables you're talking about? because i think i have the proper joins but the data is being repeated..

thanks in advance!

|||

Dear ,

In SSAS2005 can have more then one fact table.

So when u going the fact table with the dimension table .The join shoud be Facttable primary key is used as a refrence key in the dimension table.

The same methord is applied in all.

In this way AS will not repeat the data .

HTH

Regards

Sufian

CY and LY comparison

Hopefully an easy one:

I would like to get a CY comparison vs. a LY comparison of sales between offices. The problem I am running into is the performance of the query. When I try below, it works, but it is painfully slow (I believe the SUM is what is killing it):

With Member [Measures].[CY Sales] as
Sum (( [Org].[Reg - Pctr - Office].CurrentMember,
[Calendar].[Date].&[20061101] : [Calendar].[Date].&[20070220] )
, [Measures].[Sales])

Member [Measures].[LY Sales] as
Sum (( [Org].[Reg - Pctr - Office].CurrentMember,
[Calendar].[Date].&[20061101].Lag(52 * 7) : [Calendar].[Date].&[20070220].Lag(52 * 7) )
, [Measures].[Sales]
)

Select {
[Measures].[CY Sales]
,[Measures].[LY Sales]
} on 0,
{
[Org].[Reg - Pctr - Office].[East].&[ZZZ123].Children
} on 1
From [Cube]

The comparison is important to get the day of week to day of week comparison (hence the 52 weeks * 7 days vs. using parallelPeriod to do a day of the year comparison).

If I try the following, it is *very* quick, but the problem is if I run it for CY and separately for LY, some offices may not have been open last year, or may have closed this year, so the number of rows returned may be (will be) different. Also, I don't know how to "union" them together into one cellset returned.

Select {
[Measures].[Sales]
} on 0,
{
[Org].[Reg - Pctr - Office].[East].&[ZZZ123].Children
} on 1
From [Cube Name]
Where { [Calendar].[Date].&[20061101] : [Calendar].[Date].&[20070220] }

Does anyone know of a way I can do something like the following, either with Temporary sets, etc.? Please forgive the simplicity of the problem, as I am still cutting my teeth with this powerful, yet complex product. :)

With Member [Measures].[CY Sales] as
({ [Org].[Reg - Pctr - Office].CurrentMember,
[Calendar].[Date].&[20061101] : [Calendar].[Date].&[20070220] } ,
[Measures].[Sales] )

Member [Measures].[LY Sales] as
({ [Org].[Reg - Pctr - Office].CurrentMember,
[Calendar].[Date].&[20061101].Lag(52 * 7) : [Calendar].[Date].&[20070220].Lag(52 * 7) } ,
[Measures].[Sales] )

Select {
[Measures].[CY Sales]
,[Measures].[LY Sales]
} on 0,
{
[Org].[Reg - Pctr - Office].[East].&[ZZZ123].Children
} on 1

From [Cube]

Any suggestions are greatly appreciated!

Thank you in advance,
John Hennesey

It seems to me that the last MDX that you provided won't work, since you are using:

{ [Org].[Reg - Pctr - Office].CurrentMember, [Calendar].[Date].&[20061101].Lag(52 * 7) : [Calendar].[Date].&[20070220].Lag(52 * 7) }

It mixes different dimensions in the enumeration set - you will get an error for that.

I also beleive that the following query should give you very good performance in AS2005 even without SP2, but especially with SP2:

With Member [Measures].[CY Sales] as
Sum ( [Calendar].[Date].&[20061101] : [Calendar].[Date].&[20070220], [Measures].[Sales])

Member [Measures].[LY Sales] as
Sum ( [Calendar].[Date].&[20061101].Lag(52 * 7) : [Calendar].[Date].&[20070220].Lag(52 * 7), [Measures].[Sales]
)

Select {
[Measures].[CY Sales]
,[Measures].[LY Sales]
} on 0,
{
[Org].[Reg - Pctr - Office].[East].&[ZZZ123].Children
} on 1
From [Cube]

But probably, if you really need to compare at the Day Of Year level, the best idea would be to add [Day Of Year] attribute to your Time dimension, and then you will be able to do the following:

Select { [Calendar].[Year].[2006], [Calendar].[Year].[2007] } on 0,
{
[Org].[Reg - Pctr - Office].[East].&[ZZZ123].Children
} on 1
From [Cube Name]
Where { [Calendar].[Day Of Year].&[Day1] : [Calendar].[Day Of Year].&[Day2] } * Measures.Sales

|||

Mosha -

Thank you very much for your quick response. I was afraid that was the only way to do it... When I run the first example, it takes ~9 seconds with just the CY Sales. When I add LY Sales, it goes to 18 seconds. When I add another measure (say, CY Net sales, for the same time period as CY Sales), it balloons to 36 seconds. By the time I get all the measures in (Sales, Return Count, Net sales, etc..) it takes ~1 minute to run. We have a relatively small cube (we do a fully reprocess 4 times a day, which takes < 5 minutes to complete). As for the 2nd example, it is a year to date comparison, so we definitely have to restrict the LY dates to 364 days ago. Sounds like either a redesign of the cube, or service pack 2 is the answer. Does SP2 address this kind of query? Is there any way (for lack of better terms) to design this similar to SQL where you can stash the results from one MDX in a temporary cube, run another MDX then do a join between the two?

Thank you so much,
John Hennesey

|||

> When I run the first example, it takes ~9 seconds with just the CY Sales

Have you tried my variant of the MDX ? I beleive it should perform as fast as set in the WHERE clause even before SP2, but certainly with SP2 as well.

|||

Once again, thank you for the timely response!

Here is what I ran:

With Member [Measures].[CY Sales] as
Sum ( [Calendar].[Date].&[20061101] : [Calendar].[Date].&[20070220], [Measures].[Sales])
Member [Measures].[LY Sales] as
Sum ( [Calendar].[Date].&[20061101].Lag(52 * 7) : [Calendar].[Date].&[20070220].Lag(52 * 7), [Measures].[Sales]
)

Select {
[Measures].[CY Sales]
,[Measures].[LY Sales]
} on 0,
{
[Org].[Reg - Pctr - Office].[RG].[ZZZ123].Children
} on 1
From [Cube]

I did, and unfortunately it took ~20 seconds.

When I run the following it takes less than a second, whether for CY or LY.

Select { [Measures].[LY Sales] } on 0,
{ [Org].[Region - Pctr - Office].[RG].[ZZZ123].Children } on 1
From [Cube]
Where [Preparer Calendar].[Date].&[20061101].Lag(364) : [Preparer Calendar].[Date].&[20070220].Lag(364)

Our environment is c# querying the cube, so I think the answer in this case (until we apply SP2) is to:

Get a list of all offices