Price Aggregation Question

Hi all,

I have a cube that has time, product, and location hierarchies. I also have the retail price for every transaction at the leaf level. I am having trouble writing the mdx to get the aggregation the way I need them.

In the time dimension, at the non-leaf level, i want the last retail price of the children in the time. ie: if I am at the quarter level, i want to show the price of period 3.

In both the Product and Location dimensions, I want the retail price to be averaged at the non-leaf levels such as Product category and Zip code.

I don't really know what the right approach is to make this happen, any advice is greatly appreciated.

Thanks,

Wino...

Hi Wino,

Based on my understanding of your scenario, and assuming you have AS 2005 Enterprise Edition, here's an approach to try:

Create a cube measure like [RetailPrice] on the "Retail Price" fact table field, using LastNonEmpty aggregation function.

Preventing the loading of duplicate data to a table - Best Option

Start at the file level; sort it, and scrub it with a 3GL program
before you load it. I would also look into Sunopsis. This is an ELT
tool -- it uses native SQL tools to move data rather than adding yet
another ETL language on top of everything.Thanks, I appreciate the tip; however, the constraints of the project
require that this be done in SQL Server. I am interested to know if this
(Primary Key or Unique Constraint) is the best way to do this within the
restrictions that are in place.
*** Sent via Developersdex http://www.examnotes.net ***|||A PK or UNIQUE constraint is the obvious way to generate the exception
condition. It's probably useful also to report the invalid data so you
may want to load to a staging table, without the constraint, and then
write a query using HAVING COUNT(*)>1 to find the duplicate rows.
David Portas
SQL Server MVP
--

Preventing expansion of a cross-tab column

Is it possible to prevent certain users from expanding a column (or
row) in a cross-tab/matrix report? That is, a lower level of
granularity in a report column is too detailed for a certain job role,
and you don't want that person to be able to expand that column out,
but you don't want to write multiple reports to accomplish this task.
Thanks.On May 10, 12:25 pm, kmac2...@.gmail.com wrote:
> Is it possible to prevent certain users from expanding a column (or
> row) in a cross-tab/matrix report? That is, a lower level of
> granularity in a report column is too detailed for a certain job role,
> and you don't want that person to be able to expand that column out,
> but you don't want to write multiple reports to accomplish this task.
> Thanks.
I would suggest setting a hidden parameter for the current user (=User!
UserID.ToString) in the report, then pass this hidden parameter to the
stored procedure/query that is sourcing the report and based on the
user's job role, do not return certain drill-down (etc) data. Hope
this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||The suggestion helped a bit...I was able to use the user role to set
certain information null, so when the group is expanded in the matrix,
it doesn't do anything but add another header row. However, the +/-
sign is still there, and I'd like to hide it in these situations. I
found the place to work with this information in the Edit Group
properties of the matrix column, but the ToggleItem can't be an
expression. While I could set it to another textbox than the default
and hide -that- textbox (which would hide the +/-) it would remove the
context of the +/- in situations where I need it. I might be missing
something, but I can't figure how to make this work out. Any ideas?