Showing posts with label level. Show all posts
Showing posts with label level. Show all posts

Wednesday, March 28, 2012

How do I access the lowest level data?

OK, this feels like a dumb question... but how do I access the

lowest-level data in the cube? I.E., before it is aggregated?

What I'm trying to do is find all of the rows where an amount is >

1000. However, when I create a calculated member or constrain in

any way, it compares to the aggregated amount. I have tried a few

ways, but they all seem to act only on the aggregated data.

Thoughts?

BTW, the SQL would be:

Select sum(measure), count(measure)

from fact_table

where measure > 1000

Thanks,

Doug

Just to understand your issue:

For example, you have sales at a daily level, some of which are <1000 some over 1000. When you roll these up to the week, month, etc , they aggregate as expected.For a given higher-level time period, do you want to sum the sales of all the days with sales<1000, and also count the number of these days? (eg in a given week where only two days are <1000 the sum might return 750 and the count 2)

It's not actually trivial:
The way I would do it would be to use DESCENDANTS to return a set of the lowest level members that are underneath your current member, then FILTER that set to pick out those where [measures].[sales] <1000 then finally SUM the values from that set.

This is a bit cumbersome to write, but will work. Check out BOL for the syntax, (or I'll post a code sample when I can check BOL to make sure I get the syntax right!)

Anyone knows a more efficient way of doing this I would appreciate it.

Richard R.

|||Thanks Richard. Your suggestion was definitely helpful, but

ultimately it ended up being way too slow because I need to span

hundreds of thousands of records, and to do that it essentially meant

creating the cartesian that described them! Anyway, what I've

done for now is to create another dimension that has ranges what the

sum of the measures are. I.E. sales range (0-49, 50-99, 100-499,

etc.). and amm using those to constrain. I guess this makes

sense, since it is a constraint, not a measure, but it is definitely

frustrating to have to do it this way. The major drawback is that

I have to now create a dimension attribute for every type of sale, and

combination of sales types, to be able to properly analyze the

data.

If anyone finds an easier way to do this, please let me know!

Thanks,

Doug|||

Hi again Doug,

I don't know if this is will be useful or not in real life, but I've found a feature in AS2005 which automatically clusters continuous variables into groups for use as dimensions - which is pretty much exactly what you want.

Create a new dimension using your fact table as the source, and your sales value as the dimension attribute. On the properties box of the attribute is a wonderfully-named property "DiscretizationBucketCount", and underneath is is "DiscretizationMethod".

The first determines the number of buckets the values will be separated into (eg 7 groups), the second the method (Automatic, Even-spaced, Clustered)

I tried it on a 14 million fact table row, asking it to cluster the value spend attribute into 7 buckets automatically, and the dimension processed in a few minutes (Fast P4 laptop 1GB RAM). The resulting clusters were not that elegant, as they had the decimal points on the range names (eg 0-15.79, 15.89 - 99.34), but they are good enough for the evaluation cubes I'm delivering to the customer tomorrow :-D

I'll dig a bit more and see if the names or clusters can be frigged manually. It's all XML behind the scenes, so I'm sure it can.

Anyone out there want to REALLY show off their expertise?

Regards,

Richard

Wednesday, March 21, 2012

How change name of level (All)

Hi,
I’m creating dimension with SSAS 2005 – Business Intelligence Development
Studio. Dimension has attribute hierarchies and user hierarchies.
For attribute hierarchies and user hierarchies I can define my own all
member name ( property AttributeAllMemberName for attribute hierarchies and
property AllMemberName for user hierarchies). But I can’t find a place where
I could change the name of all level. It’s name is now “(All)”. But I need
to change it. How can I do it with AMO or with BI Development studio? Please
help me.
Thanks for the answer
Dorota
you can't rename it
you want to change it to which value?
"Dorota Papiernik" <DorotaPapiernik@.discussions.microsoft.com> wrote in
message news:1B0E4BDC-BD75-4483-AB51-24F7747C87FF@.microsoft.com...
> Hi,
> I’m creating dimension with SSAS 2005 – Business Intelligence Development
> Studio. Dimension has attribute hierarchies and user hierarchies.
> For attribute hierarchies and user hierarchies I can define my own all
> member name ( property AttributeAllMemberName for attribute hierarchies
> and
> property AllMemberName for user hierarchies). But I can’t find a place
> where
> I could change the name of all level. It’s name is now “(All)”. But I
> need
> to change it. How can I do it with AMO or with BI Development studio?
> Please
> help me.
> Thanks for the answer
> Dorota
>
|||Hi,
Thank you very much for the answer. I’d like to rename it to polish name,
for example instead of “(All)” I need “Calosc”.
Thanks for the help.
Dorota Papiernik
"Jeje" wrote:
[vbcol=seagreen]
> you can't rename it
> you want to change it to which value?
> "Dorota Papiernik" <DorotaPapiernik@.discussions.microsoft.com> wrote in
> message news:1B0E4BDC-BD75-4483-AB51-24F7747C87FF@.microsoft.com...
|||well...
in fact it's never used, and the end user generally never use (All) level
directly.
the end user use the all member name you defined.
I know that few applications display "(All)" in the interface.
But it's not possible to change it today.
Log your request in the connect website as an improvment.
"Dorota Papiernik" <DorotaPapiernik@.discussions.microsoft.com> wrote in
message news:7F16FE08-B741-4148-93B4-C802467735BF@.microsoft.com...[vbcol=seagreen]
> Hi,
> Thank you very much for the answer. I’d like to rename it to polish name,
> for example instead of “(All)” I need “Calosc”.
> Thanks for the help.
> Dorota Papiernik
>
> "Jeje" wrote:
|||Hi,
In Analysis Services 2000 it was possible to change name of the level (All).
So for polish users I changed it to polish value “Calosc”. I have migrated
database of my customer to Analysis Services 2005, and I have expected that
on Analysis Services 2005 I also can change name of level (All). Now I see it
is not possible. So polish users will have to work with the name “(All)”.
Thank for help and for information once more. As you suggested, I’m going
to log my request in the connect website.
Dorota Papiernik
"Jeje" wrote:
[vbcol=seagreen]
> well...
> in fact it's never used, and the end user generally never use (All) level
> directly.
> the end user use the all member name you defined.
> I know that few applications display "(All)" in the interface.
> But it's not possible to change it today.
> Log your request in the connect website as an improvment.
>
> "Dorota Papiernik" <DorotaPapiernik@.discussions.microsoft.com> wrote in
> message news:7F16FE08-B741-4148-93B4-C802467735BF@.microsoft.com...

Monday, March 12, 2012

How can we get the aggregation information including the level of aggregations?

Hi, All experts here,

I am trying to fetch all aggregations' information including their aggregations' levels.

What I tried was typing in :<Aggregations> in the xml file of the cube, but all I can see from there is the aggregations ID and dimension ID related to the aggregations? Cant see any information like the levels of aggregations?

I am looking forward to hearing from you and thanks a lot in advance.

With best regards,

Yours sincerely,

How do you want to see this information? There was an Aggregation Manager application which was released with SP2 that presents this in a UI where you can also edit the aggregations manually (which can be dangerous if you don't know what you are doing). We also built an improved version of this application into BIDSHelper www.codeplex.com/bidshelper which lets you right click on a cube and edit it's aggretations.|||

Hi,

Yes, I have got this BIDSHelper in my SSAS2005 and I am able to right click on the cube and open up the 'Aggregation Manager' to edit the aggregations. But it does not include any aggregation levels there?

What you can do with this 'Aggregation Manager' is to 'eliminate redundancies' and 'eliminate duplicates' and add more aggregations?

But the question is: there is a strange option there which enables you to right click on the interface and 'add aggregation', but I have no clue what aggregation this is produced from here?

I am very confused now and if any of you have a clear idea about it please shed me any light on it.

Thank you very much and I am looking forward to hearing from you for your helpful advices.

With best regards,

Yours sincerely,

|||

Helen999888 wrote:

What you can do with this 'Aggregation Manager' is to 'eliminate redundancies' and 'eliminate duplicates' and add more aggregations?

But the question is: there is a strange option there which enables you to right click on the interface and 'add aggregation', but I have no clue what aggregation this is produced from here?

To your first question, yes that is all you can do. You can either get the query log to generate the design or manually create the design, this kind of leads me onto your second question. If you right click and add aggregation, it produces a blank one, where you have to check the attributes you want to use in this aggregation. With this method you will have to know which attributes are going to produce the best performance gains for your query/queries. And the only way to really do that, apart from guessing, is to look at the queries people are running and the profiler.

Hope that helps

Matt

|||

Hi, Matt,

Thanks a lot. Your advices have been really helpful for me.

With best regards,

Yours sincerely,

|||

I agree with Matt, don't go creating aggregations blindly. The best practice is to get the Wizard to build some for you at about 20-30%. Then make sure the query logging is switched on and log a period of standard user activity and the use the Usage Based Optimization wizard.

Only after this, based on evidence from profiler would you consider manually creating aggregations. Adding the wrong or too many aggregations can not only slow down processing of your cubes, the can also slow down query performance. With the original AggManager sample it was even possible to add aggregations that would produce incorrect figures (only if you had not materialized reference dimensions and we fixed this issue in the BIDSHelper version)

When you said you were after the aggregation level, were you talking about the levels in your hierarchies or about the percentage level that the aggregation wizard uses? The aggregation wizard "level" is specific to the wizard and is not stored anywhere.

|||

Hi, the leve I am talking about is the 'hierarchy' level of a dimension which is aggregated.

Thanks for your advices.

Kind regards,

How can we control the recursion in CTE?

Hi,

I would like to have some sort of control to recursive CTE, i.e. the query result should be generated level by level.

By default, it looks like this:

levels parentid, categoryid category name

--

1 0 1 aaa

2 1 100 bbb

2 1 101 ccc

2 1 102 ddd

3 102 200 xxx

3 102 201 yyy

4 201 300 zzz

5 300 400 qqq

3 101 210 ppp

....

How can I control the JOIN so that sql server query engine will process join level by level? the CTE I used as following:

with cte(levels, parentid, categoryid, title) as (

select 1,

parentid,

categoryid,

title

from categories

where parentid = 0

union all

select p.levels+1,

c.parentid,

c.categoryid,

c.title

from cte p join categories c on p.categoryid = c.parentid

)

select *

from cte

Thanks

Bill:

If what you want is just to sort the data by level, a simple order by will do -- something like:

declare @.categories table
( levels int,
parentId int,
categoryId int,
title varchar(10)
)
insert into @.categories
select 1,0,1,'aaa' union all
select 2,1,100,'bbb' union all
select 2,1,101,'ccc' union all
select 2,1,102,'ddd' union all
select 3,102,200,'xxx' union all
select 3,102,201,'yyy' union all
select 4,201,300,'zzz' union all
select 5,300,400,'qqq' union all
select 3,101,210,'ppp'

;with cte(levels, parentid, categoryid, title) as (
select 1,
parentid,
categoryid,
title
from @.categories
where parentid = 0
union all
select p.levels+1,
c.parentid,
c.categoryid,
c.title
from cte p join @.categories c on p.categoryid = c.parentid
)
select *
from cte order by levels, categoryId

/*
levels parentid categoryid title
-- -- -- -
1 0 1 aaa
2 1 100 bbb
2 1 101 ccc
2 1 102 ddd
3 102 200 xxx
3 102 201 yyy
3 101 210 ppp
4 201 300 zzz
5 300 400 qqq
*/

However, now that I think about it, I think you are asking a different question; hold on and I will get a second example.

|||

What do you mean "the query result should be generated level by level." There is never any guarantee what the result will look like unless you order it. What order do you want to achieve? Based on your generated level by level question, I would guess:

...

select *

from cte

order by levels

|||

My knee-jerk reaction to the question was the same as Louis' reaction. What I often find is that this question is intended to ask how do you display the data in tree order. That is a very different proposition. Is this more like what you are looking for:


;with cte(levels, parentid, categoryid, tree, title) as (
select 1,
parentid,
categoryid,
cast(str(categoryId, 11) as varchar(120)),
title
from @.categories
where parentid = 0
union all
select p.levels+1,
c.parentid,
c.categoryid,
cast(tree + '/' + str(c.categoryId, 11) as varchar(120)),
c.title
from cte p join @.categories c on p.categoryid = c.parentid
)
select levels,
parentId,
categoryId,
title,
replace(tree, ' ', '') as tree
from cte order by tree

/*
levels parentId categoryId title tree
-- -- -- - -
1 0 1 aaa 1
2 1 100 bbb 1/100
2 1 101 ccc 1/101
3 101 210 ppp 1/101/210
2 1 102 ddd 1/102
3 102 200 xxx 1/102/200
3 102 201 yyy 1/102/201
4 201 300 zzz 1/102/201/300
5 300 400 qqq 1/102/201/300/400
*/

|||

Thanks Kent/Louis,

Please refer to this thread: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=473895&SiteID=17.
Euan's codes work perfect for a small table, but mine is pretty large, I want the CTE returning me maximum 20 levels of result, I did use [OPTION (MAXRECURSION 20)] in my query, but because the statement will terminate first when it reaches maximum levels, in this case, ORDER BY won't work.

My result set looks like this:

Levels Pathid Paths

1 96 40885>96
1 98 40885>98
....
1 60532 40885>60532
2 193 40885>60532>193
2 194 40885>60532>194
....
2 60524 40885>60532>60524
3 698 40885>60532>60524>698
3 915 40885>60532>60524>915
...
3 60527 40885>60532>60524>60527
4 3068 40885>60532>60524>60527>3068
4 4022 40885>60532>60524>60527>4022
...
4 60530 40885>60532>60524>60527>60530
5 10760 40885>60532>60524>60527>60530>10760
5 20365 40885>60532>60524>60527>60530>20365
...

please note, above pattern is very interesting, what I really want here is: I'd like to have the next level results are all from previous level, NOT just last record.

Thanks

|||

Did Kent's query answer you with the path? It seems to be the same (other than the fact that you are not returning the root node). A query like that was going to be my next suggeston.

Instead of MAXRECURSION, you can also limit the level value in the where clause to make it stop too:

;with cte(levels, parentid, categoryid, tree, title) as (
select 1,
parentid,
categoryid,
cast(str(categoryId, 11) as varchar(120)),
title
from @.categories
where parentid = 0
union all
select p.levels+1,
c.parentid,
c.categoryid,
cast(tree + '/' + str(c.categoryId, 11) as varchar(120)),
c.title
from cte p join @.categories c on p.categoryid = c.parentid
where p.levels < 2 --this will get level one and two only (because p.levels + 1)
)
select levels,
parentId,
categoryId,
title,
replace(tree, ' ', '') as tree
from cte
order by tree

levels parentId categoryId title tree

-- -- -- - --

1 0 1 aaa 1

2 1 100 bbb 1/100

2 1 101 ccc 1/101

2 1 102 ddd 1/102

|||

Thanks Louis , that works

Bill