Turning Off Aggregation On A Certain Hierarchy

May 20, 2008

I have a multi dimensional cube. Among the dimensions I have, there is one dimension that has one hierarchy defined. When I view any of my measures on this hierarchy, I don't want the measures to be aggregated. I read some threads here having to do with semi additive behaviors, but Im not sure if it applies to my case. (or maybe it does and I'm just not getting it)

For example,

DimPerson

PersonID, PersonName, AnotherPersonID
1 , Person_A , null
2 , Person_B , 1
3 , Person_C , 1

The purpose of "AnotherPersonID" is to have a self join that describes a certain relationship. So, in this case, Person B and C are related to Person A. I describe this relationship using a hierarchy where A is a parent of B and C. However, I don't want the measures for Person_A to be replaced by the sum of the measures in Person_B and Person_C.

Why do I have a hierarchy if I don't want to sum the numbers? This is motivated by the need to dynamically report on A's numbers when B or C's numbers are reported. I'm just using persons as an example, but, in my case I have a bunch of members that can be paired with another member in that dimension. And when I report on a member that joins to another member in that dimension, I need to dynamically report on that member's measures as well.

Lastly, If the use of a hierarchy is not the best approach for this, would you recommend another approach?

View 4 Replies


ADVERTISEMENT

Aggregation In A Hierarchy Involving Non-leaf Data.

May 22, 2008

NOTE: I apologize to anyone (especially moderators) who may notice that I am basically repeating a question that was already posted by me in another recent thread. The reason why I am reposting is because I want to filter my question down to its crux because the other question may not have been asked in the most clear way.

The Question:

The sceanrio is this.
(1) I have a sales person dimension with a hierarchy.
(2) In this hierarchy, Bill and Ted roll up to John.
(3) Bill sells 10 units, Ted sells 8 units, and John sells 5 units.

When you process this hierarchy, what would you expect the total to be for John?
(A) 23, which is the sum for Bill, Ted, and John
or
(B) 18, which is the sum of Bill and Ted only and overwritting John's number

I say (A) and I think most will choose the same, and all the examples I've been reflects (A).

However, in my simple cube I get (B).

Is there a setting I need to adjust to get (A)?


View 16 Replies View Related

Turning A Null Into A Value

Aug 16, 2006

