May 2, 2012 at 4:17 pm
Hi everyone!
I've been fighting with SSAS scope and MDX for some days...
I have a cube that takes data from 2 fact tables.
I need something very simple:
If date > '01/06/2011', take measure from fact table 1
If it is not, take measure from fact table 2.
As you can see, so simple is the problem, but i haven't could do it in MDX. I've tried the SCOPE statement:
scope({NULL:[Fecha].[Fecha].[Día].[01/06/2011]},[Measures].[Cantidad Ventas]);
[Measures].[Cantidad Ventas] = -1;
end scope;
But i get an error "arbitrary shapes are not allowed in this context"...
I've tried the IIF sentence, but neither i could.
Someone knows how to do this? ... or another way to do? .. or a way to change the 'context'...?
Thanks in advance.
May 4, 2012 at 7:13 am
Hello,
Have you made any progress on this issue yet?
Three things occur to me which may or may not work in your situation:
If you have a measure that will overwritten based on one attribute all of the time and there is never a situation prior to 1/6/2011 where the measure should be represented as it's original value (in other words, you are attempting to correct some data problem in the source system):
(1) Ask the business to fix the source data
(2) Handle it in ETL. Caution: now your data warehouse will not tie to your source...Begin QA headaches
(3) Attributize. Fix a data element somewhere which you can dimensionalize (I know, it's not a real word but it SHOULD be) as its own attribute allowing you to write a simple SCOPE. This has many benefits.
i lied... a fourth item has occurred to me:
(4) Effective Dating: I do not have enough details to recommend how you would implement effective dating in your data, but your solution should be something that you can hook dimensionally.
If these are not acceptable solutions, you could try the following, although I am skeptical that it will work and even if it does, you have at least created QA headaches for yourself and anyone supporting the application after you:
SCOPE ( { [Measures].[Cantidad Ventas] } );
This = IIF ( [Fecha].[Fecha].[Dia].[attribute name for date attribute] > '01/06/2011', -1, [Measures].[Cantidad Ventas] );
END SCOPE;
I don't know if that will work. I have never tried an inequality in MDX. I much prefer to deal in attibutes that I can affix to the lattice.
Chris Umbaugh
Data Warehouse / Business Intelligence Consultant
twitter @ToledoSQL
May 4, 2012 at 9:23 am
Chris thanks for your answer.
(1) Fix source data is not possible. Data prior to cut-off date was from a legacy system and was from another business area. Users want to see those data prior to that date, and new data after that date.
(2) I cannot alter ETL because both of the source fact tables are used by another cubes
(3) I was seeking for 'dimensionalize'...i don't understand very well, but ok, i understand you say to find out data in another attributes that enable me to make another dimension... I could search for it.
(4) Effective dating... i would not believe it works.. my fact dates are fixed and they won't ever change
I tried MDX statement but i got an #VALUE! error.
Ok, ultimately i did it in a way i did not want to do... i'll explain
I made a database view with the UNION of both tables and the CASE for managing dates. I had to change the DSV and relations on the cube.
That was the solution i found.
CREATE view [VW_SIH_VENTA_METALES_SIH_CONSUMO_OTRAS_EXISTENCIAS] as
select
fecha_movimiento,codigo_oficina,codigo_estado,codigo_denominacion,
cantidad_total, null as cantidad_consumo,
case when fecha_movimiento < '20110601' then 0 else cantidad_total end as Cantidad_Ventas,
'SIH_VENTA_METALES' as Fuente
from SIH_VENTA_METALES
union
select
FECHA,CODIGO_OFICINA,CODIGO_ESTADO,CODIGO_DENOMINACION,
null as cantidad_total, cantidad_consumo ,
case when FECHA < '20110601' then CANTIDAD_CONSUMO else 0 end as Cantidad_Ventas,
'SIH_CONSUMO_OTRAS_EXISTENCIAS' as Fuente
from SIH_CONSUMO_OTRAS_EXISTENCIAS
May 4, 2012 at 10:40 am
I was not shocked to see your response to number (1) and (4). As for number (3), all I am really saying is to add an attribute to your date dimension. This new attribute would have exactly two members. Call them whatever you want, 'Before' and 'After', whatever. You would populate every date in your date dimension table with one of these two members. Add the attribute to your date dimension in SSAS and you are ready to hook it in a SCOPE Statement like this:
SCOPE ( {[Date].[LegacyAttribute].[Before], [Measures].[MeasureName]})
This = -1
END SCOPE;
I just made up the attribute/member names....
Your solution will work just fine. I would be interested to see if anyone else has a purely MDX solution.
Chris Umbaugh
Data Warehouse / Business Intelligence Consultant
twitter @ToledoSQL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply