Group members of a derived dimension

Hi, Have created a dimension based on a column in the FACT, called Age as given in the post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=199100&SiteID=1) . Now, I need to group these members in custom buckets. The 'aggregate*' property is not allowing for custom buckets. Since this is a derived dimension, it is not appearing in the list of dimensions under the 'Create Custom Member formula' under 'Add BI' feature. How to accomplish this?

Secondly, If I try to create a calculated member on this dimension, why should it be attached under an existing attribute? So, even this route didnt work.

Thanks ina dvance,

If you followed the steps in that post, what you created in the fact table with your case statement is a set of derived surrogate keys. You would then link these keys to a dimension created from a table/view in your dsv. In this dimension table you could then add an extra column for a grouping and specify the group for each member.

GROUP BY messes up results of view based on UDF !?!

Hi there,
we have a rather strange effect here were a group by on a view does not
return the expected results. We managed to nail it down to the fact that the
view is based on multiple fields being the result of the same User Defined
Function, but with different arguments. The error seems consistent and in
the example below you can easily see how it fails on the simpelest udf/view
on the pubs db.
Anyone can explain this ' Or better, tell us how to work around it ' (we
may have several situations in our application where this goes wrong, ly
we only just found out)
Thanks.
Cu
Roby
An example : (Pubs db)
DROP FUNCTION dbo.fn_to_upper_or_lower
GO
CREATE FUNCTION dbo.fn_to_upper_or_lower(@.string1 varchar(1024),
@.int1 int) -- 0 = UPPER, 1 =
lower
RETURNS varchar(1024)
AS
BEGIN
DECLARE @.result varchar(1024)
IF @.int1 = 0
BEGIN
SELECT @.result = Upper(@.string1)
END
ELSE
BEGIN
SELECT @.result = Lower(@.string1)
END
Return(@.result)
END
GO
-- SELECT dbo.fn_to_upper_or_lower('hello', 0),
-- dbo.fn_to_upper_or_lower('hello', 1)
-- GO
DROP VIEW test
GO
CREATE VIEW test
AS
SELECT title_id,
pub_id,
title,
notes,
upper_case = dbo.fn_to_upper_or_lower (title, 0),
lower_case = dbo.fn_to_upper_or_lower (title, 1)
FROM titles
GO
SELECT info = 'Without GROUP BY', title_id, title, upper_case, lower_case,
notes
FROM test
SELECT info = 'With GROUP BY', title_id, title, upper_case, lower_case,
notes
FROM test
GROUP BY title_id, title, upper_case, lower_case, notesLooks like a bug. I reported it and will be back when I have any info.
Tested on SQL2K Dev/SP3.
Bug seems to be fixed in Yukon (tested on CTP2).
BG, SQL Server MVP
www.SolidQualityLearning.com
"deroby" <deroby@.discussions.microsoft.com> wrote in message
news:3C4C4B2B-0646-4D24-9388-F96613CC3A1B@.microsoft.com...
> Hi there,
> we have a rather strange effect here were a group by on a view does not
> return the expected results. We managed to nail it down to the fact that
> the
> view is based on multiple fields being the result of the same User Defined
> Function, but with different arguments. The error seems consistent and in
> the example below you can easily see how it fails on the simpelest
> udf/view
> on the pubs db.
> Anyone can explain this ' Or better, tell us how to work around it ' (we
> may have several situations in our application where this goes wrong,
> ly
> we only just found out)
> Thanks.
> Cu
> Roby
> --
> An example : (Pubs db)
> DROP FUNCTION dbo.fn_to_upper_or_lower
> GO
> CREATE FUNCTION dbo.fn_to_upper_or_lower(@.string1 varchar(1024),
> @.int1 int) -- 0 = UPPER, 1 =
> lower
> RETURNS varchar(1024)
> AS
> BEGIN
> DECLARE @.result varchar(1024)
> IF @.int1 = 0
> BEGIN
> SELECT @.result = Upper(@.string1)
> END
> ELSE
> BEGIN
> SELECT @.result = Lower(@.string1)
> END
> Return(@.result)
> END
> GO
> -- SELECT dbo.fn_to_upper_or_lower('hello', 0),
> -- dbo.fn_to_upper_or_lower('hello', 1)
> -- GO
> DROP VIEW test
> GO
> CREATE VIEW test
> AS
> SELECT title_id,
> pub_id,
> title,
> notes,
> upper_case = dbo.fn_to_upper_or_lower (title, 0),
> lower_case = dbo.fn_to_upper_or_lower (title, 1)
> FROM titles
> GO
>
> SELECT info = 'Without GROUP BY', title_id, title, upper_case, lower_case,
> notes
> FROM test
> SELECT info = 'With GROUP BY', title_id, title, upper_case, lower_case,
> notes
> FROM test
> GROUP BY title_id, title, upper_case, lower_case, notes
>
>
>|||Roby,
This is a known bug. See this thread for details and some suggested
workarounds.
http://groups.google.co.uk/groups?h...&q=kryszak+kass
The bug occurs only in very restricted situations, so a workaround is
usually possible.
Steve Kass
Drew University
deroby wrote:

