multiple rows insert: calculation

Showing posts with label calculation. Show all posts

Wednesday, March 28, 2012

How to estimate SQL database growth when new fields are added?

I need help regarding calculation of database size growth. Here is my
query:
Q1 We have created two SQL databases, databaseA and databaseB; they are
mirror images of a databaseC which is a non - SQL database.
DatabaseB has been altered to accomodate 3-4 new fields. We need to
estimate how much databaseB grew by. Please note that number of rows
and tables have remained same for both databaseB and databaseC. What
would be the best estimation technique?
We tried database size, in properties but results are very weird.
Any suggestion would be highly appreciated.
Thank you,
AnjaliUse Windows Explorer, and compare DatabaseA files with DatabaseB files. The
File size difference (reported by the OS) should be a matter of subtraction.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Anjali" <anjali.bisht@.gmail.com> wrote in message
news:1162533985.274091.33370@.k70g2000cwa.googlegroups.com...
>I need help regarding calculation of database size growth. Here is my
> query:
> Q1 We have created two SQL databases, databaseA and databaseB; they are
> mirror images of a databaseC which is a non - SQL database.
> DatabaseB has been altered to accomodate 3-4 new fields. We need to
> estimate how much databaseB grew by. Please note that number of rows
> and tables have remained same for both databaseB and databaseC. What
> would be the best estimation technique?
>
> We tried database size, in properties but results are very weird.
> Any suggestion would be highly appreciated.
> Thank you,
> Anjali
>|||I am not sure i can see SQL database in windows explorer. I have
already checked database sizes through SQL enterprise manager, but i am
not satisfied with results.
Is there any other solution?
Arnie Rowland wrote:
> Use Windows Explorer, and compare DatabaseA files with DatabaseB files. The
> File size difference (reported by the OS) should be a matter of subtraction.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "Anjali" <anjali.bisht@.gmail.com> wrote in message
> news:1162533985.274091.33370@.k70g2000cwa.googlegroups.com...
> >I need help regarding calculation of database size growth. Here is my
> > query:
> >
> > Q1 We have created two SQL databases, databaseA and databaseB; they are
> > mirror images of a databaseC which is a non - SQL database.
> >
> > DatabaseB has been altered to accomodate 3-4 new fields. We need to
> > estimate how much databaseB grew by. Please note that number of rows
> > and tables have remained same for both databaseB and databaseC. What
> > would be the best estimation technique?
> >
> >
> > We tried database size, in properties but results are very weird.
> >
> > Any suggestion would be highly appreciated.
> > Thank you,
> > Anjali
> >|||Of course you can see the database files using Windows Explorer. Unless you
don't have permissions to the OS level of the server. IF that is the
situation, then ask your admin for assistance.
You can calculate the 'estimated' size increase by adding the datatype
storage requirements for the four new fields, and multiplying that by the
total number of rows in the table. If any of the four new columns are
indexed, the effect of indexing is not included.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Anjali" <anjali.bisht@.gmail.com> wrote in message
news:1162550620.309751.37790@.m7g2000cwm.googlegroups.com...
>I am not sure i can see SQL database in windows explorer. I have
> already checked database sizes through SQL enterprise manager, but i am
> not satisfied with results.
> Is there any other solution?
> Arnie Rowland wrote:
>> Use Windows Explorer, and compare DatabaseA files with DatabaseB files.
>> The
>> File size difference (reported by the OS) should be a matter of
>> subtraction.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>> You can't help someone get up a hill without getting a little closer to
>> the
>> top yourself.
>> - H. Norman Schwarzkopf
>>
>> "Anjali" <anjali.bisht@.gmail.com> wrote in message
>> news:1162533985.274091.33370@.k70g2000cwa.googlegroups.com...
>> >I need help regarding calculation of database size growth. Here is my
>> > query:
>> >
>> > Q1 We have created two SQL databases, databaseA and databaseB; they are
>> > mirror images of a databaseC which is a non - SQL database.
>> >
>> > DatabaseB has been altered to accomodate 3-4 new fields. We need to
>> > estimate how much databaseB grew by. Please note that number of rows
>> > and tables have remained same for both databaseB and databaseC. What
>> > would be the best estimation technique?
>> >
>> >
>> > We tried database size, in properties but results are very weird.
>> >
>> > Any suggestion would be highly appreciated.
>> > Thank you,
>> > Anjali
>> >
>sql

