I've built a cube which has all the measures and dimensions I want, unfortunately the numbers are off: the product prize is aggregated then rather simply being fetched from the underlying oledb destination file in the data warehouse. So if product x costs 10 USD and has been purchased 3 times in a certain month by a customer, the cube shows a product prize of 30 USD. I've switched the measure's usage from SUM to no aggregations but I still don't get the simple value list without aggregation. Why is this happening?
I am just starting out using CUBEMEMBER/CUBEVALUE formulas in excel linked into a sql olap db - using this method for some custom reports where pivot tables are not suitable. The time dimension values include Months, Quarters and Years and the CUBEMEMBER formulas like
=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1].&[1]") work fine - 1st quarter 1st month etc.
Is there a straightforward notation to aggregate months or do I need to use a plus sign to add a number of CUBEMEMBER formulas together.In other words - Is there an easier way of for say jan to july 2015 totals than
I have an employee, who received an initial starting bonus of $50k. This value will be static from day 1 to day n and will never change. I want to see the intial starting bonus, but any sets higher than the employee dimension will need to aggregate the starting bonus. Is there an easy way to do this?
If I just look at the data from an employee's perspective, I can do this by making the measure a Min, Max, or Avg Aggregate function. But, if for instance I want to view the data from the perspective of departments, it would need to sum() the data instead (which min/max/avg don't do).
If I make the starting bonus a member property of the employee, and a calculated measure off the member property, it aggregates the data when it shouldn't.
Hi All, I have some clarifications on stopping my package once cube is refreshed or processed.
Below i have given steps for the transformation in my package
Let me give you what are all the dataflow transformations that i had given in my package.
1. Data Flow Task
2. Script Task 1- I have written code for getting the last processed cube (global variable has been declared for Last processed cube date - lastProcessedCube)
3. Script Task 2 - I have written code for SS_Batch table where i can get Create_Ts date that is assigned to another global variable - create_ts.
4. Analysis Processing Task.
In between Script Task 2 and Analysis Processing Task i have given @lastProcessedCube > @create_ts for Expression and Constraint under Precedence Constraint Editor
Actually i need to run package for every 10 minutes which i can do it in Job Schedule and need to refresh or process the cube daily. Is there any way to stop the package once when my cube is processed on that day. Again start the package for the next day.... Is it possible to do this? Please let me know.
I'm using a SSIS cube as source for reporting services. In my DataSet I have 3 parameters: Year, ProductGroup, Product. By default all parameters are cascading when defined on the DataSet. This is great for ProductGroup -> Product, but I don't want it for Year -> ProductGroup (I want to avoid the roundtrip). I removed the link in the generated MDX, I removed the parameters on the DataSet, but when I run the report, ProductGroup is still grayed out until I choose a year.
I am using the code below to get all the children of a particular product and it is working fine. How to get the particular product's id in the select statement. for example, i need to show 891 in a separate column for all the records returned by the query below.
DECLARE @Hierarchy TABLE (Product_Id INT, Parent_Product_Id INT) INSERT INTO @Hierarchy VALUES (123, 234) INSERT INTO @Hierarchy VALUES (234, 456) INSERT INTO @Hierarchy VALUES (456, 678) INSERT INTO @Hierarchy VALUES (678, 891) INSERT INTO @Hierarchy VALUES (891, NULL)
I need some help aggregating values in a single table, where neither a simple Sum() nor a simple First() function will do... Would like to do Sum(First()) but that's not allowed!
Sample dataset (select * from cs_view):
Gender | Program | Student | Class_Section | Heads | Credits ------ | ------- | ------- | ------------- | ----- | ------- Female | English | Elena | Phys 101-b | 1 | 4 Female | English | Elena | Hist 101-c | 1 | 4 Female | English | Elena | Engl 101-a | 1 | 4 Female | English | Elena | Engl 105-b | 1 | 4 Male | History | Rich | Phys 105-a | 1 | 4 Male | History | Rich | Engl 101-c | 1 | 4 Male | History | Rich | Hist 101-b | 1 | 4 Male | History | Jacob | Phys 101-a | 1 | 4 Male | History | Jacob | Hist 101-b | 1 | 4 Male | History | Jacob | Engl 101-c | 1 | 4 Male | History | Jacob | Phys L-101-a | 1 | 0
Dataset has one row per student enrollment in class section. No trouble summing credits by student or by program (or gender). HOWEVER, aggregate head-count should add each student only once.
Desired table: Gender Program Heads Credits ------ ------- ----- ------- Female English 1 16 Male History 2 24 - --- 3 40If I add a third grouping level, that is, add a student-level grouping to the desired table, First(Fields!Heads.Value) will return the correct student-level head count; however, I don't know how to sum up the student-level group header rows ('subtotal' rows), to aggregate head count by gender or by program.
I am using the following query (which works fine):
select min(timex) as start_date ,end_date ,entityid ,entityname ,locationid
[code]....
However I would like to not use the delta (it takes effort to calculate and populate it); instead I am wondering if there is any way to calculate it as part / whilst running the query.
Problem 2
I have the following table which shows the location of different people at 1 hour intervals
I'm facing a big problem in my actual report development task... however, what i need to do is (in my opinion) so basic that there has to be a way to do this...
What does it all about:
My customer needs a report in which different measures are shown in rows in a matrix table. columns are reserved for the months, simulating a sort of calendar aspect.
However, the matrix has one additional group in it's rows and the whole matrix is contained within a list.
A possible situation could be following:
Coca Cola
Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec TOT %
The problem are those percentages in red. They are calculated on total amount of BRU/NET without distinction of the first group in the table (PM or IN). If I set the value for the textbox to the following expression:
=(sum(Fields!value.Value)/sum(Fields!value.Value,"Level2"))*100 Where Level2 is the Details specified in the list (in our case Coca Cola or Pepsi Cola) Reporting services makes the sum of the value field without making the distinction of which type of value it is dealing with...
My question is now rather obvious: Is this calculation possible and if yes how?? I've thought to write a custom method in Reporting Services (no assembly) but I've no Idea how i can 1) access the dataset. 2) I need to be able to specifiy the name of the measure for which the calculation needs to be done and 3rd I also need to pass the current detail of the list item to be able to apply the necessary filters on the dataset to perform the aggregation....
I have what I thought would be a simple problem, maybe my approach is all wrong!
Say you have customers, registered in a year/month group. They can be active in another year/month group.
So we get a simple table with registration year/month down the side and activity along the top.
This shows "of all the people p who register in month m, how many are active in month a?"
So assuming that all people registered in month m are also active in month m, the max(activity) for that slice (ie the max value for any active month for the registered month) should be the value if we collapse the activemonth group to activeyear.
Let me try to draw it :D
Registered/Active 2006 2007 ... Jan Feb Mar ... Jan Feb Mar ... ... 2 Jan 20 10 10 20 . 5 3 1 5 20 0 Feb 0 25 15 25 . 8 5 5 8 25 0 Mar 0 0 40 40 . 11 1 6 11 40 6 ... 20 35 65 65 . 24 9 12 24 85 2 Jan 0 0 0 0 . 50 34 44 34 50 0 Feb 0 0 0 0 . 0 45 40 41 45 7 ... 20 35 65 65 . 50 79 44 75 95 ..... 20 35 65 65 . 74 88 56 99 180
I think. The ...'s represent the grouping band total.
The actual schema I'm working against is proprietary and also adds more complication to the problem I'm trying to solve. So to solve this problem, I created a mock schema that is hopefully representative. See below for the mock schema, test data, my initial attempts at the query and the expected results.
-- greatly simplified schema that makes as much sense as the real schema CREATE TABLE main (keyvalue INT NOT NULL PRIMARY KEY, otherdata VARCHAR(10)); CREATE TABLE dates (datekeyvalue INT NOT NULL, keyvalue INT NOT NULL, datevalue DATE NULL, PRIMARY KEY(datekeyvalue, keyvalue)); CREATE TABLE payments (datekeyvalue INT NOT NULL, keyvalue INT NOT NULL, paymentvalue INT NULL, PRIMARY KEY(datekeyvalue, keyvalue));
[Code] ....
Desired results:
SELECT 1 AS keyvalue, 'first row' AS otherdata, '2015-09-25' AS nextdate, 30 AS next_payment UNION ALL SELECT 2, 'second row', '2015-10-11', 150 UNION ALL SELECT 3, 'third row', NULL, NULL
I know I'm doing something wrong in the last query and I believe another sub-query is needed?
Let me answer a few questions in advance:
Q: This schema looks horrible! A: You don't know the half of it. I cleaned it up considerably for this question.
Q: Why is this schema designed like this? A: Because it's a 3rd-party mainframe file dump being passed off as a relational database. And, no, I can't change it.
Q: I hope this isn't a frequently-run query against a large, high-activity database in which performance is mission-critical. A: Yes, it is, and I left out the part where both the date and the amount are actually characters and have to pass through TRY_CONVERT (because I know how to do that part).
I'm trying to create a query to return Open, Close, Max and Min Price for each 1 minute interval. Source data has two fields - Price, and Datestamp at 5 second intervals.
I can calculate the Max and Min (below) and set the datestamp to the middle of the interval, but get stuck on how to also return the Open and Close price for each interval.
SELECT MAX(price) AS MaxPrice, MIN(price) AS MinPrice, DATEADD(ss, 30, DATEADD(n,DATEDIFF n, '1/1/2006', DateStamp),'1/1/2006')) AS DateStamp FROM MasterData GROUP BY DATEDIFF(n, '1/1/2006',DateStamp)
What I am trying to do is count persons in buckets "non-recidivists" and "recidevists" based on how many bkg_nbr they have per STATE_NBR. If they have more than 1 bkg_nbr per STATE_NBR then put them in the "recdivists" bucket. If they only have a 1 to 1 then put them in the "non-recidivists" bucket.
When I make a call to GetSchemaDataset with a restriction of a cube name with a space in the name of the cube the call fails. Following is a sample of the code: adoRestriction = new AdomdRestriction("CATALOG_NAME", "Contoso Telecom_Contoso"); adoRestrictions.Add(adoRestriction); dataSet = conn.GetSchemaDataSet("MDSCHEMA_CUBES", adoRestrictions); I am running SQL Server 2005 Analysis Services SP2. Is there some way to qualify the cube name in the restriction or is this just a bug? Thanks.
I need to analyze the results from Table 1, count how many records there are where one field matches, and then put some of Table 1's information into Table 2 along with the count of the records that match. This is built to interface with another system, so a lot of the information, names, and relationships can't always be changed.
Table 1: tblResourceAllocation Fields (Description):ID (Unique)LastName (Unique alphanumeric string for each employee, but one employee can have multiple records in this table)Project (Unique name, can have multiple entries in this table)Owner (Unique alphanumeric string for each owner, but one owner can have multiple records in this table)ResourceStartDate (datetime)ResourceEndDate (datetime)ServiceID [I](this is what I want to aggregate)Status_FC [I]
Table 2: tblGRFM ID (unique)Pm (the tblResourceAllocation.Owner's actual full name, which I'll look up from an EmployeeData table and concatenate the FirstName and LastName)IdClient (from tblResourceAllocation.Project I will look up the Customer from the Projects table, then look up the Customer ID from the Customers table)US_State_Id (look up State from Projects table then ID from tblStateCodes)Project_name (same as tblResourceAllocation.Project)Status (same as tblResourceAllocation.Status_FC)Id_operlst (same as tblResourceAllocation.ServiceID)Start (earliest start date of relevant entries)end (latest end date of relevant entries)Nb_ress (total number of ResourceAllocation entries that match Id_operlst)Cadence (explanation below)
I need to calculate how many of each serviceID's are in table 1, and then store that calculation and the ServiceID (Id_operlst) in table 2I need to separate those calculations by Owner, IdClient, US_State_Id, and Project_Name I.E. If there are 5 entries in table 1 with ServiceId = X, and Owner A is listed on 2 of them and Owner B is listed on 3, I want it displayed as [Owner A, X, 2] and [Owner B, X, 3] in table 2.
Same goes for the other fields, which obviously adds more complexityWithin those calculations I also need to calculate the Start and end date for table 2. Using the last example, if one entry of Owner A's service X has the early start date and the other entry has the later end date, those dates need to be displayed in the Start and end for table 2 (should I use Rank here?)
I need to create a string of numbers separated by semicolons that shows how many resources are working on a given week after the start date
Example:I have 3 resources on a projectResource 1: 12/31/2012-1/27/2013 (Week 1-4 of our calendar)Resource 2: 1/7/2013-1/27/2013 (Week 2-4)Resource 3: 1/7/2013-1/27/2013 (Week 2-4)tblGRFM.Start will equal 12/31/2012 and tblGRFM.end will equal 1/27/2013Cadence should be equal to "1;3;3;3" to indicate that one resource will be working the project the first week, and three resources will be working the project for the next three weeks.
Hello I installed MS SQl 2005 the eval version and it has expired. I have bought a copy now and i need to put the product keys in without having to reinstall the program. Is there anyway i can do this with having to reinstall SQl again.. Any help would be great
I want to upgrade 5 SQL Server 6.5 production boxes to SQL Server 7. I got SQL Server 7.0 pricing and licensing list from www.microsoft.com/sql/70/gen/pricing.htm and I think I should buy 5 SQL Server 7.0 Combined Product/Version/Competitive Upgrade (Since I have been using SQL Server 6.5, I don’t need to buy the full product. Is this right?). I am not sure if the upgrade offer (the price is much lower than the full product offer $699/$1399) includes the SQL Server 7.0 both software and access license.
To retrieve all products of type 'A', one must know the table name, in this case 'productA'.
Here is one method.
Create a table that contains the table name that corresponds to each product type, thus the stored procedure only needs to recieve the type_id which can be used to obtain the name of the respective table.
I am installing SQL Server x64 on a new server, and I was told to use the same license key we have for the other servers... but no one seems to know where it is. Is there any way I could see the license key for one of the working boxes so I could put it on the new one?
Does anyone know of a SQL Server product that would let me do the following?Connect to the instanceConfigure thresholds like,1.Show databases not backed up in the last X days2. Show databases that are full that have logs not backed up in the lastx days/hours.3. Show jobs that have failed in the last x days.I would then like to click a process button and have it bring back anythingthat is outside those thresholds?, any thing like that in a windowsapplication?
I have a field with numbers (double datatype) as values and I want a aggregate function that gives me the product of all the values in that field. Is there a way to do that in SSRS?
How can the licensing product key with which SQL Server is installed be replaced/updated? If this is possible, will the same procedure apply both to SQL Server 2000 and SQL Server 2005?
We are building a system that has mobile clients (laptop with XP and tablet with XP). These clients have data moving back and forth to a centralised n-tier server based system. The communications is proprietory and are very low bandwidth (5 KByte/sec).
We've decided that the best approach to handling both the reference data (ranging from trivial to a complex list of items - about 2 million rows) and the day-to-day operational data is with a database product rather than attempting to handle it ourselves within code.
The question is - what SQL platform to use? SQL Everywhere seems to be a fairly good choice except that it does not seem to support stored procedures. SQL Express is another possibility but there are concerns about the size of the footprint and managing the database engine and the database itself.
The users of the mobile client are considered computer illiterate and would not be able to manage any database administrative tasks. They only come back to base every three to six months. History has shown remote management of the machine has proven difficult (if not impossible) via the low bandwidth connection.
The machines have 512MB of RAM and only 40GB of disk. They have to support XP, Office, our client, and our GIS client. What is the best answer?
When I was setting up my new SQL server I entered the CD-KEY off of the wrong license. I don't want to have to uninstall everything to reinstall it with the new CD-KEY.
Is there a way to change the CD-KEY after installation?
I have a search box on my website which is used to search the products database. I will be using the search text in an SQL stored procedure that uses LIKE statements. The search string could realistically contain any character. How do I prevent SQL injection when any search string is reasonably feasible? .
I know "select distinct ProductId from Product " selects one product only one time how can i apply the same logic in a query like give bellow SELECT I.QuoteRequestItemId, I.ProductId ,P.StorePartNumber from QuoteRequestItem I left join Product P on I.ProductId = P.ProductId ie product with same productId should be considered only oncethere should not be more than one row with same productId