>Hi there,
>we have a rather strange effect here were a group by on a view does not
>return the expected results. We managed to nail it down to the fact that th
e
>view is based on multiple fields being the result of the same User Defined
>Function, but with different arguments. The error seems consistent and in
>the example below you can easily see how it fails on the simpelest udf/view
>on the pubs db.
>Anyone can explain this ' Or better, tell us how to work around it ' (we
>may have several situations in our application where this goes wrong, ly
>we only just found out)
>Thanks.
>Cu
>Roby
>--
>An example : (Pubs db)
>DROP FUNCTION dbo.fn_to_upper_or_lower
>GO
>CREATE FUNCTION dbo.fn_to_upper_or_lower(@.string1 varchar(1024),
> @.int1 int) -- 0 = UPPER, 1 =
>lower
>RETURNS varchar(1024)
>AS
>BEGIN
> DECLARE @.result varchar(1024)
> IF @.int1 = 0
> BEGIN
> SELECT @.result = Upper(@.string1)
> END
> ELSE
> BEGIN
> SELECT @.result = Lower(@.string1)
> END
> Return(@.result)
>END
>GO
>-- SELECT dbo.fn_to_upper_or_lower('hello', 0),
>-- dbo.fn_to_upper_or_lower('hello', 1)
>-- GO
>DROP VIEW test
>GO
>CREATE VIEW test
>AS
>SELECT title_id,
> pub_id,
> title,
> notes,
> upper_case = dbo.fn_to_upper_or_lower (title, 0),
> lower_case = dbo.fn_to_upper_or_lower (title, 1)
> FROM titles
>GO
>
>SELECT info = 'Without GROUP BY', title_id, title, upper_case, lower_case,
>notes
> FROM test
>SELECT info = 'With GROUP BY', title_id, title, upper_case, lower_case,
>notes
> FROM test
> GROUP BY title_id, title, upper_case, lower_case, notes
>
>
>
>|||Roby,
Also see http://support.microsoft.com/kb/883415.
SK
deroby wrote:

