Can not deploy calculation...

Hi,

I had a calculated measure which simply tries to create a Margin Profit. This is the simply Measure divided by another measure. The problem is that when I try to deploy this measure with a Parent Hierarchy of measures it will not deploy I get the error like this:

Errors and Warnings from Response
MdxScript(SSAS) (7, 8) The 'Measures' dimension contains more than one hierarchy, therefore the hierarchy must be explicitly specified.

Rgds.,

Hello. Can you explain what you mean by "Parent hierarchy of measures"?

For this calculation ([Margin Profit] = [Measures].[Sales]/[Measures].[Cost]) should be sufficient.

Regards

Thomas Ivarsson

|||

I've seen the error you specified occur when the cube collation sequence has "case sensitivity" set, and the name of the measures dimension in the MDX expression didn't match the case of measures as specified in the cube. As an example:

measures.[A Measure Name] = resulted in the error

Measures.[A Measure Name] = processed correctly.

Note the capital "M" in the second measures specification. Don't know ifthis is your problem, but it is one time when I have seen the error you reported.

PGoldy

|||

Hi PGoldy,

Thanks for your information.

I had to try to change the [MEASURES] to [Measures], it's work I can deploy it.

thanks very much for your help.

Cherming

Can not created Named Calculation...

I can not seem to create a Named Calculation on a table that I have already created a Named Query on in my data source view. Any idea why this is and if there is a work around for it?

TIA

Wellman

If you have already a Named Query you can add an expression-based column to the SELECT statement using the syntax of the underlying data source.|||

Thank you for this information. Any idea why this is so? Doesn't make much sense to me.

Also, can I reference other tables as well?

TIA

Wellman

|||Which part doesn't make sense? You can view a named query as a SQL view. Just like a SQL view, your SELECT statement can include any valid syntax, including other tables, calling UDF, etc.

can I use an attribute in a calculation?

i have an instrument dimension. the instrument dimension has a conversion ratio attribute (i.e. each instrument has a conversion ratio). the conversion ratio attribute is not interesting for analysis in itself, but is often used in calculations.

I want to say somehting like this:

for all instruments, show me sum of [measures].[Price] * ~ CONVERSION RATIO OF THAT INSTRUMENT ~

this actually works ok at the detail level. however, when I start rolling up instruments into say, sectors, then i get a type mismatch error saying that teh "All" member cannot be converted to type double.

In general, is it valid to use a non-measure group dimension attribute to perform a calculation? If not, should any attribute that might participate in a calculation be created as a measure (even though it has no real analysis value by itself). Or should I do the

"Fact * Looked Up Attribute"-type calculation in the DSV and create a new Fact called something like [Price X Conv Ratio]?

The first of these queries generates the error you mention above. I believe the second handles the logic in the manner you are wanting. I haven't had a lot of time to look at this, so I would highly recommend you independtly verify the results of this logic (or the version you implement) to make sure the values are correct. Also, I used a SUM function to aggregate the values in the calculated member. There are a number of functions available to you. In this situation, AVG may be more appropriate.

Thanks,
Bryan

Code Snippet

withmember Measures.[x] as

[Measures].[Reseller Sales Amount]/[Product].[List Price].CurrentMember

select

{[x],[Reseller Sales Amount]} on 0

from [Adventure Works]

;

withmember Measures.[x] as

SUM(

[Product].[List Price].[List Price].Members,

([Product].[List Price].CurrentMember, [Measures].[Reseller Sales Amount])/

[Product].[List Price].CurrentMember.MemberValue

), format="#,#"

select

{[x],[Reseller Sales Amount]} on 0

from [Adventure Works]

;

can I update based on a join result with a calculation

