Dimension question

I asked this question in the MS OLAP and the SQL group but got no
response. I am
hoping somebody will have some answers here.
Thanks in advance
HI,
I need to create a dimension that will play off another dimension. For
example: lets say we have branches going into discontinued operations
time to time. What we would like to create is a dimension that will
have the Discontinued status with Year. So something like:
2007 Discontinued
2006 Discontinued
2005 Discontinued
By clicking on each of those members we can effect a 2nd dimension
called Branch which will have its own parent child rollup for example:
United Kingdom
Birmingham
London
USA
New York
Dallas
My question is what is the best way to design this. Thanks for all
your help.
I'll bite.
* This only works if the Branch dimension isn't a true parent-child
hierarchy. *
You need to modify the Branch dimension by adding a Status attribute.
Each branch must be tagged with: "Active", "2007 Discontinued", "2006
Discontinued"... you get the idea.
Your branch dimension has a regular hierarchy without Status, i.e.
Country > City > Location, that people currently use. Add a new
hierarchy named "Branches by Status" or something, and build this one
as Status > Country > City > Location. If users want to navigate
starting at Status, they use that instead.
If the Branch dimension is a parent-child, then why? Parent-child
isn't a good idea design-wise because you lose context - SSAS doesn't
know that the USA node is a country, and if someone asks you for a
list of countries, you can't give it to them. There's also the small
issue of poor support in SSAS - which we've been grappling with for
almost a decade.
The only time you functionally need a parent-child is if you need
unary operators for rollups. I look forward to the day when unary
operators are available for normal hierarchies.
Otherwise, put in the time to un-ragged your ragged hierarchy. In my
experience, designers are lazy and don't want to do the hard schema
and ETL work to normalize a ragged hierarchy, and reason away their
laziness with the notion that "parent-child is flexible". You lose a
lot for that supposed flexibility.
On Jan 18, 2:45 pm, Mark T <vwttra...@.hotmail.com> wrote:
> I asked this question in the MS OLAP and the SQL group but got no
> response. I am
> hoping somebody will have some answers here.
> Thanks in advance
> --
> HI,
> I need to create a dimension that will play off another dimension. For
> example: lets say we have branches going into discontinued operations
> time to time. What we would like to create is a dimension that will
> have the Discontinued status with Year. So something like:
> 2007 Discontinued
> 2006 Discontinued
> 2005 Discontinued
> By clicking on each of those members we can effect a 2nd dimension
> called Branch which will have its own parent child rollup for example:
> United Kingdom
> Birmingham
> London
> USA
> New York
> Dallas
> My question is what is the best way to design this. Thanks for all
> your help.
|||On Jan 18, 1:04 pm, entaroadun <johnny.c.k...@.gmail.com> wrote:[vbcol=seagreen]
> I'll bite.
> * This only works if the Branch dimension isn't a true parent-child
> hierarchy. *
> You need to modify the Branch dimension by adding a Status attribute.
> Each branch must be tagged with: "Active", "2007 Discontinued", "2006
> Discontinued"... you get the idea.
> Your branch dimension has a regular hierarchy without Status, i.e.
> Country > City > Location, that people currently use. Add a new
> hierarchy named "Branches by Status" or something, and build this one
> as Status > Country > City > Location. If users want to navigate
> starting at Status, they use that instead.
> If the Branch dimension is a parent-child, then why? Parent-child
> isn't a good idea design-wise because you lose context - SSAS doesn't
> know that the USA node is a country, and if someone asks you for a
> list of countries, you can't give it to them. There's also the small
> issue of poor support in SSAS - which we've been grappling with for
> almost a decade.
> The only time you functionally need a parent-child is if you need
> unary operators for rollups. I look forward to the day when unary
> operators are available for normal hierarchies.
> Otherwise, put in the time to un-ragged your ragged hierarchy. In my
> experience, designers are lazy and don't want to do the hard schema
> and ETL work to normalize a ragged hierarchy, and reason away their
> laziness with the notion that "parent-child is flexible". You lose a
> lot for that supposed flexibility.
> On Jan 18, 2:45 pm, Mark T <vwttra...@.hotmail.com> wrote:
>
>
Thanks for your reply. Unfortunately, i cant move away from the parent-
child relationship. The issue is all of our branch dimensions are
based on this and at least for now I need to follow the design
methodology that we have in place. So, i dont think this solution
would work... though i wish we didnt have to worry about the parent-
child issue. I did create multiple rollups somewhat similar to what
you defined:
1990 - Total Company
1991 - Total Company
1992 - Total Company
...and so on. These rollups were parent child but you can see the
problem... This list gets too long and cumbersome.