I am trying to return a 0 when there is a NULL as a result, I thought that Sum(ISNULL(Payment,0) would do the trick, I was wrong...any ideas:
 MonthlyCredit =
(Select
Sum(ISNULL(Payment,0)) from transaction as t
Where AccountNumber = @AccountNumber
and
Month(t.PayDate) = @Month
)
 

View 6 Replies View Related

Turning Off The Log File

Jan 13, 1999

Is There any way to stop a database from writing to the log file when you alter the design
of a table.

View 1 Replies View Related

Turning Triggers Off

Jan 16, 2002

Is it possible to turn a trigger off in SQL Server as you would be able to do in oracle? If so, how?

Thanks
David

View 1 Replies View Related

Turning IDENTITY_INSERT ON

Aug 22, 2001

Im trying to do an INSERT SELECT statement in the following manner:

INSERT INTO
DB1.dbo.TABLE
SELECT *
FROM dbo.TABLE1
dbo.TABLE2 ON dbo.TABLE1.column = dbo.TABLE2.column

And Im given this error message:

An explicit value for the identity column in table 'DB1.dbo.TABLE' can only be specified when a column list is used and IDENTITY_INSERT is ON


So if anyone knows how to turn it on it would be a great help.

Sincerely,
Matt

View 3 Replies View Related

Turning Triggers On And Off.

Feb 15, 2005

Hi is it possible to run a certain SQL statement agaisnt SQL Server and ask it not to fire any triggers? Or is would it be better to disable the trigger and then reable it after ward? If so how? Thanks Ed

View 3 Replies View Related

Turning RDA Tracking Off

Nov 28, 2006

I have a device application that simply needs to upload data to a server. The preferred DB server is Oracle but I've made it work using RDA and SQL Server. The problem I'm having is that it just needs to upload data, whichh I send using the RDA.Push() method. The data arrives just fine, the first time. With every subsequent upload all of the previous data is deleted fromt he server. Apparently RDA is tracking the deletion of the previously uploaded data locally and on the next .Push deleting that data from the server.

My question is: Is it possible to prevent RDA from deleting data on SQL Server? I attempted to delete the rows from the __sysDeletedRows/__sysRowTrack tables but got a "Data is read only" error.







View 1 Replies View Related

Turning Age 65 Or 75 In May 2008

May 12, 2008

--Environment: SQL Server 2000
I am doing following query for who is reaching of Age 65/75 in May 2008 but Member_DOB's or Spouse_DOB's showing different month. Month should show '05' because I want to see results who is reaching of age 65/75 in May 2008 only.
Please help in this regard.
--Query:
Select m.empid, m.dob "Member_DOB",
round(datediff(dd,m.dob,'05/30/2008')/365.25,1) Member_Age,
d.dob "Spouse_DOB",
round(datediff(dd,d.dob,'05/30/2008')/365.25,1) Spouse_Age
from member m
left outer join (SELECT * FROM depend where depcode = 'S' and activestatus = 1)d
on m.empid = d.empid
where (datepart(yy,m.dob) in (1933,1943) or
datepart(yy,d.dob) in (1933,1943))
and round(datediff(dd,m.dob,'05/30/2008')/365.25,1) in (65,75)
or round(datediff(dd,d.dob,'05/30/2008')/365.25,1) in (65,75)

--Results:
Empid Member_DOB Member_Age Spouse_DOB Spouse_Age
000000033 1931-12-07 00:00:00.000 76.500000 1933-06-16 00:00:00.000 75.000000
000000085 1933-05-23 00:00:00.000 75.000000 1938-03-10 00:00:00.000 70.200000
000000695 1933-06-10 00:00:00.000 75.000000 1934-07-08 00:00:00.000 73.900000
000000792 1931-01-15 00:00:00.000 77.400000 1933-06-05 00:00:00.000 75.000000
000002406 1933-05-27 00:00:00.000 75.000000 NULL NULL
000004149 1933-05-20 00:00:00.000 75.000000 NULL NULL


Desired results:

Member_DOB and Spouse_DOB's should show '05' i.e. 1931-05-07, 1931-05-15

View 2 Replies View Related

Turning Off Select All In SP2

Feb 26, 2007

Does anyone know if there is an easy way to turn off the "Select All" option from appearing on reports with multi-selects? I am going to have a hard time getting the development staff to update all of our reports AGAIN after making them conform to SP1.

Please let me know if there is a way before I install SP2.

Thanks.

View 8 Replies View Related

Turning Off Validation

Feb 10, 2007

I need to turn off validation and I've seen some threads saying this is not possible but my situation has a twist.

A customer needs the package to connect to different modem dialup connections to connect to different servers (they use dialup for security reasons). We have written two VB script tasks at the beginning and end of a loop, with data flows in between. Before the loop the dialup connection info is read into a recordset along with Data Source connection information. The first script uses this information to dialup and the last script hangs up the connection. The problem is the package tries to validate the data connections and the package has not dialed up yet, so it fails.

We managed to confirm it works in a test environment by putting a break in the first script, manually VPNing into the test network (to allow validation of the data flow to work), and then manually disconnecting from VPN during the break. The script dials in and pumps the data. But this won't be an option in production.

So if anyone has figured out a way to turn off validation, great. Otherwise, any ideas to make this work? I was thinking about setting up a dummy connection that would be connected outside the package before running just for validation (and then the script would disconnect to begin, but I would prefer to handle all of this within SSIS.

Any help? While I see the point of validation it's a bummer that MSFT didn't put this in the hands of the user.

Thanks, Kayda

View 4 Replies View Related

I4 To I8 In Aggregation

May 30, 2007

I'm using an Aggregation task to summarize an input file by item and week before inserting it into a SQL table.



Two of the fields I'm summing, because their totals per record can occasionally exceed 32k, are defined as int (I4) instead of smallint (I2). However, the summarized total never exceeds the value an int can hold.



I ran into a problem on the insert, however, with SSIS telling me it couldn't insert an I8 value into an I4 table field. I discovered the metadata for the summed totals had automatically been set to bigint (I8), and the mapping was failing.



I didn't see a way to change that metadata within the Aggregation task itself, so I added a Data Conversion task to convert the totals to four-byte signed integers and enable the mapping. Was that the proper workaround?

View 1 Replies View Related

Turning A View Into A Table

Apr 15, 2007

I am in a scenario where my tables are refreshed every morning by a batch update.  I have built a few views off of one table.  To increase speed I would like to take all the rows from one of the view s and insert them into their own table.  I know this can be done with some T-SQL but I'm a noob to it and don't know how to specifically do it.Any detailed help would be greatly appreciated. -Nate 

View 1 Replies View Related

Turning SELECT Into A DELETE

May 8, 2008

I've constructed the SELECT statement to show the rows I want - and it shows 189 rows. Now I want to delete these rows. Here is the SELECT statement:

SELECT tblinqty.* FROM tblinqty LEFT JOIN tblmporder ON tblinqty.linkidsub = tblmporder.orderno WHERE tblmporder.orderno IS NULL and tblinqty.transtype = '0' and tblinqty.linkid = 'MP'

If I change the statement to "select * from tblinqty where exists ()", putting the above command inside the (), it returns over 12,000 rows! My intention is to change the SELECT into a DELETE by replacing the "select *" with a "DELETE" - but if I do that it will delete the wrong rows. How is the easiest way to turn the above successful SELECT statement, which yields 189 rows, into a DELETE statement which also deletes the same 189 rows?

I've tried changing the statement to a WHERE, thinking it would be easier to change to a DELETE, but the following yields 0 rows:

SELECT tblinqty.* FROM tblinqty WHERE tblinqty.linkidsub = tblmporder.orderno AND tblmporder.orderno IS NULL and tblinqty.transtype = '0' and tblinqty.linkid = 'MP'

View 1 Replies View Related

Turning Rows Into Columns?

May 7, 2015

I'm using SQL 2008. I want to essentially turn rows into columns. The source table has a variable number of rows and a fixed number of columns - the magical, elusive SQL query will yield a result that has a variable number of columns and fixed number of rows. A slight twist is that there is grouping by Territory, and in this example the first two rows should be reduced to one, with the SlsPerson concatenated to AA/BB.

The table, represented by RC_DataTable:

Territory----State--Est--SlsPerson
----------------------------------
Chicago------IL-----2004--AA------
Chicago------IL-----2004--BB------
New York-----NY-----1989--CC------
Los Angeles--CA-----2007--DD------

The result of the query will yield:

COL1------COL2-------COL3----------
-----------------------------------
Chicago---New York---Los Angeles---
IL--------NY---------CA------------
2004------1989-------2007----------
AA/BB-----CC---------DD------------

Here is a script to establish the above data:

USE [Northwind]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[RCDataTable](

[code]....

View 2 Replies View Related

Turning Columns Into Rows

Dec 13, 2007

I have a summary table with a number of columns that give all the information I need to build a report. What I would like to do is create a view specific to a single report, that organizes the data so that each row represents one metric. The only way I know of to do this would be with a series of Union querries, but that would require querrying what is basicly the same data multiple times. Is there some way to gather the data in one pass and then split it up with multiple Union queries? Sicne I doubt I'm explaining this well I'll just try an exsample.

Let say I have a summary table with the following columns: Location, Severity, Date_Day, Number_Dispatch, Dispatch_Duration, Dispatch_Goal, Number_Dispatch_Met_Goal, and Dispatch_Met_Goal.

Now I want to turn this into a table with the following columns: Metric, Location, Severity, Goal, Value.

The only way I know how to do that is with the following SQL:

--Number Dispatched Yesterday
SELECT Tickets Dispatched Yesterday AS Metric
, Location
, Severity
, N/A AS Goal
, sum( Number_Dispatch ) AS VALUE
FROM Summary_Table
Where

UNION
--Average Dispatch Duration
SELECT Average Dispatch Duration AS Metric
, Location
, Severity
, Dispatch_Goal AS Goal
, sum( Dispatch_Duration ) / sum( Number_Dispatch ) AS Value
FROM Summary_Table
Where...

UNION
--Percent Dispatch Duration Met Goal
SELECT Percent Dispatch Duration Met Goal AS Metric
, Location
, Severity
, Dispatch_Met_Goal AS Goal
, sum(Number_Dispatch_Met_Goal ) / sum( Number_Dispatch ) AS Value
FROM Summary_Table
Where...

Now I dont have a problem writing a statement for each metric, but it seems like this would be a rather wasteful query, as each would have the same where statement. What Id like is some way to either do all of the above in one pass (some kind of CASE statement perhaps?) or some way to pull the data for all the UNION queries in one pass.

View 4 Replies View Related

Turning DB To NON-ARCHIVE Mode

Jan 25, 2008

Hi all,

I am pretty new MS SQL server,
I am actaully working as an SAP BASIS ADMIN and 1 of our client is on MS SQL 2005 with SP1 as the DB and Win 2003 as the server.

I just wanted to know

1) How can we login to the dbase from the command prompt?

2) How to alter the database to NON-ARCHIVE mode and back to ARCHIVE MODE ?

I just wanted to execute few SAP activities for which i dont want the database to generate the archive logs, so i need some assistance.

Hoping to START GOOD here in this forum...
Thanks a million in advance to all

Waiting to hear from all the SQL gurus.

Regards
Hunky

View 5 Replies View Related

Turning Rows Into Columns

Jan 23, 2007

Say I have a table of data containing something likeRegion | County | Year | Month | Valuefor some sort of value (int). I want to re-arrange this data so that itcomes out like this:Region | County | Year | J | F | M | A | M | J | J | A | S | O | N | Dwhere the letters are obviously the months in order. How would I goabout this/what's the best way. I attempted to use 12 INNER JOINS onthe table itself, sadly that failed miserably. Also, this doesn't seemvery efficient?Before you ask I got rid of my original code (gave up!)

View 4 Replies View Related

SSIS Performance Turning

Aug 28, 2007

Hello Everyone,
Can any one update me up performance turning of SSIS and what difference would it make if I change the default value of this two parameter in each Data Flow.
DefaultBuffermaxRows
DefaultBufferSize

Also update me on what is these parameters used for.

Thank you

View 3 Replies View Related

Turning Rows Into Columns

Jan 26, 2006

I have a denormalization question that seems fairly fundamental but I haven't found the answer in BOL. I have data stored in a normalized transaction oriented database that I would like to denormalize to do some queries/analysis. Many tables contain attributes that are virtual columns driven by configuration. I am struggling with how to take those rows of data and turn them into columns of data.

Example Source:

Column1: CustomerId
Column2: AttributeType
Column3: Attribute Value

Ex Data:

123, ShoeSize, 9
123, Age, 45
123, Gender, Male


I would like to turn that into a table with one row, many columns:

CustomerId, ShoeSize, Age, Gender

123, 9, 45, Male



Also, I have other tables that are keyed off of the CustomerId that I would like to append to my ouput table via more columns. For example, a customer's address.

Example Source:
Column1: CustomerId
Column2: AddressLine1
Column3: AddressLine2
Column4: City
Column5: State
Column6: Zip


If I need to combine several tables, should I nest several merge transformations?

Thanks,

Craig

View 3 Replies View Related

SSIS - Turning Unicode OFF

May 1, 2007

All:



When creating a package, SSIS assumes varchar columns as Unicode (DT_WSTR) so before loading data into the target tables, I have to perform a data conversion from DT_WSTR to DT_STR.



Is there any way to turn UNICODE off? So I do not need to do the conversion? Please advise...



Rohan

View 4 Replies View Related

Turning The SQL Server 2005

Aug 8, 2007


Hello,
I€™m loading the Fact table of more then 8 million records. The SQL Server Database is taking hell lots of time to get this insertions and updations. Can any one guide me on turning the SQL Server 2005 Database.



Thank you

View 2 Replies View Related

GROUP BY Without Aggregation -- Is It Possible ?

Jan 14, 2004

Here is what I want to do.

I have a database with HotelInfo

hotelid
hotelname
hotelstate
hotelcity
hotelphone etc etc

I want to display all hotels I have grouped by state

SELECT hotelname, hotelcity, hotelphone
FROM HotelInfo
GROUP BY hotelstate


So basically I was For eg.

TX
hotel1 Dallas xxx-xxx-xxxx
hotel4 Plano xxx-xxx-xxxx

CA
hotel2 San Fransisco xxx-xxx-xxxx




How can I group by without Aggregation ?

View 5 Replies View Related

Aggregation Query

Feb 21, 2003

Hi.
i have a problem that related to aggregation functions.
i have a table (requests)that consist with the following fields:event_id, request_type,data.
i want to present a report that for each event_id it will show the number of records where the request_type value is:"VMR".

i wrote the following query-
SELECT event_id, Count(request_type)
FROM requests
WHERE ((requestType)="VMR")
GROUP BY event_id

the problem is that:if event_id don't has a record with the value "VMR" it will not be showen in the report and the goal is to present those kind of events with the value 0.
who can i make it happen.

thanks.

View 6 Replies View Related

SQL Aggregation Poser

Jul 20, 2005

Can anyone help me with an problem I have come across in my databasedesign.I have a primary table and a related table with 3 child records (eachwith a numeric field). I require a query to return the primary keyfrom the main table and the PRODUCT (i.e. all numeric valuesmultiplied together) of the three child records, much like a SUM wouldadd them together.Any help would be gratefully received!Tony.

View 3 Replies View Related

Sum And Aggregation In A Matrix

Jun 14, 2007

Hello,



I have a matrix where I count number of employees per country, per product line.

The calcul is a simple sum :

(sum(Fields!ID__T_E_Employee_Distinct.Value)).



My report is correct at week level:

March 2007

W9 W10 W11 W12 W13

Product_Line1 17 17 17 17 17

Country Product_Line2 4 4 5 5 5

Product_Line3 25 25 26 25 25

Product_Line4 12 12 12 12 12



However my report is false when I drill-up to month level.



I obtain:

March 2007

Product_Line1 85

Country Product_Line2 23

Product_Line3 126

Product_Line4 60



but I want to obtain:

March 2007

Product_Line1 17

Country Product_Line2 5

Product_Line3 26

Product_Line4 12



For information, my datasource is an OLAP cube.



I have tried to add scope in the sum function but it doesn't work...

Moreover the sum is mandatory for me at week level, but I can have max of the sum at month level.

How to do max of sum in SSRS (I can have only one aggragation function in an expression).



Any help is welcome.



Thanks.

Guillaume.



View 2 Replies View Related

Problem With An Aggregation

Aug 6, 2007

Hi,

I am new to the reporting services and I've been working on problem in one of my reports all day long and after 8 hours of frusturation I decided ask for a profesional help.

Ok here is my problem: I have a report that calculates the amount of meetings with our clients. The dataset contains an activity_id field that we assign for each our meetings with our clients. SSRS counts these meetings and shows it in a drilldown enabled report. Everything seems fine on the report except that someof the activities involves few different clients and SSRS is not counting the activities multiple times in region drilldown as there is only one activity id associates in that region even though it contains different companies. And I want those companies to be calculated in too.

From the crude drawing below I wanted to explain my dilemma visually. As it can be seen the total number of meetings we had is actually 40. But as we had 3 activities that involves more than 1 clients it only gives 37 as a count. I would like to know is there a way to make the report count the same activity multiple times if activity_id is associated with more than one clients.


I hope I managed to explain my problem

**********************************************************************************************
Manager Region Market Company Meeting Detail

+Manager 1 (9 meetings)

+Manager 2 (37 meetings)

- West (37 meetings)







-Denver (37 meetings)



+Company 1 (5 meetings)
+Company 2 (2meetings)
+Company 3 (2meetings)
+Company 4 (3meetings)
+Company 5 (0meetings)
+Company 6 (0meetings)
+Company 7 (5meetings)
+Company 8 (1meetings)
+Comapny 9 (19meetings)
+Company 10 (3meetings)
Total (40 meetings)

View 5 Replies View Related

Aggregation Usage

Apr 10, 2008

I know we can design Aggregation usage for a dimension within a cube. For example, you can choose Full , None, Unrestricted and Default method. I assume this is the Global settings.

Is it possible that Ican do the same thing for the attributes within a dimension? I cannot find such property in attributes. Does this means we can't control which attributes we want Analysis Services to aggregate in a dimension?

View 6 Replies View Related

Aggregation Table

Jul 3, 2007

Hi all



i need to create aggregation table from 2 tables group by date, any one have any idea how to create it by using SSIS



thanks & regards

View 1 Replies View Related

Smart Aggregation

Jan 1, 2007

I'm having problems implementing the following in reporting services 2005.

My hierarchy looks like this (just to illustrate the problem...):

University->Student->Exam

My query returns the following fields:

University,Student,StudentPayment,ExamName,ExamScore

I need to create a report that will show the hierarchy and to smartly aggregate the StudentPayment to both the Student and the University levels.

The problem is that the StudentPayment field is being multiplied by the number of exams in the upper level aggregation.

If only I could set the granularity level of the StudentPayment measurement...

Note that I don't have access to the query, so I can't change anything on that front.

Thanks,

Efi

View 6 Replies View Related

BIT-Wise Aggregation

Oct 29, 2006

Hi,

I have the following three tables :
Account (Id int, AccountName nvarchar(25))
Role (id int, Rights int)
AccountRole (AccountID, RoleID)

In Role table - Rights Column is a bit map where in each bit would refer to access to a method.
One account can be associated with multiple roles - AccountRole table is used for representing the N:N relation.

I want to develop a store procedure - which would return all AccountName and their Consolidated Rights.
Basically I want to do a BitWise OR operation for all the Rights in the Aggregation instead of the SUM as shown in the following statement.

SELECT Account.Name, SUM(Role.Rights) FROM Account WITH (NOLOCK)
JOIN RoleAccount ON RoleAccount.AccountID = Account.Id
JOIN Role ON RoleAccount.RoleId = Role.Id
GROUP BY Account.Name

Thanks,
Loonysan

View 6 Replies View Related

Help Turning This Sql Statement Into A Stored Procedure

Aug 26, 2005

Hello, I need a little help turning this:SELECT RequestNum FROM Tickets WHERE ReceiptDate>='" & FromDate & "' AND ReceiptDate<='" & ToDate & "'"into a sproc because of the two different values (FromDate and ToDate) for the ReceiptDate field in the database.I have this so far (problem areas are ??):Dim AuditConnection As New SqlConnection(ConnString)Dim AuditCommand As New SqlCommand("CreateAudit", AuditConnection)AuditCommand.CommandType = CommandType.StoredProcedureAuditCommand.Parameters.Add(New SqlParameter("@??", SqlDbType.NVarChar)).Value = FromDateAuditCommand.Parameters.Add(New SqlParameter("@??", SqlDbType.NVarChar)).Value = ToDateAuditConnection.Open()Dim AuditResult As SqlDataReader = AuditCommand.ExecuteReader()AuditGrid.DataSource = AuditResultAuditGrid.DataBind()AuditConnection.Close()and:CREATE PROCEDURE CreateAudit    ??    ??ASSELECT    RequestNumFROM    TicketsWHERE    ??AND    ??GOI know I'm an idiot and this should be something simple.  Arrrgh.  Any help is appreciated immensely!!!  :)

View 2 Replies View Related

Question Abt Turning On Svc Brker For Msg Delivery

Apr 4, 2008

hello,

I am trying to enable service broker by issuing this command:

USE master ;
GO
ALTER DATABASE msdb SET ENABLE_BROKER ;
GO

It is taking a while to do that and I am wondering whether msdb needs to be in single user mode? Some smaller dbs completed right away. Going through Surface area config I got a message that I need a service broker endpoint and I looked at my other db and that has dbmail functioning and same message saying this instance needs an endpoint in surface config, what do you think is wrong?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved