get a value from a measure

I want to create a new calculated member in a virtul cube.
I have a dimension Time with 3 levels Year Month and Day and a Measure ValSa
l.
I want to get a value from the measure ValSal on day 2004/05/25, but i don't
know how?
With this value i want to fill the calculated member.
Any help is appreciated.
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...Try ([Measures].[ValSal],[Time].&[2005].&[5].&[25])
, if your dimension
[Time] is Year-month-day
wrote in message news:epEgjy4UEHA.212@.TK2MSFTNGP12.phx.gbl...
> I want to create a new calculated member in a virtul cube.
> I have a dimension Time with 3 levels Year Month and Day and a Measure
ValSal.
> I want to get a value from the measure ValSal on day 2004/05/25, but i
don't know how?
> With this value i want to fill the calculated member.
>
> Any help is appreciated.
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...

get a value from a measure

I want to create a new calculated member in a virtul cube.
I have a dimension Time with 3 levels Year Month and Day and a Measure ValSal.
I want to get a value from the measure ValSal on day 2004/05/25, but i don't know how?
With this value i want to fill the calculated member.
Any help is appreciated.
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
Try ([Measures].[ValSal],[Time].&[2005].&[5].&[25]), if your dimension
[Time] is Year-month-day
wrote in message news:epEgjy4UEHA.212@.TK2MSFTNGP12.phx.gbl...
> I want to create a new calculated member in a virtul cube.
> I have a dimension Time with 3 levels Year Month and Day and a Measure
ValSal.
> I want to get a value from the measure ValSal on day 2004/05/25, but i
don't know how?
> With this value i want to fill the calculated member.
>
> Any help is appreciated.
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...
sql

Generating combinations (cells of a multidimensional cube?)