Dimension Filter in MDX

Hi guys,

I need to create a report using data from our OLAP. I'm using a MDX query to do it, but I founded some limitations that I want to know if its real or just my mistakes. I have a the following dimensions

- "Projects", that have Project Name and Company name

- "Company", that have my company name too

- "Months", that have the months and years about projects

If I'm developing a sql query, its seems like this:

select * from Projects p, Months m

where p.company in ('A', 'B', 'C')

and m.Year = 2007

But when I'm using MDX queries, I need the same flexibility to filter more than one data (like the IN at the sql query above) from the dimension that I'm looking for (like filter p.company from my PRojects tables)

Here is the sample of my code. If you look inside it, I'm filtering the best I can in the mdx query and after, I'm filtering better using T-sql. Its works fine, but its expensive and I need to get better performance. I want to filter my company using the project dimension and not company dimension. I'm also want to filter the Month dimension at my mdx query, but its says that I cant because I'm using it as a row member.

SELECT

"[Measures].[codProject]" as codProj,

"[Measures].[Month]" asMonth,

isnull("[Measures].[Valor Total]",0)as vlrTotal

FROM

OPENQUERY( DW2007,''

with

member [Measures].[codProject] as'[Project].Properties("key")'

member [Measures].[Month] as'[Month].Properties("key")'

SELECT

NON EMPTY { [Measures].[Valor Total], [Measures].[codProject], [Measures].[Month] }ON COLUMNS,

non empty {[Project].[Project].Members * [Month].[Month].MEMBERS }ON ROWS

FROM [Budget]

where( [Company].['+ rtrim(@.nmCompany) +'], [Year].['+ cast(@.Year as char(4)) +'] )

'')

wherecast("[Project].[Company].[member_caption]" as varchar(10))='''+ rtrim(@.nmCompany) +'''

andcast("[Month].[Year].[MEMBER_CAPTION]" aschar(4))=' + cast(@.Year as char(4)) + ''

Can anyone help me ?

Best Regards,

Tritech - Consultoria e Desenvolvimento de Solu??es em TI

I think the problem is that the "non empty" clauses are not really doing much as the two calculated measures that get the key properties will return a non empty value for every month and project. Something like the following, where I have used the iif() function to only return a value. Possibly even excluding these measures from the MDX query and then joining back to the dimension table in T-SQL might be faster.

SELECT

"[Measures].[codProject]" as codProj,

"[Measures].[Month]" asMonth,

isnull("[Measures].[Valor Total]",0)as vlrTotal

FROM

OPENQUERY( DW2007,''

with

member [Measures].[codProject] as'iif(IsEmpty(Measures.[Valor Total]),NULL,[Project].Properties("key"))'

member [Measures].[Month] as'iif(IsEmpty(Measures.[Valor Total]),NULL,[Month].Properties("key"))'

SELECT

NON EMPTY { [Measures].[Valor Total], [Measures].[codProject], [Measures].[Month] }ON COLUMNS,

non empty {[Project].[Project].Members * [Month].[Month].MEMBERS }ON ROWS

FROM [Budget]

where( [Company].['+ rtrim(@.nmCompany) +'], [Year].['+ cast(@.Year as char(4)) +'] )

'')