>Hi there,
>we have a rather strange effect here were a group by on a view does not
>return the expected results. We managed to nail it down to the fact that th
e
>view is based on multiple fields being the result of the same User Defined
>Function, but with different arguments. The error seems consistent and in
>the example below you can easily see how it fails on the simpelest udf/view
>on the pubs db.
>Anyone can explain this ' Or better, tell us how to work around it ' (we
>may have several situations in our application where this goes wrong, ly
>we only just found out)
>Thanks.
>Cu
>Roby
>--
>An example : (Pubs db)
>DROP FUNCTION dbo.fn_to_upper_or_lower
>GO
>CREATE FUNCTION dbo.fn_to_upper_or_lower(@.string1 varchar(1024),
> @.int1 int) -- 0 = UPPER, 1 =
>lower
>RETURNS varchar(1024)
>AS
>BEGIN
> DECLARE @.result varchar(1024)
> IF @.int1 = 0
> BEGIN
> SELECT @.result = Upper(@.string1)
> END
> ELSE
> BEGIN
> SELECT @.result = Lower(@.string1)
> END
> Return(@.result)
>END
>GO
>-- SELECT dbo.fn_to_upper_or_lower('hello', 0),
>-- dbo.fn_to_upper_or_lower('hello', 1)
>-- GO
>DROP VIEW test
>GO
>CREATE VIEW test
>AS
>SELECT title_id,
> pub_id,
> title,
> notes,
> upper_case = dbo.fn_to_upper_or_lower (title, 0),
> lower_case = dbo.fn_to_upper_or_lower (title, 1)
> FROM titles
>GO
>
>SELECT info = 'Without GROUP BY', title_id, title, upper_case, lower_case,
>notes
> FROM test
>SELECT info = 'With GROUP BY', title_id, title, upper_case, lower_case,
>notes
> FROM test
> GROUP BY title_id, title, upper_case, lower_case, notes
>
>
>
>|||Thx for the replies guys.
Bit strange to find this has been solved quite a while ago but still is in a
hotfix that only the happy few can get. And then still they make it sound as
if you'd rather prefer not to insall it all...
We'll have a look at the workarounds, they seem do-able, but it sure is a
pain =( I guess optimizers aren't always a developpers best friend...
Cu
Roby
"Steve Kass" wrote:

> Roby,
> Also see http://support.microsoft.com/kb/883415.
> SK
> deroby wrote:
>
>

group by

Hi.

I have read lots, but obviously not the right stuff.
A reference pointer would be fine, or an answer. I in fact got an oracle
solution that I am still researching, but was wondering about either a generic
or an ms sql server solution?

I'd like to use sql to find out:

given a metadata table
where object is a table name
attribute is a col name,
keyseq is if the attribute is
part of a primary key

AND

given (by rules, not db design) that you should only have a single attribute
with a given non null value in keyseq

AND

table metatable(object varchar2, attribute varchar2, keyseq int)
table1, col1, 1
table1, col2, 2
table1, col3, null
table1, col4, null
table2, col1, 1
table2, col2, 1
table2, col3, null
table2, col4, null
table3, col1, 1
table3, col2, 2
table3, col3, null
table3, col4, null

I'd like to easily find out what tables have more than one attribute for any
keyseq that is not null, and which attribute it was with the problem.

Is it possible?

I started off with select object, attribute, keyseq from metatable
where keyseq is not null, and got a few thousand rows.

Then I tried a count(*) and group by (I wish I knew this aggregate stuff
better) like this:

select object, keyseq, count(keyseq) from metatable
where keyseq is not null
group by object, keycolseq
order by (count(keyseq))

and got a few thousand rows, where the very last row told me the object that
had more than one attribute with a single keyseq value.

So yes, I can figure it out, but I was hoping/wondering if there was a better
way, using just sql, that would return what object/attribute had the
'violation'.

I thought I might be able to use the above query in some sort of
subquery/exists combination, but I'm at a loss.

Some helpful person (thanks m cadot) in the Oracle group gave me some
guidance and made an initial suggestion which I show here, and a more advanced
solution I'm not showing because I don't understand it, and it might be oracle
specific.

select object, keyseq, count(keyseq)
from metatable
where keyseq is not null
group by object, keyseq
having count(*) > 1 --<-- just this line to add
order by count(keyseq)

thanks for your time and knowledge.

Thanks
Jeff Kish
Jeff KishThis should work. WHERE limits which cases go into the tally.
HAVING limits which results are output after the tally is completed.

In this case, it limits the output to those object-keyseq combinations
where the count is greater than one, which I believe is what you're
after.

> Some helpful person (thanks m cadot) in the Oracle group gave me some
> guidance and made an initial suggestion which I show here, and a more advanced
> solution I'm not showing because I don't understand it, and it might be oracle
> specific.
> select object, keyseq, count(keyseq)
> from metatable
> where keyseq is not null
> group by object, keyseq
> having count(*) > 1 --<-- just this line to add
> order by count(keyseq)|||Jeff Kish (jeff.kish@.mro.com) writes:
> Some helpful person (thanks m cadot) in the Oracle group gave me some
> guidance and made an initial suggestion which I show here, and a more
> advanced solution I'm not showing because I don't understand it, and it
> might be oracle specific.
> select object, keyseq, count(keyseq)
> from metatable
> where keyseq is not null
> group by object, keyseq
> having count(*) > 1 --<-- just this line to add
> order by count(keyseq)

It can't be any more standard SQL than this. The above SELECT should
run on any RDBMS that supports SQL.

HAVING is like WHERE, but is applied after the GROUP BY, and thus
permits filters with aggregate functions.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Mon, 19 Jun 2006 21:58:38 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

>Jeff Kish (jeff.kish@.mro.com) writes:
>> Some helpful person (thanks m cadot) in the Oracle group gave me some
>> guidance and made an initial suggestion which I show here, and a more
>> advanced solution I'm not showing because I don't understand it, and it
>> might be oracle specific.
>>
>> select object, keyseq, count(keyseq)
>> from metatable
>> where keyseq is not null
>> group by object, keyseq
>> having count(*) > 1 --<-- just this line to add
>> order by count(keyseq)
>It can't be any more standard SQL than this. The above SELECT should
>run on any RDBMS that supports SQL.
>HAVING is like WHERE, but is applied after the GROUP BY, and thus
>permits filters with aggregate functions.
How would I add the attribute column so I know exactly which ones are
of interest? That was what is the most difficult thing for me to
figure out.

Thanks|||Jeff Kish (kishjjrjj@.charter.net) writes:
> How would I add the attribute column so I know exactly which ones are
> of interest? That was what is the most difficult thing for me to
> figure out.

Not sure that I understood your original question, but try:

SELECT a.object, a.attribute, a.keyseq
FROM metatable a
JOIN (SELECT object, keyseq
FROM metatable
WHERE keyseq IS NOT NULL
GROUP BY object, keyseq
HAVING COUNT(*) > 1) AS b ON a.object = b.object
AND a.keyseq = b.keyseq

The thing in parenthesis is a derived table. You could think of a
derived table as a temp table within the query, but never materialised.
Or even computed, the optimizer may recasts the computation order
to get performance. This is a great tool to write complex queries
effeciently.

And, yes, this is ANSI-SQL that should run on most RDBMS. (It's not
as vintage as HAVING though.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Tue, 20 Jun 2006 22:04:41 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

>Jeff Kish (kishjjrjj@.charter.net) writes:
>> How would I add the attribute column so I know exactly which ones are
>> of interest? That was what is the most difficult thing for me to
>> figure out.
>Not sure that I understood your original question, but try:
> SELECT a.object, a.attribute, a.keyseq
> FROM metatable a
> JOIN (SELECT object, keyseq
> FROM metatable
> WHERE keyseq IS NOT NULL
> GROUP BY object, keyseq
> HAVING COUNT(*) > 1) AS b ON a.object = b.object
> AND a.keyseq = b.keyseq
>The thing in parenthesis is a derived table. You could think of a
>derived table as a temp table within the query, but never materialised.
>Or even computed, the optimizer may recasts the computation order
>to get performance. This is a great tool to write complex queries
>effeciently.
>And, yes, this is ANSI-SQL that should run on most RDBMS. (It's not
>as vintage as HAVING though.)
Wow.
I'll have to study up some more on the 'ON' and 'AS' syntax.

Thanks so much Erland.

I appreciate both the answer and the education.
kind regards|||Jeff Kish (kishjjrjj@.charter.net) writes:
> Wow.
> I'll have to study up some more on the 'ON' and 'AS' syntax.

The AS is optional and is only for defining an alias for the derived
table. The alias is always mandatory, though.

The ON is part of the newer ANSI syntax for joins which was introduced to
handle left joins, but once you have get used to it, you will use it for
all you joins, because it makes the queries so much clearer.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Wed, 21 Jun 2006 06:56:24 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

>Jeff Kish (kishjjrjj@.charter.net) writes:
>> Wow.
>> I'll have to study up some more on the 'ON' and 'AS' syntax.
>The AS is optional and is only for defining an alias for the derived
>table. The alias is always mandatory, though.
>The ON is part of the newer ANSI syntax for joins which was introduced to
>handle left joins, but once you have get used to it, you will use it for
>all you joins, because it makes the queries so much clearer.
study study study.. it never ends...
Thanks again. Bonus that it works for sql server 2000 as well, I imagine, the
newer version.
Regards
Jeff Kish