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)
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?
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).
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 )
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
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.
--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)
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.
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.
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?
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
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'
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------
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.
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
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!)
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.
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?
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...
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.
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.
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.
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
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?
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
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!!! :)
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?