How to estimate SQL database growth when new fields are added?

Wednesday, March 7, 2012

How to do weighted average in Cube Calculation?

I am struggling with creating a weighted average calculation in my SSAS 2005 cube. I have a calculated member that computes the average Height of my products. This works out fine when I slice that measure up by Product Code. However, when I use it in another calculation, it appears to be defaulting to the ALL member.

Using the SQL Profiler connected to SSAS I was able to extract the following MDX when I got a Pivot Table in Excel set up the way I want initially:

SELECT NON EMPTY CrossJoin(Hierarchize(AddCalculatedMembers({DrilldownLevel({[Dim Product].[Products - Product Code]})})), {[Measures].[Cut Jobs - Avg Piece Height]})

ON COLUMNS

FROM [DN DWH Cube]

The MDX above gives me a breakdown of each product code and it's average height.

Now what I need to do for another calculation is for each product code, take the sum of the average height * number of parts / total number of parts.

For example, say for a given dimensional slice (date or job number, production run, etc...) I have the following data:

Prod Avg Height Num Parts

A 10 100

B 7 500

C 23 225

The resulting formula should look perform the computation like this:

((10 * 100) + (7 * 500) + (23 * 225)) / 825 = 11.72

Wheras the actual average of the heights is 24.6

How do I write the MDX in a cube calculation so that I can use this weighted average value in other calculations?

Thanks in advance!

Is "Avg Height" a cube or calculated measure - if calculated, what are the underlying cube measures and formula? I'm assuming that "Num Parts" is a cube measure, of course.

|||

Avg Height is a calculated measure defined as:

[Measures].[Product Height] / [Measures].[Fact Count]

|||

In that case, if you want a weighted average just over Product Codes (as opposed to weighted average at the fact level), you could try something like:

Code Snippet

Sum(existing [Dim Product].[Products - Product Code].[Products - Product Code],

[Measures].[Avg Height] * [Measures].[Num Parts])

/ [Measures].[Num Parts]

|||

Thanks Deepak. That worked perfectly. One question, in the MDX you wrote, how is this

[Dim Product].[Products - Product Code].[Products - Product Code]

different from this:

[Dim Product].[Products - Product Code]?

|||

The naming pattern is: Dimension->Hierarchy->Level, so [Dim Product].[Products - Product Code] is a hierarchy, and

[Dim Product].[Products - Product Code].[Products - Product Code] is a level of that hierachy.

How to do weighted average in Cube Calculation?

Using the SQL Profiler connected to SSAS I was able to extract the following MDX when I got a Pivot Table in Excel set up the way I want initially:

SELECT NON EMPTY CrossJoin(Hierarchize(AddCalculatedMembers({DrilldownLevel({[Dim Product].[Products - Product Code]})})), {[Measures].[Cut Jobs - Avg Piece Height]})

ON COLUMNS

FROM [DN DWH Cube]

The MDX above gives me a breakdown of each product code and it's average height.

Now what I need to do for another calculation is for each product code, take the sum of the average height * number of parts / total number of parts.

For example, say for a given dimensional slice (date or job number, production run, etc...) I have the following data:

Prod Avg Height Num Parts

A 10 100

B 7 500

C 23 225

The resulting formula should look perform the computation like this:

((10 * 100) + (7 * 500) + (23 * 225)) / 825 = 11.72

Wheras the actual average of the heights is 24.6

How do I write the MDX in a cube calculation so that I can use this weighted average value in other calculations?

Thanks in advance!

Is "Avg Height" a cube or calculated measure - if calculated, what are the underlying cube measures and formula? I'm assuming that "Num Parts" is a cube measure, of course.

|||

Avg Height is a calculated measure defined as:

[Measures].[Product Height] / [Measures].[Fact Count]

|||

In that case, if you want a weighted average just over Product Codes (as opposed to weighted average at the fact level), you could try something like:

Code Snippet

Sum(existing [Dim Product].[Products - Product Code].[Products - Product Code],

[Measures].[Avg Height] * [Measures].[Num Parts])

/ [Measures].[Num Parts]

|||

Thanks Deepak. That worked perfectly. One question, in the MDX you wrote, how is this

[Dim Product].[Products - Product Code].[Products - Product Code]

different from this:

[Dim Product].[Products - Product Code]?

|||

The naming pattern is: Dimension->Hierarchy->Level, so [Dim Product].[Products - Product Code] is a hierarchy, and

[Dim Product].[Products - Product Code].[Products - Product Code] is a level of that hierachy.

How to do weighted average in Cube Calculation?

Using the SQL Profiler connected to SSAS I was able to extract the following MDX when I got a Pivot Table in Excel set up the way I want initially:

SELECT NON EMPTY CrossJoin(Hierarchize(AddCalculatedMembers({DrilldownLevel({[Dim Product].[Products - Product Code]})})), {[Measures].[Cut Jobs - Avg Piece Height]})

ON COLUMNS

FROM [DN DWH Cube]

The MDX above gives me a breakdown of each product code and it's average height.

Now what I need to do for another calculation is for each product code, take the sum of the average height * number of parts / total number of parts.

For example, say for a given dimensional slice (date or job number, production run, etc...) I have the following data:

Prod Avg Height Num Parts

A 10 100

B 7 500

C 23 225

The resulting formula should look perform the computation like this:

((10 * 100) + (7 * 500) + (23 * 225)) / 825 = 11.72

Wheras the actual average of the heights is 24.6

How do I write the MDX in a cube calculation so that I can use this weighted average value in other calculations?

Thanks in advance!

Is "Avg Height" a cube or calculated measure - if calculated, what are the underlying cube measures and formula? I'm assuming that "Num Parts" is a cube measure, of course.

|||

Avg Height is a calculated measure defined as:

[Measures].[Product Height] / [Measures].[Fact Count]

|||

In that case, if you want a weighted average just over Product Codes (as opposed to weighted average at the fact level), you could try something like:

Code Snippet

Sum(existing [Dim Product].[Products - Product Code].[Products - Product Code],

[Measures].[Avg Height] * [Measures].[Num Parts])

/ [Measures].[Num Parts]

|||

Thanks Deepak. That worked perfectly. One question, in the MDX you wrote, how is this

[Dim Product].[Products - Product Code].[Products - Product Code]

different from this:

[Dim Product].[Products - Product Code]?

|||

The naming pattern is: Dimension->Hierarchy->Level, so [Dim Product].[Products - Product Code] is a hierarchy, and

[Dim Product].[Products - Product Code].[Products - Product Code] is a level of that hierachy.

Friday, February 24, 2012

How to do this calculation?

I have 3 tables , these table contain field as follow

- Dim_Product

- DecVolume

- Dim_Category

- DecVolume

- Fact_SalesTrx

- DecUnitPrice

If i would like to calculate a Price Per Unit with this formula, how should i do it?

Price Per Unit for specific Unit of Measurement (UOM)= (Unit Price/ UOM for that product) * UOM for that Category

I had try to do in the Calculation tab of the cube, but the result generate is weird (= Infinity).

This is the script that i had try

CALCULATE;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Price Per Unit]

AS ([Measures].[Dec Unit Price]/[Dim Product].[Dec Volume])*[Dim Category].[Dec Volume],

FORMAT_STRING = "Standard",

NON_EMPTY_BEHAVIOR = { [Dec Unit Price] },

VISIBLE = 1 ;

Appreciate some one can leave some clue here for me to go on...

Thanks

Woon How

You can do that inside datasourceview as named calculation!

NamedCalculation_PricePerUnit = (UnitPrice/DecVolume)*DecVolume

Try it.

Helped?

regards!

|||

Assuming that the "Value Column" Source and DataType have been correctly configured for the [Dim Product].[Dec Volume] and [Dim Category].[Dec Volume] attributes, you could try .MemberValue, like:

Code Snippet

AS ([Measures].[Dec Unit Price]/[Dim Product].[Dec Volume].MemberValue)

* [Dim Category].[Dec Volume].MemberValue,

|||

Thanks for your hlp

Do you mean:

"Value Column" - mean insert some data in? (I'm had put some value for this column)

"Data Type: - mean set the data type ? )(I'm created it in design and set it to decimal)

I try had you approach and result return "#VALUE!". and i highlight the cell, it mention type mismatch.

Any idea?

Thanks In Advance Deepak Puri..

|||

Thanks PedroCGD,

If doing in this way, i would need to create another 2 columns into the Fact_SalesTrx Tables (decVolume Of Product,DecVolume Of Category)

Would that be a redundacy? since i had the columns in the Dim_Product, Dim_Category. And what should be the best way to design this, sorry im new in SSAS, so a lot of stuff im not sure.

|||

It's easier to illustrate this with a sample Adventure Works query - note that the .MemberValue of [Promotion].[Min Quantity] and [Product].[Reorder Point] attributes is only meaningful at the appropriate granularity of those dimensions:

Code Snippet

With

Member [Measures].[WeightedQuantity] as

iif(IsEmpty([Measures].[Order Quantity]) or

[Promotion].[Min Quantity].MemberValue = 0,

Null,

([Measures].[Order Quantity] /

[Product].[Reorder Point].MemberValue)

* [Promotion].[Min Quantity].MemberValue),

FORMAT_STRING = '#,0.0'

select

{[Measures].[WeightedQuantity]} on 0,

Non Empty [Product].[Product].[Product]

* [Promotion].[Promotion].[Promotion] on 1

from [Adventure Works]

How to do Minof max and maxof min Calculation in SQL

Hi all,
I have two tables say A and B. and i have the following fields in the two tables
A B
vc_low(say 32) min_vc(say 35)
What is the query to get the MAX of these two fields
It should be like max(vc_low,min_vc),....But i dont know how to form a querry in SQL..
Can any one help me .........

Thanks in AdvanceYou can use CASE clause. From your subject and message i've drafted below example:
create table test1 (id1 int)
insert into test1 values(1)
insert into test1 values(2)
create table test2 (id2 int)
insert into test2 values(11)
insert into test2 values(12)
select 'max value' = case when min(test1.id1) > min(test2.id2) then min(test1.id1) when min(test2.id2) > min(test1.id1) then min(test2.id2) END from test1,test2
The result of the above query will 11
hope the above helps you.
-srikanthr

Sunday, February 19, 2012

How to do calculation in Stored Procedure

Hi All,

Can anyone show me how to do a calculation in stored procedure? What I want to achieve is:
Employee has a different commission on each order, but what I would like to get is the total commission for all the orders that he/she accomplished so far.
Can anyone help me to write a query for this?

Thanks in advance.

Anyone please...|||

Try something like this:

SELECT EmployeeID, SUM(Commission) AS TotalCommission FROM yourOrderTable

GROUP BY EmployeeID

|||Thanks, I got it. I was thinking too complicated.

Wednesday, March 28, 2012

How to estimate SQL database growth when new fields are added?

How to estimate SQL database growth when new fields are added?

How to estimate SQL database growth when new fields are added?

Wednesday, March 7, 2012

How to do weighted average in Cube Calculation?

How to do weighted average in Cube Calculation?

How to do weighted average in Cube Calculation?

Friday, February 24, 2012

How to do this calculation?

How to do Minof max and maxof min Calculation in SQL

Sunday, February 19, 2012

How to do calculation in Stored Procedure

multiple rows insert

Blog Archive

About Me