wherecast("[Project].[Company].[member_caption]" as varchar(10))='''+ rtrim(@.nmCompany) +'''

andcast("[Month].[Year].[MEMBER_CAPTION]" aschar(4))=' + cast(@.Year as char(4)) + ''

|||

Hi Darren,

Tks for your reply, but I want to know if can I put my sql where clause into the mdx query ? If I can, will be faster and solve many problems for me. I look in some place, that you can select a "cube", using a select like one. as its show in the folowing query that I get from Microsoft Team Foundation Server reports. Here its use a select with another, but using parameter, anyone know if can I do this with queries and dimensions ?

SELECT
NON EMPTY {
[Measures].[Current Work Item Microsoft_VSTS_Scheduling_BaselineWork], [Measures].[Current Work Item Microsoft_VSTS_Scheduling_CompletedWork], [Measures].[Current Work Item Microsoft_VSTS_Scheduling_RemainingWork] } ON COLUMNS,
NON EMPTY {
([Assigned To].[Person].[Person].ALLMEMBERS * [Work Item].[System_Title].[System_Title].ALLMEMBERS * [Work Item].[System_State].[System_State].ALLMEMBERS )
}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM (
SELECT
( STRTOSET(@.MicrosoftVSTSSchedulingFinishDateYearMonthDate, CONSTRAINED) ) ON COLUMNS
FROM (
SELECT ( STRTOSET(@.MicrosoftVSTSSchedulingStartDateYearMonthDate, CONSTRAINED) ) ON COLUMNS
FROM (
SELECT ( STRTOSET(@.AssignedToPerson, CONSTRAINED) ) ON COLUMNS
FROM (
SELECT ( STRTOSET(@.TeamProjectTeamProject, CONSTRAINED) ) ON COLUMNS
FROM [Team System]
)
)
)
)
WHERE ( IIF( STRTOSET(@.TeamProjectTeamProject, CONSTRAINED).Count = 1,
STRTOSET(@.TeamProjectTeamProject, CONSTRAINED),
[Team Project].[Team Project].currentmember ),
IIF( STRTOSET(@.MicrosoftVSTSSchedulingStartDateYearMonthDate, CONSTRAINED).Count = 1,
STRTOSET(@.MicrosoftVSTSSchedulingStartDateYearMonthDate, CONSTRAINED), [Microsoft_VSTS_Scheduling_StartDate].[Year Month Date].currentmember ), IIF( STRTOSET(@.MicrosoftVSTSSchedulingFinishDateYearMonthDate, CONSTRAINED).Count = 1, STRTOSET(@.MicrosoftVSTSSchedulingFinishDateYearMonthDate, CONSTRAINED), [Microsoft_VSTS_Scheduling_FinishDate].[Year Month Date].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

|||

hi Darrel,

I copy the query that you changed but doesnt work, but after I make some changes, its works. This make sense ? tks

BEFORE CHANGES

with

member [Measures].[codProject] as'iif(IsEmpty(Measures.[Valor Total]),NULL,[Project].Properties("key"))'

AFTER

with

member [Measures].[ProjectCode] as '[Project].Properties("key")'

member [Measures].[codProject] as'iif(IsEmpty(Measures.[Valor Total]),NULL, Measures.[ProjectCode] )'

Tritech Informática
Brasilia
Brasilsql

Different results when updating a cube

I've updated an OLAP cube and I can see the new cell value in my report.

But I was surprised when I tried to execute a MDX against the changes I made and compared it with the data of my cube in the reportin-Services.

I updated a cube cell with the value of 1

The report showed - in fact - 1; but the MDX-query got 0.99999 back.

Why is their a different?

Any idea?

Florian

If the cell you updated was not right down at the leaf level, then AS would have split up the value you were allocating across all the leaf level cells under the one that you updated. My guess is that this has most likely resulted in a small rounding error. The report is probably just set to format to a given number of decimal places an is rounding up.