My table design is
CREATE TABLE [HashKeyTotals] (
[hKey] [bigint] NOT NULL ,
[Total] [int] NULL ,
CONSTRAINT [HashKeyTotals] PRIMARY KEY CLUSTERED
(
[hKey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
and I also have data files of the same structure. My goal is to import the
data files in order to update the Total table where the hKey matches.
I could
A. Read each record (4.5 million) and send to a stored procedure which does
a look up and add to the total field.
or
B. Import the data file into a temp table, join the HashKeyTotals table with
some sort of calc'd field summing the two totals and put that into a temp
table to be loaded back into the HashKeyTotals table.
I'm not even sure if B is possible but just a crazy idea. Will it work and
will it be that much faster than plan A? The HashKeyTotals table can have up
to 25 million records and I'd be loading a data file with 5 million records
in it each day.
Any thoughts?
ThanksSo, you're incrementing the Total by what you get in the import table for
the corresponding key? If so, try:
CREATE TABLE [HashKeyTotals] (
[hKey] [bigint] NOT NULL ,
[Total] [int] NULL ,
CONSTRAINT [PK_HashKeyTotals] PRIMARY KEY CLUSTERED
(
[hKey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [HashKeyTotals2] (
[hKey] [bigint] NOT NULL ,
[Total] [int] NULL ,
CONSTRAINT [PK_HashKeyTotals2] PRIMARY KEY CLUSTERED
(
[hKey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
insert HashKeyTotals values (1, 10)
insert HashKeyTotals values (2, 20)
insert HashKeyTotals2 values (1, 5)
insert HashKeyTotals2 values (2, 15)
go
update h
set
Total = h.Total + h2.Total
from
HashKeyTotals h
join HashKeyTotals2 h2 on h2.hKey = h.hKey
go
select * from HashKeyTotals
go
drop table HashKeyTotals, HashKeyTotals2
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Mike" <Mike@.mike.com> wrote in message
news:uuIuedUcGHA.3936@.TK2MSFTNGP05.phx.gbl...
My table design is
CREATE TABLE [HashKeyTotals] (
[hKey] [bigint] NOT NULL ,
[Total] [int] NULL ,
CONSTRAINT [HashKeyTotals] PRIMARY KEY CLUSTERED
(
[hKey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
and I also have data files of the same structure. My goal is to import the
data files in order to update the Total table where the hKey matches.
I could
A. Read each record (4.5 million) and send to a stored procedure which does
a look up and add to the total field.
or
B. Import the data file into a temp table, join the HashKeyTotals table with
some sort of calc'd field summing the two totals and put that into a temp
table to be loaded back into the HashKeyTotals table.
I'm not even sure if B is possible but just a crazy idea. Will it work and
will it be that much faster than plan A? The HashKeyTotals table can have up
to 25 million records and I'd be loading a data file with 5 million records
in it each day.
Any thoughts?
Thanks|||Yes thats exactly what I need. Thank you very much.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23LBk8iVcGHA.1276@.TK2MSFTNGP03.phx.gbl...
> So, you're incrementing the Total by what you get in the import table for
> the corresponding key? If so, try:
> CREATE TABLE [HashKeyTotals] (
> [hKey] [bigint] NOT NULL ,
> [Total] [int] NULL ,
> CONSTRAINT [PK_HashKeyTotals] PRIMARY KEY CLUSTERED
> (
> [hKey]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> CREATE TABLE [HashKeyTotals2] (
> [hKey] [bigint] NOT NULL ,
> [Total] [int] NULL ,
> CONSTRAINT [PK_HashKeyTotals2] PRIMARY KEY CLUSTERED
> (
> [hKey]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> insert HashKeyTotals values (1, 10)
> insert HashKeyTotals values (2, 20)
> insert HashKeyTotals2 values (1, 5)
> insert HashKeyTotals2 values (2, 15)
> go
> update h
> set
> Total = h.Total + h2.Total
> from
> HashKeyTotals h
> join HashKeyTotals2 h2 on h2.hKey = h.hKey
> go
> select * from HashKeyTotals
> go
> drop table HashKeyTotals, HashKeyTotals2
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Mike" <Mike@.mike.com> wrote in message
> news:uuIuedUcGHA.3936@.TK2MSFTNGP05.phx.gbl...
> My table design is
> CREATE TABLE [HashKeyTotals] (
> [hKey] [bigint] NOT NULL ,
> [Total] [int] NULL ,
> CONSTRAINT [HashKeyTotals] PRIMARY KEY CLUSTERED
> (
> [hKey]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> and I also have data files of the same structure. My goal is to import the
> data files in order to update the Total table where the hKey matches.
> I could
> A. Read each record (4.5 million) and send to a stored procedure which
> does
> a look up and add to the total field.
> or
> B. Import the data file into a temp table, join the HashKeyTotals table
> with
> some sort of calc'd field summing the two totals and put that into a temp
> table to be loaded back into the HashKeyTotals table.
> I'm not even sure if B is possible but just a crazy idea. Will it work and
> will it be that much faster than plan A? The HashKeyTotals table can have
> up
> to 25 million records and I'd be loading a data file with 5 million
> records
> in it each day.
> Any thoughts?
> Thanks
>