I have a query where I'm using summarize to return aggregated tabular data.
EVALUATE
(
SUMMARIZE (
CALCULATETABLE (
'Inscricoes',
'Year'[ID] = VALUE(26)
[code]...
I want to add a measure that ranks them 1,2,3,4. In this case it could be the rownumber, but I'm failing to use RANKX under summarize.
According to the book "Microsoft SQL Server 2012 Analysis Services - The BISM Tabular Model" (pages 276 and 277) the following DAX query should work. However, I'll get a rank of 1 returned for every product name. My impression is that ALL doesn't block the context transition for product name. Because there is nothing in the errata, I might do something wrong!? The second query is just a workaround giving the correct result.
EVALUATE
CALCULATETABLE(
SUMMARIZE(
'Internet Sales',
Product[Product Name],
[code]....
Is it possible to find the position of an item without retrieving all the records in the table?
I have a products table, and want to find out how its sales are doing within a particular category.
My table consists of the following columns of interest:
ProductID, CategoryID, ItemsSold.
So, how would I turn the following query into an (not sure if the terminology is correct) aggregate query?SELECT * FROM dbo.Products
WHERE CategoryID = 10
ORDER BY ItemsSoldAlso, is it possible to include the SUM() of the items (in the same category) in the aggregate function or would I need to perform a separate query?Any help would be very much appreciated.Thanks.
I've been trying to solve this problem for better of 4 days:We summarize registrations of students on a daily basis, however theyare net changes.Example:A student registers one class for the first time for the Fall quarteron a Monday. A report would reflect that change for Monday.Next, the same student adds another class on Tuesday. Since the studentwas already counted on Monday, I dont want the student to count onTuesday.On Wednesday, the student decides to drop both classes. Since thestudent no longer has any classes, I wish to decrement the studentcount on Wednesday for that one student.If the same student adds a new class on Friday, then they would counton Friday since their previous classes net to zero.After the end of the session, I would be able to sum up the dailybalance of adds and drops and it would net out to be equal with thetotal number of unique students registered for the quarter.- Students can add and or drop classes on the same day, or on differentdays.- I need to know when the net effect when a student is changed andreflect that quantity on the date for the quarter (SESSION).We have reports on our legacy system written in IBM's Universe (its abusiness basic). Its pretty straight forward as we would traverse thedata using a basic program. However, trying to something in batch inSQL has eluded me.What I am looking to select:What I am looking to select:SESSION DATE STUDENT_ADDS STUDENT_DROPS200602 2005-07-18 1 0200602 2005-08-23 1 02006002 2005-09-30 0 1TIARob(I thought of getting the first registration, and last drop (if any),but it wont work as there can be adds and drops in between)(I ran the DDL this time, and it returned what I expected when Iselected it)CREATE TABLE "DBO"."REGTRACK"("STUDENT_SKEY" INT NOT NULL,"SESSION_ID" CHAR(6) NOT NULL,"FULL_CLASS_ID" CHAR(15) NOT NULL,"ACTIVITY_CODE" CHAR(1) NOT NULL,"ACTIVITY_DT" DATETIME NOT NULL,"ACTIVITY_COUNT" INT)INSERT INTO REGTRACKVALUES(250,'200602','MAT100001024','A',CONVERT(DAT ETIME,'2005-08-23'),1)INSERT INTO REGTRACKVALUES(250,'200602','ENG200001024','A',CONVERT(DAT ETIME,'2005-08-23'),1)INSERT INTO REGTRACKVALUES(260,'200602','BUS100002011','A',CONVERT(DAT ETIME,'2005-07-18'),1)INSERT INTO REGTRACKVALUES(260,'200602','CIS105001011','A',CONVERT(DAT ETIME,'2005-07-18'),1)INSERT INTO REGTRACKVALUES(260,'200602','CIS105002011','A',CONVERT(DAT ETIME,'2005-07-19'),1)INSERT INTO REGTRACKVALUES(260,'200602','CIS105001011','D',CONVERT(DAT ETIME,'2005-07-19'),1)INSERT INTO REGTRACKVALUES(260,'200602','CIS105002011','D',CONVERT(DAT ETIME,'2005-09-30'),1)INSERT INTO REGTRACKVALUES(265,'200602','PAD500001024','A',CONVERT(DAT ETIME,'2005-08-26'),1)INSERT INTO REGTRACKVALUES(266,'200602','CIS110001006','A',CONVERT(DAT ETIME,'2005-09-19'),1)INSERT INTO REGTRACKVALUES(267,'200602','ECO100001004','A',CONVERT(DAT ETIME,'2005-09-07'),1)INSERT INTO REGTRACKVALUES(267,'200602','BUS520012016','A',CONVERT(DAT ETIME,'2005-09-07'),1)INSERT INTO REGTRACKVALUES(267,'200602','BUS520012016','D',CONVERT(DAT ETIME,'2005-10-10'),1)INSERT INTO REGTRACKVALUES(267,'200602','ECO100001004','D',CONVERT(DAT ETIME,'2005-10-10'),1)INSERT INTO REGTRACKVALUES(275,'200602','ITB300001016','A',CONVERT(DAT ETIME,'2005-08-17'),1)INSERT INTO REGTRACKVALUES(275,'200602','BUS310006016','A',CONVERT(DAT ETIME,'2005-08-31'),1)INSERT INTO REGTRACKVALUES(288,'200602','FIN100002016','A',CONVERT(DAT ETIME,'2005-07-28'),1)INSERT INTO REGTRACKVALUES(288,'200602','POL300003016','A',CONVERT(DAT ETIME,'2005-07-28'),1)INSERT INTO REGTRACKVALUES(288,'200602','FIN100002016','D',CONVERT(DAT ETIME,'2005-08-30'),1)INSERT INTO REGTRACKVALUES(288,'200602','MKT200002016','A',CONVERT(DAT ETIME,'2005-08-30'),1)INSERT INTO REGTRACKVALUES(321,'200602','CIS105004010','A',CONVERT(DAT ETIME,'2005-08-03'),1)INSERT INTO REGTRACKVALUES(321,'200602','BUS100005010','A',CONVERT(DAT ETIME,'2005-08-03'),1)INSERT INTO REGTRACKVALUES(321,'200602','CIS105004010','D',CONVERT(DAT ETIME,'2005-11-15'),1)INSERT INTO REGTRACKVALUES(321,'200602','BUS100005010','D',CONVERT(DAT ETIME,'2005-11-28'),1)INSERT INTO REGTRACKVALUES(243172,'200602','ENG102001001','A',CONVERT( DATETIME,'2005-09-21'),1)INSERT INTO REGTRACKVALUES(243172,'200602','CIS105023016','A',CONVERT( DATETIME,'2005-09-21'),1)INSERT INTO REGTRACKVALUES(243172,'200602','ACC100002010','A',CONVERT( DATETIME,'2005-09-21'),1)INSERT INTO REGTRACKVALUES(334,'200602','MAT300009016','A',CONVERT(DAT ETIME,'2005-08-29'),1)INSERT INTO REGTRACKVALUES(334,'200602','CIS111009016','A',CONVERT(DAT ETIME,'2005-08-29'),1)INSERT INTO REGTRACKVALUES(256542,'200602','CIS460002016','A',CONVERT( DATETIME,'2005-09-28'),1)INSERT INTO REGTRACKVALUES(256542,'200602','CIS500019016','A',CONVERT( DATETIME,'2005-09-28'),1)INSERT INTO REGTRACKVALUES(349,'200602','CIS500001003','A',CONVERT(DAT ETIME,'2005-09-22'),1)INSERT INTO REGTRACKVALUES(255713,'200602','BUS520008016','A',CONVERT( DATETIME,'2005-08-30'),1)INSERT INTO REGTRACKVALUES(359,'200602','BUS531001029','A',CONVERT(DAT ETIME,'2005-09-01'),1)INSERT INTO REGTRACKVALUES(359,'200602','CIS514001029','A',CONVERT(DAT ETIME,'2005-09-01'),1)INSERT INTO REGTRACKVALUES(367,'200602','ENG102005001','A',CONVERT(DAT ETIME,'2005-09-16'),1)INSERT INTO REGTRACKVALUES(367,'200602','ENG102005001','D',CONVERT(DAT ETIME,'2005-10-26'),1)INSERT INTO REGTRACKVALUES(367,'200602','ENG102005001','A',CONVERT(DAT ETIME,'2005-11-08'),1)INSERT INTO REGTRACKVALUES(368,'200602','CIS110003016','A',CONVERT(DAT ETIME,'2005-08-16'),1)INSERT INTO REGTRACKVALUES(368,'200602','HUM300001016','A',CONVERT(DAT ETIME,'2005-08-16'),1)INSERT INTO REGTRACKVALUES(369,'200602','BUS530011016','A',CONVERT(DAT ETIME,'2005-09-13'),1)INSERT INTO REGTRACKVALUES(381,'200602','BUS100026016','A',CONVERT(DAT ETIME,'2005-08-02'),1)INSERT INTO REGTRACKVALUES(381,'200602','ECO405001016','A',CONVERT(DAT ETIME,'2005-08-02'),1)INSERT INTO REGTRACKVALUES(385,'200602','BUS100002008','A',CONVERT(DAT ETIME,'2005-07-27'),1)INSERT INTO REGTRACKVALUES(385,'200602','BUS107001008','A',CONVERT(DAT ETIME,'2005-07-27'),1)INSERT INTO REGTRACKVALUES(249922,'200602','ECO405008016','A',CONVERT( DATETIME,'2005-09-12'),1)INSERT INTO REGTRACKVALUES(249922,'200602','POL300011016','A',CONVERT( DATETIME,'2005-09-12'),1)INSERT INTO REGTRACKVALUES(249922,'200602','HUM100022016','A',CONVERT( DATETIME,'2005-09-12'),1)INSERT INTO REGTRACKVALUES(249922,'200602','HUM100022016','D',CONVERT( DATETIME,'2005-10-03'),1)INSERT INTO REGTRACKVALUES(395,'200602','HUM400011016','A',CONVERT(DAT ETIME,'2005-08-17'),1)INSERT INTO REGTRACKVALUES(395,'200602','CIS499001016','A',CONVERT(DAT ETIME,'2005-08-17'),1)INSERT INTO REGTRACKVALUES(395,'200602','CIS499002016','A',CONVERT(DAT ETIME,'2005-09-21'),1)INSERT INTO REGTRACKVALUES(395,'200602','CIS499001016','D',CONVERT(DAT ETIME,'2005-09-21'),1)INSERT INTO REGTRACKVALUES(395,'200602','CIS499001015','A',CONVERT(DAT ETIME,'2005-09-22'),1)INSERT INTO REGTRACKVALUES(395,'200602','CIS499002016','D',CONVERT(DAT ETIME,'2005-09-22'),1)INSERT INTO REGTRACKVALUES(397,'200602','ENG095001001','A',CONVERT(DAT ETIME,'2005-09-19'),1)INSERT INTO REGTRACKVALUES(397,'200602','ENG096001001','A',CONVERT(DAT ETIME,'2005-09-19'),1)INSERT INTO REGTRACKVALUES(397,'200602','ENG097001001','A',CONVERT(DAT ETIME,'2005-09-19'),1)INSERT INTO REGTRACKVALUES(398,'200602','HUM200005016','A',CONVERT(DAT ETIME,'2005-08-05'),1)INSERT INTO REGTRACKVALUES(398,'200602','HUM400004016','A',CONVERT(DAT ETIME,'2005-08-05'),1)INSERT INTO REGTRACKVALUES(398,'200602','CIS427001016','A',CONVERT(DAT ETIME,'2005-08-05'),1)INSERT INTO REGTRACKVALUES(406,'200602','ECO550008016','A',CONVERT(DAT ETIME,'2005-08-01'),1)INSERT INTO REGTRACKVALUES(406,'200602','MAT540004016','A',CONVERT(DAT ETIME,'2005-08-01'),1)INSERT INTO REGTRACKVALUES(406,'200602','MAT540004016','D',CONVERT(DAT ETIME,'2005-11-14'),1)INSERT INTO REGTRACKVALUES(429,'200602','POL300006016','A',CONVERT(DAT ETIME,'2005-08-03'),1)INSERT INTO REGTRACKVALUES(429,'200602','SOC300006016','A',CONVERT(DAT ETIME,'2005-08-03'),1)INSERT INTO REGTRACKVALUES(429,'200602','ACC403003016','A',CONVERT(DAT ETIME,'2005-09-01'),1)INSERT INTO REGTRACKVALUES(429,'200602','SOC300006016','D',CONVERT(DAT ETIME,'2005-09-01'),1)INSERT INTO REGTRACKVALUES(433,'200602','ACC560001021','A',CONVERT(DAT ETIME,'2005-09-19'),1)INSERT INTO REGTRACKVALUES(433,'200602','MAT540001021','A',CONVERT(DAT ETIME,'2005-09-19'),1)INSERT INTO REGTRACKVALUES(433,'200602','BUS531001021','A',CONVERT(DAT ETIME,'2005-09-19'),1)INSERT INTO REGTRACKVALUES(433,'200602','ACC560001021','D',CONVERT(DAT ETIME,'2005-09-27'),1)INSERT INTO REGTRACKVALUES(433,'200602','ENG102001021','A',CONVERT(DAT ETIME,'2005-09-28'),1)INSERT INTO REGTRACKVALUES(433,'200602','BUS533001021','A',CONVERT(DAT ETIME,'2005-09-28'),1)INSERT INTO REGTRACKVALUES(433,'200602','ACC560001021','A',CONVERT(DAT ETIME,'2005-09-28'),1)INSERT INTO REGTRACKVALUES(433,'200602','BUS531001021','D',CONVERT(DAT ETIME,'2005-09-28'),1)INSERT INTO REGTRACKVALUES(433,'200602','MAT540001021','D',CONVERT(DAT ETIME,'2005-09-28'),1)INSERT INTO REGTRACKVALUES(433,'200602','ENG102001021','D',CONVERT(DAT ETIME,'2005-09-29'),1)INSERT INTO REGTRACKVALUES(448,'200602','ENG102013016','A',CONVERT(DAT ETIME,'2005-09-27'),1)INSERT INTO REGTRACKVALUES(448,'200602','HUM101013016','A',CONVERT(DAT ETIME,'2005-09-27'),1)INSERT INTO REGTRACKVALUES(459,'200602','HUM101002010','A',CONVERT(DAT ETIME,'2005-08-25'),1)INSERT INTO REGTRACKVALUES(459,'200602','BUS310001010','A',CONVERT(DAT ETIME,'2005-08-25'),1)INSERT INTO REGTRACKVALUES(466,'200602','HUM100004016','A',CONVERT(DAT ETIME,'2005-07-18'),1)INSERT INTO REGTRACKVALUES(466,'200602','CIS111003016','A',CONVERT(DAT ETIME,'2005-07-18'),1)INSERT INTO REGTRACKVALUES(479,'200602','BUS100050016','A',CONVERT(DAT ETIME,'2005-09-20'),1)INSERT INTO REGTRACKVALUES(253486,'200602','ENG102001012','A',CONVERT( DATETIME,'2005-10-05'),1)INSERT INTO REGTRACKVALUES(253486,'200602','MAT105001012','A',CONVERT( DATETIME,'2005-10-05'),1)INSERT INTO REGTRACKVALUES(490,'200602','BUS532001003','A',CONVERT(DAT ETIME,'2005-09-20'),1)INSERT INTO REGTRACKVALUES(509,'200602','ENG102021016','A',CONVERT(DAT ETIME,'2005-10-01'),1)INSERT INTO REGTRACKVALUES(509,'200602','MAT100021016','A',CONVERT(DAT ETIME,'2005-10-01'),1)INSERT INTO REGTRACKVALUES(511,'200602','LEG100001012','A',CONVERT(DAT ETIME,'2005-08-29'),1)INSERT INTO REGTRACKVALUES(556,'200602','LEG100013016','A',CONVERT(DAT ETIME,'2005-09-24'),1)INSERT INTO REGTRACKVALUES(556,'200602','SOC304001003','A',CONVERT(DAT ETIME,'2005-09-24'),1)INSERT INTO REGTRACKVALUES(576,'200602','ACC100002026','A',CONVERT(DAT ETIME,'2005-08-30'),1)INSERT INTO REGTRACKVALUES(576,'200602','BUS100043016','A',CONVERT(DAT ETIME,'2005-08-30'),1)INSERT INTO REGTRACKVALUES(581,'200602','CIS288001010','A',CONVERT(DAT ETIME,'2005-09-08'),1)INSERT INTO REGTRACKVALUES(581,'200602','CIS450001002','A',CONVERT(DAT ETIME,'2005-09-08'),1)INSERT INTO REGTRACKVALUES(581,'200602','CIS286001002','A',CONVERT(DAT ETIME,'2005-09-08'),1)INSERT INTO REGTRACKVALUES(583,'200602','BUS490001017','A',CONVERT(DAT ETIME,'2005-08-09'),1)INSERT INTO REGTRACKVALUES(583,'200602','SOC300004016','A',CONVERT(DAT ETIME,'2005-08-09'),1)INSERT INTO REGTRACKVALUES(583,'200602','BUS490001017','D',CONVERT(DAT ETIME,'2005-09-07'),1)
View 2 Replies View RelatedHi champs!
The data i have is like this:
nr date value
------- -------------- --------
1 2007-10-03 45
1 2007-10-05 5
1 2007-10-11 -1
1 2007-10-30 23
2 2007-03-03 3
2 2007-03-13 -5
2 2007-03-03 6
3 2007-10-03 42
3 2007-10-03 11
.....
I want to summerize the value in each group and set the date to the 1'st og that month
i.e.
nr date value
------- -------------- --------
1 2007-10-01 72
2 2007-03-01 4
3 2007-10-01 53
any help is much appreciated.
thanks
I recieve duplicate customer bill data which I want to consolidate using SQL query. How to summarize data using single query...both Qty and Price. If either one of price or qty is -ve then both should be -ve.
Here is example data for one customer.
1 Only Qty is negative
Product Qty Price
Shirt 2 6.00
Shirt -1 6.00
-------------------------------------------------------------------------------------------------
Shirt 1 6.00 Result
2 - Only price is negative
Product Qty Price
Pant 2 6.00
Pant 1 -6.00
-------------------------------------------------------------------------------------------------
Pant 1 6.00 Result
Thanks for help!!
Pintoo
I have 2 tables that I would like to summarize a couple of columns for a full day of production(12:00:00 AM to 11:59:59 PM) based on passed variables. Here are my Tables:
Order_Details_tbl
PlantID – IngredientID – AmountBatched – DateTime
1 – 8 – 1000 – 4/30/2012 1:23:12 PM
1 – 8 – 1000 – 4/30/2012 4:23:12 PM
1 – 8 – 1000 – 5/1/2012 1:23:12 PM
1 – 8 – 1000 – 5/1/2012 10:23:12 PM
1 – 8 – 4500 – 5/3/2012 1:23:12 PM
1 – 8 – 11000 – 5/7/2012 1:23:12 PM
1 – 8 – 1000 – 5/7/2012 10:23:12 AM
1 – 8 – 1000 – 5/7/2012 1:23:12 PM
1 – 8 – 1000 – 5/7/2012 1:23:12 PM
1 – 8 – 1000 – 5/8/2012 9:23:12 AM
1 – 8 – 1000 – 5/8/2012 4:23:12 PM
1 – 8 – 1000 – 5/8/2012 2:23:12 PM
Order_Details_Details_tbl
PlantID – IngredientID – AmountBatched – DateTime
1 – 8 – 100 – 4/30/2012 1:23:12 PM
1 – 8 – 11000 – 5/4/2012 11:23:12 PM
1 – 8 – 11000 – 5/7/2012 11:23:12 PM
1 – 8 – 1000 – 5/8/2012 11:23:12 AM
1 – 8 – 1000 – 5/8/2012 1:23:12 AM
1 – 8 – 1000 – 5/8/2012 11:23:12 PM
1 – 8 – 1000 – 5/8/2012 5:23:12 PM
1 – 8 – 1000 – 5/8/2012 2:23:12 PM
I will pass in the @PlantID int, @IngredientID int, and @Days int. I want to sum the AmountBatched from both tables and display the total for each given day. The @Days will indicate the number of days to query off of previous to the current date. I would also like to eliminate weekends from the results. For example when stored procedure is run passing the following values @PlantID = 1, @IngredientID = 8, and @Days = 14. If date procedure is run is 5/9/2012, would like to summarize for 4/25/2012 to 5/8/2012 excluding weekends if possible.
Results
Date – AmountBatched
4/25/2012 – 0
4/26/2012 – 0
4/27/2012 -- 0
4/30/2012 -- 2100
5/1/2012 -- 2000
5/2/2012 -- 0
5/3/2012 -- 4500
5/4/2012 -- 11000
5/7/2012 -- 25000
5/8/2012 – 8000
Notice 4/28, 4/29, 5/5, and 5/6 are eliminated from the results, which are weekends. Is this possible in a sql stored procedure? I am writing an app in vb .net and am hoping to get the results I need in a single call to sql server and not have to make several calls back. I have not worked with advanced datetime methods in sql server before.
Help! I'm very tired (and new at this) and have looked for a solution in many places. I have an Employee table with a one to many Revenue table. All revenue types are in this table. I need the goals and actuals (two different revenue types) for a datagrid.
This is the result. Because I am looking at two revenue types, the result is providing 2 rows of data instead of one. what is the best way to combine this.
Region FullName SHARP Year Ann Goal YTD Goal YTDActual
Region1 Doe10, John X 2003 20400 5100 0 Select
Region1 Doe10, John X 2003 0 0 3987 Select
Region1 Doe11, John X 2003 29645 7411.25 0 Select
Region1 Doe11, John X 2003 0 0 5377 Select
Here's my stored procedure:
CREATE PROCEDURE spFilterRegion
@RIDsent As Integer,
@StatusSent As Integer,
@SelectedRegion As NVARCHAR (50) Output
AS
SELECT Region.CountryID,
Employee.RegionID,
Employee.StatusID,
Employee.SHARP,
CASE
When Employee.SHARP = 1 THEN "X"
ELSE ""
END AS SHARPresult,
Employee.LastName,
Employee.FirstName,
Employee.LastName + ', ' + FirstName AS FullName,
Employee.EmployeeID,
Region.RegionName,
ProducerRevenue.RevenueTypeID,
CASE
When ProducerRevenue.RevenueTypeID = 1 Then
SUM(ProducerRevenue.Revenue)
ELSE 0
END AS AnnGoal,
CASE
When ProducerRevenue.RevenueTypeID = 1 Then
SUM(ProducerRevenue.Revenue)/DATEPART(mm, GETDATE())
ELSE 0
END AS YTDGoal,
CASE
When ProducerRevenue.RevenueTypeID = 2 Then
SUM(ProducerRevenue.Revenue)
ELSE 0
END AS Actual,
ProducerRevenue.YearID
FROM Employee
LEFT OUTER JOIN ProducerRevenue
ON Employee.EmployeeID = ProducerRevenue.EmployeeID AND
ProducerRevenue.YearID = DATEPART(yy, GETDATE()) - 1 AND
ProducerRevenue.MonthID < DATEPART(mm, GETDATE()) AND
ProducerRevenue.StatusID = 1 AND
ProducerRevenue.RevenueTypeID <= 2
LEFT OUTER JOIN Region
ON Employee.RegionID = Region.RegionID
WHERE Employee.StatusID = @StatusSent AND
Employee.RegionID = @RIDsent AND
Employee.RoleID = 1
GROUP BY Region.CountryID,
Employee.RegionID,
Region.RegionName,
Employee.RoleID,
Employee.StatusID,
Employee.SHARP,
Employee.LastName,
Employee.FirstName,
Employee.EmployeeID,
ProducerRevenue.RevenueTypeID,
ProducerRevenue.YearID
ORDER BY Region.CountryID,
Employee.RegionID,
Employee.RoleID,
Employee.StatusID,
Employee.SHARP,
Employee.LastName,
Employee.FirstName,
Employee.EmployeeID,
ProducerRevenue.RevenueTypeID,
ProducerRevenue.YearID
I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.
Any help would be greatly appreciated.
Current Table
Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20
Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35
Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40
Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40
Proposed Table
Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007
Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008
Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008
Thanks,
Mike Misera
I have, a SSAS 2012 tabular instance with SP2, there is a database on the instance with a read role with everyone assigned permissions. When configuring the Power BI analysis services connector, at the point where you enter Friendly Name, Description and Friendly error message, when you click next I receive the error "The remote server returned an error (403)." I've tested connecting to the database from Excel on a desktop and connect fine.I don't use a "onmicrosoft" account so don't have that problem to deal with.
We use Power BI Pro with our Office 365. As far as I can tell that part is working ok as I pass that stage of the configuration with a message saying connected to Power BI.The connector is installed on the same server as tabular services, its a Win2012 Standard server. The tabular instance is running a domain account that is the admin account for the instance (this is a dev environment) that account is what I've used in the connector configuration. It's also a local admin account. There is no gateway installed on the server.
I have a cube that we are processing nightly via an Analysis Service Processing Task in SSIS. In order to increase the performance of the processing time, we elected to use a lot of rigid dimension attributes, and do a full process of everything in the SSIS task. The issue that I am having is that after that task completes, I need to go into Visual Studio to deploy the cube becuase we are unable to browse or use the cube. This issue seemed to start once we changed the SSIS Analysis Service Processing Task to do a full process on the dimensions, rather than an incremental.
I would expect that once development is done, and it is processed and deployed, that is it. My thinking is that the SSIS task should just update the already deployed cube,
How to right choose key column in"Mining Structure" for Microsoft Analysis Services?
I have table:
"Incoming goods"
Create table Income (
ID int not null identity(1, 1)
[Date] datetime not null,
GoodID int not null,
PriceDeliver decimal(18, 2) not null,
PriceSalse decimal(18, 2) not null,
CONSTRAINT PK_ Income PRIMARY KEY CLUSTERED (ID),
CONSTRAINT FK_IncomeGood foreign key (GoodID) references dbo.Goods ( ID )
)
I'm trying to build a relationship(regression) between “Price Sale” from Good and “Price Deliver”.But I do not know what column better choose as “key column”: ID or GoodID ?
It is possible to create Analysis Services project (*.dwproj) in Visual Studio 2012 Data Tools?
View 5 Replies View RelatedI am using
SSAS: version 2008
Excel: version 2013
I am connecting to SSAS cube from Excel and I have date dimension with 4 fields (I have others but I don't use it for this case). I created 4 fields in order to test all possible scenarios that I could think of:
DateKey:
- Type: System.Integer
- Value: yyyyMMdd
Date:
- Type: System.DateTime
DateStr0:
- Type: System.String
- Value: dd/MM/yyyy (note: I am not using US culture)
- Example: 01/11/2015
DateStr1:
- Type: System.String
- Value: %d/%M/yyyy (note: I am not using US culture)
- Example: 1/11/2015
Filtering on date is working fine:
Initially, in excel, filtering on date was not working. But after changing dimensional type to time, and setting DataType to Date, as mentioned in [URL] filter is working fine as you can see in the picture.Grouping on date is not working:
I have hierarchy in my Date dimension and I can group based on hierarchy, no problem. But user is used to pre-build grouping function of excel, and he wants to use that. Pre-build functions of Excel, Group and ungroup seems to be available as you can see in following picture:
But when user clicks 'Group', excel groups it as if it is a string, and that is the problem. User wants to group using pre-build grouping function available in Pivot table. I also find out that Power Pivot Table does not support this excel grouping functionality. And if I understood well, this pre-build grouping functionality of excel, needs to do calculation at run time, and that is not viable solution if you have millions of rows. So Power pivot table does not support pre-build grouping functionality of excel and hence we need to use dimension hierarchy to do the grouping. But I am not using Power Pivot table, I am using simple Pivot Table. So I expect grouping functionality to be working fine. Then I tried to do simple test. I created a simple data source in excel itself. And use it as source of my Pivot table. Then grouping is working fine. The only difference that I can see is (When double click the Measure value in Excel),For date values of my simple test, excel consider them as 'Date'.
For date values of my data coming from cube, excel consider them as 'General'
2.1. But value here is same as it was in simple test.
2.2. 'Date Filter' works just fine.
2.3. If I just select this cell and unselect it, then excel change type to 'Date' though for that cell.
2.4. I have created 4 different types of fields in my date dimension thinking that values of attribute of my dimension might be the problem, but excel consider 'General' for all of them.
2.5 This value (that can be seen when double clicking on measure) comes from 'Name Column' of the attribute. And the DataType defined is WChar. And I thought that might be the reason of issue. And I changed it to 'Date'. But SSAS does not allow it to change to 'Date' giving error : The 'Date' data type is not allowed for the 'NameColumn' property; 'WChar' should be used.
So, I don't know, what is the puzzle piece that I am missing.
1. Date filter works, group does not work
2. Excel consider it as 'General' string.
3. SSAS does not allow to change 'NameColumn' to Date.
I would like to know the best practice for running analysis service in terms of port usage. Is it better to run on a specific port or have dynamic ports ? We have clustered servers that run default on 2383 but not sure with non clustered what's the best way to get performance.
View 2 Replies View RelatedI want to find out if it is possible to create a role in a cube without an AD account, e.g. using a GMail email address.
View 3 Replies View RelatedHi,
I have some questions about SQL Servers 2000 and 2005 compatibility.
In my configuration I have to use both servers.
The cubes are stocked in 2005 server.
May I transfer from 2005 to 2000 Analysis Services the cubes?
If yes, what is the procedure? The result of migration is the same in the two different versions?
If not, how can I solve this problem?
Thanks in advance.
I'm looking to see if I have a issue with excessive IO activity caused by less-than-ideal access patterns.
So I've run this query to look at logical and physical reads for logical writes:
selectsql_handle, plan_handle,
total_physical_reads, last_physical_reads,
min_physical_reads, max_physical_reads,
total_logical_writes, last_logical_writes,
min_logical_writes, max_logical_writes,
total_logical_reads, last_logical_reads,
min_logical_reads, max_logical_reads
fromsys.dm_exec_query_stats
The question is, when can it be considered excessive?
Or can you just order the results by descending order and see if you can make improvements on the worst ones?
The same goes for this query for pageiolatch wait counters and times for indexes:
select database_id, object_id, index_id, partition_number, page_io_latch_wait_count, page_io_latch_wait_in_ms
from sys.dm_db_index_operational_stats (null,null,null,null)
I have SQL query, and I am trying to write MDX with similar logic. Logic has where clause:
Where (((CD_TYP_CAT_PARENT = 'LOAD' OR CD_TYP_CAT_PARENT = 'SPEND') AND (CD_TYP_CAT NOT IN('REVERSAL','REFUND','FEE')))
OR (CD_TYP_CAT_PARENT = 'OTHER' AND CD_TYP_CAT = 'SPEND'))
how do I write this in MDX?
I just ran a testbed of 4 types of SQL queries:1. inline SQL with a StringBuilder2. managed sql3. SQL text processing (@SQL as varchar(5000); SET @SQL = 'SELECT ' + @var...)4. a regular sproc that has the columns and table name hard coded1,2, and 4 always end up at about the same time given the averages.3 is always at last 1.5 times slower, and usually closed to 2 times.1 and 2 both use StringBuilders, the code is a direct copy, and 3 is a copy as well.My managed SQL is: [Microsoft.SqlServer.Server.SqlProcedure] public static void usp_Items_Select_Managed(SqlString table, SqlString name, SqlString value) { // sql StringBuilder stringBuilder = new StringBuilder(); stringBuilder.AppendFormat( "SELECT {0}.* FROM {0} WHERE {0}.{1} = {2}", table, col, value ); SqlConnection sqlConnection = new SqlConnection("context connection=true"); SqlCommand sqlCommand = new SqlCommand(stringBuilder.ToString(), sqlConnection); sqlConnection.Open(); SqlContext.Pipe.Send(sqlCommand.ExecuteReader()); sqlConnection.Close(); }Is there anything wrong with my Managed SQL, or is this just the way that it is?Thanks
View 1 Replies View RelatedHello ,
I have a SQL 2000 with Analysis server installed on it . Its in a different domain . When i am trying to register the server remotely i.e from another domain ,from another server it gives me an error that :
'Cannot connect to the repository .
Analysis Server : <Server_name>
Error : Cannot open database requested in Login '<Database_name > Login fails . Do you wish to register this server ? '
Both the domains are trusted . Except that the OS where the Analysis server is running is Windows 2000 . I am trying to connect through SQL 2000 Server with Windows NT 4.0 Server .
We have another Analysis server which is running but on Windows NT 4.0 OS where i can easily register that server .
I tried providing access with all the rights to concerned domains , but in vain .
I am exhausted trying to figure out the problem .
Any assistance in this regard would be of great help to me .
Thanks
Ravi .
Hello,
I am tuning some queries and am using the Display Execution Plan option from Query Analyzer. What I am looking to for is somewhere which will explain the differences between the costs displayed when you put the mouse pointer over the object in the Execution plan output. BOL gives some general directions, but I need more specifics.
Any help on this would be appreciated
Thanks,
Bill
Hi all,
Is there any method by which I can use Stored procedures in Analysis services. I have some procedures which uses Temprorary tables. I wanna use those procedures's columns. Is it possible?
Thanks in advance!
Rahul
Hi,
Im pretty much new to AS and would like to ask some silly questions why I cant browse the data in my cube.
Version - SQL2K Developers Edition on W2K OS
Problem:
Im doing the tutorial which comes with Analysis Services. I have successfully followed their instructions and created and processed the 'Sales' cube.
However when i browse the cube, i get the error Unable to browse the cube 'Sales'. Unspecified error
Would anyone know why?
thanks in advance.
will anyone help me as iam new to AS and i created the cube using the sample tutorial.but when i try to browse the cube it is showing an error as "unable to browse the cube,unspecified error'.pls help me thanx in adv
View 3 Replies View Relatedhello everybody
I am new to bi project.my client want user activities to be captured into chart and cube.Into this context i just started reading analysis services.i read some document and implemented one simple analysis service project using adventureworksdw database.
My question is this like i have to work on analysis services using my own db.so directly can i use clint db(ie user table) or i need to write some script to change this db into a db which can produce cube and dimention.
Thanks in advance
I installed MS Analysis Services in my Windows XP (with SP1) desktop. When I try to expand the "+" sign beside the server, I got the following error message: "Unable to connect to the registry on the server, or you are not a member of the OLAP Administrators group on this server."
It is very weird that occasionally I can connect to the server without the above problem. But most of time, I cannot connect to the server and will get the above the error message.
Anyone knows how to fix this problem?
Thanks a lot!
A lot of job postings these days for SQL DBA's list the requirement of Analysis Services. Not having any experience with it I'm wondering what exactly is meant? Of course its going to vary from company to company, but I really have no clue what they mean in general? Is it cube design? MDX query writing? Simply backing up? To be clear, Im asking in regards specificically for a DBA, not a Developer.
TIA, ChrisR
I have been looking at Microsoft Business Intelligence tools. SQL Server 2005. I see great potential with Analysis server, and creating data cubes for reporting. This will offload much processing from my transactional servers.
I just haven't been able to identify the process for automatically updating my cubes over night. The documentation leaves me baffled.
Any assistance is greatly appreciated.
Hi,
I am running XP in the office and already have SQL's Enterprise Manager and Analysis services installed - which I can use to access the SQL databases on our office server.
Question:
I have installed the desktop version of SQL on my machine and am trying to register the server in the Analysis Service Manager but no luck.
Kindly help.
THanks
Hi
I was doing steps on page 15 / 16 of attached sheet
I was doing the microsoft example about cube to setup DSN SOURCE CONNECTION to ACCESS database (food mart) when i do a test it works
but after i do design storage and go to Process the cube
it gives me error
'test connection failed because of an error in initializing provide
(microsoft) ODBC DRIVER manager data source name not found and no default driver specified
one more error found
Microsoft ODBC DRIVER MANAGER driver sql connect attr failed: IMOO6
Thanks
Hi.
I have a problem with the caculation of the dimensions in the Analysis
Services and I need help.
In a certain dimension I make some calculations with the "personalized member formulas". Some cells are modified after this calculations. After this, in another dimension, other calculations are made using again the "personalized member formulas". With this operations some cells that were modified in the previous dimension should be modified again, but they aren't. Is there a way to fix the sequence of the calculation of the dimensions? Do they get calculated at the same time or some get done before others? Once one of this cells are modified using those formulas is it possible to modify them again? In the Hyperion's Essbase software ther is an option to calculate some dimensions before others. Does Analysis Services has that option or something similar?.
I hope you can help me. Thank you very much.
Hi,
I've got a stored procedure and a view that do the same thing and i would like to figure out which one does what faster. I use SQL Server... what are some ways for me to figure out which is better in terms of performance?