Hi,
(Apologies if I've posted this to the wrong group.)
I have a table of Categories and Category Items, and need to generate
every possible combination, e.g. I have:
CategoryID CategoryItemID
-- --
1 1
1 2
2 1
2 2
2 3
2 4
3 1
3 2
3 3
and I want to generate:
CellID CategoryID CategoryItemID
-- -- --
1 1 1
1 2 1
1 3 1
2 1 1
2 2 1
2 3 2
...
24 1 2
24 2 4
24 3 3
I *could* write a stored proc that iterates over each item in turn to
build up the results set, but it's clunky and inelegant - is there a
smarter way to do this?
Thanks in advance,
NateHi Nate,
You can use following logic to generate every possible combination but
couldnt understand your example.
See if this help.
create table #temptab
(
CategoryID int,
CategoryItemID int
)
insert into #temptab values(1, 1)
insert into #temptab values(1, 2)
insert into #temptab values(2, 1)
insert into #temptab values(2, 2)
insert into #temptab values(3, 1)
insert into #temptab values(3, 2)
insert into #temptab values(3, 3)
select
*
from
(select distinct CategoryID from #temptab) a
cross join (select distinct CategoryItemID from #temptab) b
Regards,
Lakshman.|||Lakshman, thanks for replying.
Sorry, I've not been clear enough. Say I have as input:
Category CategoryItem
-- --
Fruit Apple
Fruit Orange
Car Ford
Car Vauxhall
Car Rover
Car Fiat
Animal Cat
Animal Dog
Animal Tortoise
I need to generate all the possible combinations of the 3 categories:
Comb. # Category CategoryItem
-- -- --
1 Fruit Apple
1 Car Ford
1 Animal Cat
2 Fruit Apple
2 Car Ford
2 Animal Dog
...
24 Fruit Orange
24 Car Fiat
24 Animal Tortoise
Is that any clearer?
(Of course, I need to be able to extend this to a non-fixed number of
categories)|||If this doesn't do what you want, then please see my signature so you can
understand why good specs are better than word problems.
You might also consider storing these in separate tables instead of trying
to partially relate them, if there really is no relevance to the
relationship...
USE TempDB
GO
CREATE TABLE dbo.Categories
(
CategoryID INT,
CategoryItemID INT
)
GO
SET NOCOUNT ON
INSERT dbo.Categories SELECT 1, 1
INSERT dbo.Categories SELECT 1, 2
INSERT dbo.Categories SELECT 2, 1
INSERT dbo.Categories SELECT 2, 2
INSERT dbo.Categories SELECT 2, 4
INSERT dbo.Categories SELECT 3, 1
INSERT dbo.Categories SELECT 3, 2
INSERT dbo.Categories SELECT 3, 3
GO
SELECT c1.CategoryID, c2.CategoryItemID
FROM dbo.Categories c1
FULL OUTER JOIN
dbo.Categories c2
ON c1.CategoryID > 0
GROUP BY c1.CategoryID, c2.CategoryItemID
ORDER BY 1, 2
GO
DROP TABLE dbo.Categories
GO
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Nate" <spamthecan@.gmail.com> wrote in message
news:1115389621.362575.318720@.o13g2000cwo.googlegroups.com...
> Lakshman, thanks for replying.
> Sorry, I've not been clear enough. Say I have as input:
> Category CategoryItem
> -- --
> Fruit Apple
> Fruit Orange
> Car Ford
> Car Vauxhall
> Car Rover
> Car Fiat
> Animal Cat
> Animal Dog
> Animal Tortoise
> I need to generate all the possible combinations of the 3 categories:
> Comb. # Category CategoryItem
> -- -- --
> 1 Fruit Apple
> 1 Car Ford
> 1 Animal Cat
> 2 Fruit Apple
> 2 Car Ford
> 2 Animal Dog
> ...
> 24 Fruit Orange
> 24 Car Fiat
> 24 Animal Tortoise|||Hi Nate,
This example is better than earlier one.
I was able to generate all combinations (72 rows) but i should see
whehter it is possiable to add a comb#.
Let you know if i could find.
Script:
create table #temptab
(
CategoryID int,
CategoryItemID int
)
insert into #temptab values(1, 1)
insert into #temptab values(1, 2)
insert into #temptab values(2, 1)
insert into #temptab values(2, 2)
insert into #temptab values(2, 3)
insert into #temptab values(2, 4)
insert into #temptab values(3, 1)
insert into #temptab values(3, 2)
insert into #temptab values(3, 3)
select
*
from
(select CategoryID from #temptab) a
cross join (select CategoryItemID from #temptab) b
where
exists (
select
*
from
#temptab c
where
c.CategoryID = a.CategoryID
and c.CategoryItemID = b.CategoryItemID)
order by
a.CategoryID
Regards,
Lakshman.|||Hi AB,
Thanks for replying.

>If this doesn't do what you want, then please see my signature so you
can
>understand why good specs are better than word problems.
The table in my original post is the actual table - the IDs are
integers.

>You might also consider storing these in separate tables instead of
trying
>to partially relate them, if there really is no relevance to the
>relationship...
Not entirely sure what you mean here - the categories are unrelated to
each other, but they are all categories. Each row in the CategoryItem
table has a parent Category.
When I run your code, I get impossible combinations, e.g. (1,3) and
(1,4), but there are only two types of Fruit (to use the example in
words from my 2nd post).
I appreciate that I probably haven't explained the situation very well!|||Ah, Lakshman, that's great - I feel you're on to something there!
Using the EXISTS clause like that is something I didn't consider.|||> I appreciate that I probably haven't explained the situation very well!
That's why I have already suggested you read http://www.aspfaq.com/5006 !
It won't hurt you to learn how to "explain the situation" in terms we can
understand and, more importantly, reproduce.|||Nate,
If you have a fixed and known number of categories, it's not as
tricky. See if this helps:
create table Categories (
pk int primary key,
Category varchar(10)
)
insert into Categories values (1,'Fruit')
insert into Categories values (2,'Car')
insert into Categories values (3,'Animal')
go
create table Items (
pk int primary key,
Categorypk int,
CategoryItem varchar(10)
)
insert into Items values (1,1,'Apple')
insert into Items values (2,1,'Orange')
insert into Items values (3,2,'Ford')
insert into Items values (4,2,'Vauxhall')
insert into Items values (5,2,'Fiat')
insert into Items values (6,3,'Dog')
insert into Items values (7,3,'Cat')
go
create function f(
@.pk int
) returns table as return
select C.Category, CI.CategoryItem
from Categories as C join Items as CI
on C.pk = @.pk
and CI.Categorypk = @.pk
and C.pk = CI.Categorypk
go
select
identity(int,1,1) as Combopk,
A.CategoryItem as AI,
B.CategoryItem as BI,
C.CategoryItem as CI
into #temp
from f(1) as A, f(2) as B, f(3) as C
select
T.Combopk,
C.Category,
case C.pk when 1 then T.AI when 2 then T.BI when 3 then T.CI end as Item
from #temp T
cross join Categories C
order by 1,2
go
--drop table Categories, Items, #temp
--drop function f
--Steve Kass
--Drew University
Nate wrote:

>Hi AB,
>Thanks for replying.
>
>
>can
>
>The table in my original post is the actual table - the IDs are
>integers.
>
>
>trying
>
>Not entirely sure what you mean here - the categories are unrelated to
>each other, but they are all categories. Each row in the CategoryItem
>table has a parent Category.
>When I run your code, I get impossible combinations, e.g. (1,3) and
>(1,4), but there are only two types of Fruit (to use the example in
>words from my 2nd post).
>I appreciate that I probably haven't explained the situation very well!
>
>|||Hi Steve,
Many thanks for that. In some cases, the number of categories will be
fixed (or at least, will have a sensible upper bound), so I can extend
your code to cover those cases. Cheers!
Regards,
Nate

Generated query

Hello,

i would like to know where can i see queries which are generated by the cube broswer in visual studio 2005 and where can i see what are the actions it does? Because it takes a very long moment between changes in the browser.

thanks

Start a trace on Analysis Services using the Profiler (assuming that you are using Analysis Services 2005). Then you will be able to see the MDX queries generated by the cube browser.|||thanks a lot

Generate Report Model from Cube with perspectives

1. Is there a way to generate SMDL programmatically from a cube besides using the RS Webservice API?

2. Is there a way to programmatically modify the SMDL once it has been created without doing crazy xml manipulation?

3. How can I create a Report Model from a cube with multiple perspectives? I want the model to only contain one perspective of the cube.

First, there's a real lack of responses on this forum....but hopefully this will help someone else that ran into these issues.

1. It's better to modify the SMDL that's generated from the API then to try and create it from scratch

2. The only way is with xml manipulation but it's actually not that bad. The schema is pretty simple. It just looks bloated at first with lots of repeating elements.

3. When the SMDL is generated, each perspective in the cube becomes a perspective in the report model. Then you can trim down the perspectives you don't want by removing the corresponding element from the SMDL and saving it back to reporting services.

Other things I notice is that once you modify the SMDL, you can't regenerate it using the Regenerate API call. Instead, you have to delete it and recreate it again. Also, once you recreate a model, you have to link up all the reports that were previously associated with it. I have no idea why RS removes the datasource from reports when the model is deleted. It's probably better to just leave it and if the datasource isn't there, then throw an error.

|||

Other things I notice is that once you modify the SMDL, you can't regenerate it using the Regenerate API call. Instead, you have to delete it and recreate it again. Also, once you recreate a model, you have to link up all the reports that were previously associated with it. I have no idea why RS removes the datasource from reports when the model is deleted. It's probably better to just leave it and if the datasource isn't there, then throw an error.

Actually, you will want to use the SetModelDefinition API instead of Renenerate. Items in the Report Server database are linked by GUID, not name. Just because it has the same name, we don't assume that it is the same item.

|||I do use SetModelDefinition API to modify the SMDL. But after a cube has been processed and the structure changed, I will need to regenerate the model base on the new cube. If I do that, all my reports break because the datasource reference is no longer valid even though the model has been recreated. This makes sense in the situations where the report may contain references to dimensions/measures no longer in the datasource. However, in my case, dimensions/measures are often added to the cube and not removed. I just think it's better to leave the datasource and have the report fail if dimensions/measures are missing instead of ALWAYS failing with an invalid datasource.

Generate a cube in Standard Edition

I would like to know if there are some example of how to generate a cube by c#. I am using MS SQL Standard Edition. The AMOAdventureWorks example, just run in Enterprise edition, because of Semi-Additive measures.

Tks!!

Hello! In tools and samples you also have a standard edition version of the Adventure Works cube project

You can probably find it here: c:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project

HTH

Thomas Ivarsson

|||

Ok, i found it, but I want another thing...

I am working on a project that require me to build cubes programmatically i.e., using AMO object and C#. I am trying to find a sample that builds cubes using C# in Standard Edition.

Tks!!!

|||

I do not know if there is a difference in programatically creating a cube between Standard and Enterprise, but look here:

http://technet.microsoft.com/en-us/library/ms345091.aspx

|||

This example is perfect, I am using it, but when I try to execute it, or another example in SQL Standard Edition, the same error occurred in this line, problemas wuth the Semi-Additive Measures :

//--

cube.Update (UpdateOptions.ExpandFull);

//--

The Semi-Additive Measures feature of MS SQL Server 2005 is not supported in its Standard Edition SKU. How do solve this issue? Will I have to by Enterprise Edition SKU ?

This is documented here: http://www.microsoft.com/sql/editions/enterprise/comparison.mspx
Tks!

|||

I just figured this out.

Comment out this stuff in CreateCurrencyRatesMeasureGroup()

//average of children is not allowed.

//meas.AggregateFunction = AggregationFunction.AverageOfChildren;

//last non empty is not allowed.

//meas.AggregateFunction = AggregationFunction.LastNonEmpty;

Generate a cube in Standard Edition

I would like to know if there are some example of how to generate a cube by c#. I am using MS SQL Standard Edition. The AMOAdventureWorks example, just run in Enterprise edition, because of Semi-Additive measures.

Tks!!

Hello! In tools and samples you also have a standard edition version of the Adventure Works cube project

You can probably find it here: c:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project

HTH

Thomas Ivarsson

|||

Ok, i found it, but I want another thing...

I am working on a project that require me to build cubes programmatically i.e., using AMO object and C#. I am trying to find a sample that builds cubes using C# in Standard Edition.

Tks!!!

|||

I do not know if there is a difference in programatically creating a cube between Standard and Enterprise, but look here:

http://technet.microsoft.com/en-us/library/ms345091.aspx

|||

This example is perfect, I am using it, but when I try to execute it, or another example in SQL Standard Edition, the same error occurred in this line, problemas wuth the Semi-Additive Measures :

//--

cube.Update (UpdateOptions.ExpandFull);

//--

The Semi-Additive Measures feature of MS SQL Server 2005 is not supported in its Standard Edition SKU. How do solve this issue? Will I have to by Enterprise Edition SKU ?

This is documented here: http://www.microsoft.com/sql/editions/enterprise/comparison.mspx
Tks!

|||

I just figured this out.

Comment out this stuff in CreateCurrencyRatesMeasureGroup()

//average of children is not allowed.

//meas.AggregateFunction = AggregationFunction.AverageOfChildren;

//last non empty is not allowed.

//meas.AggregateFunction = AggregationFunction.LastNonEmpty;