Analysis :: Parallel-period Equivalent For A Time Range?
Jan 27, 2009
Our client wants to report on their trade volume for last year as compared to the current quarter. For simplicity let's pretend they have a report where they have a two key measures:
is based on outlook - that is, for any period we are reporting on the trade volume will be reported as actuals that have been loaded up until the current period, and forecast for the current and future periods.
[Trade Volume - Tons MTD] is based only on actuals - that is for any period we are reporting on the trade volume will be reported as actuals that have been loaded up until and including the current period, and 0 for any future periods.
If Feb09 is our current period, and we are using quarter on the time dimension (where quarter 1=Jan09,Feb09,Mar09) and we have the following data:
This is a problem, because the comparison with their current results ([Trade Volume - Tons MTD]) with what they 'forecast' ([Trade Volume - Tons]) is not based on the same period of time - we are comparing the sum of two periods versus three periods.To solve this we changed the reporting period to be monthly granularity, and now select Jan09-Feb09 as our range (as opposed to having a quarter granularity and selecting Q1,2009 in the example above).
This works well and produces the expected results:
However, this introduces a secondary problem: we are doing a prior year calculation on the Trade Volume also, so the users can compare how the actuals are comparing to the same period last year.To do this we use the following formula for the prior year calculation:
Prior Year Actuals=([Measures].[Trade Volume - Tons], ParallelPeriod([Time].[544 Hierarchy].[Period Year],1,[Time].[544 Hierarchy].currentmember))
The problem is as soon as we move from quarter granularity to (monthly granularity AND select more than one monthly period) the Prior Year Actuals calculation produces a an error "The MDX function CURRENTMEMBER failed because the coordinate for the 'Period Year' attribute contains a set".So, ParallelPeriod does not like it when currentmember is a range (Jan09,Feb09) rather than a single period (Jan09).
I have make a calculated member for previous period of an given date range. The previous period is the same date range from the previous year, and I have managed to achieve that with the calculated member:
Create member currentcube.[Measures].[PrevPeriod] as (ParallelPeriod( [Start Date].[Cal Hierarchy].[Year], 1, [Start Date].[CAL Hierarchy].CurrentMember), [Measures].[Count]);
This member returns the correct result as long as my query uses the time dimension, which makes sense... but I also need to show results sliced by other dimensions in bar charts that do not display the time dimension. For example, I have a dimension with only 3 members called [Region].[Area].[AreaName].
The result set for the bar chart needs to look like this:
[AreaName] | [Count] | [PrevPeriod] East | 43 | 56 West | 53 | 95
But the [PrevPeriod] only returns values if I include the time dimension. I essentially need to sum the results of the time dimension/AreaName/[PrevPeriod] tuple down to just Areaname/[PrevPeriod] for whatever date range may be involved.
I don't know if this is significant to the issue, but the client tool that generates the bar charts builds the query with the date range as a subcube in the FROM statement. If the [PrevPeriod] is outside of the subcube that is still OK, as long as the time dimension is included in an Axis on the final select statement, so at least I know I am not suffering from the members inside the subcube. I've also found in SSMS that it makes no difference if I make the query a subcube, or put the date range in a where clause instead; I still get NULL for [PrevPeriod] without the dates.
I can't imagine that this is an unusual situation, so I hope I've explained it adequately! What is the recommended technique for summarizing a Parallelperiod by dimensions without displaying the time/dates ?
I have developed a cube in my work place for analyzing current year sales with previous year sales in Time Hierarchy (Year- Quarter- Month) using Parallel period. If we want to see data for particular Quarters i.e. Q1 and Q2 then total at the year level should also get change. Currently if we only choose 2 quarters in the filter then current year data gets change, however data using parallel period is not getting change accordingly and its shows Total of full year.
I have a monthly time period dimension representing average number of students for each month. At the yearly aggregate level I don't want it to sum up the avg number of students from every month because that number is incorrect. I would like it to use the number of students from the most recent month as a roll up. Is that possible to configure in SSAS?
At the following MDX code , I want to get the aggregate of measure only for members that are also in the specified last time (like in examp 01/06/2015) . I tried existing and exists, but without any lack.
WITH MEMBER A AS (b)+(C) MEMBER [Measures].[Aggregate] AS Aggregate(DAYTIME].[Month].&[2013-01-01T00:00:00]:[DAYTIME].[Month].&[2015-06-01T00:00:00], ([Measures].[D])
I'm trying to find if any part of a date range in my table between orig_start_rent and stop_rent falls within a period I specify in two variables: startPeriod and endPeriod
For example if I specify 2013-11-01 as startPeriod and 2013-11-30 as endPeriod, then if any part of the date range between orig_start_rent and stop_rent(stop-rent can be null if hire is open) falls within that period, I want that to be picked up and assigned the value of 1 in my case statement for OnHire. My code is not picking everything up however - using the example above, a record with orig_start_rent of 2013-05-23 and stop_rent of 2013-11-18 is being assigned 0 when it should be 1. My code here:
declare @startPeriod as smalldatetime declare @endPeriod as smalldatetime set @startPeriod = '2013-11-01' set @endPeriod = '2013-11-30'; select dticket, orig_start_rent, stop_rent, case when orig_start_rent >= @startPeriod and orig_start_rent <= @endPeriod then 1 when orig_start_rent < @endPeriod and stop_rent is null then 1 else 0 end [OnHire] from deltickitem
I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!
I am creating an SSIS Script Task that will be used to process SSAS dimensions and partitions and ideally log the details of each in a table. Any info on the benefits or drawbacks of using the built-in SSAS parallel processing as opposed to doing it manually in a multi-threaded "Parallel.Foreach" loop using the .NET AMO library.
In my testing, when I use a Parallel.foreach loop, I am able to obtain and log information about the object such as end time and time to process immediately after each object is processed. This allows me to keep a history of processing time for each object:
public void processDimensions(Server Server, Database Database, ProcessType processType) { Parallel.ForEach(Database.Dimensions.OfType<Microsoft.AnalysisServices.Dimension>(), d => { DateTime beginTime = DateTime.Now; try { d.Process(processType);
[code]....
If circumventing the built-in SSAS parallel processing is not best practice I'd like to know in advance before I go too far down that path.
I would like to create calculated measure which should give me closing holdings at all levels(week ,Month,Quarter and year levels.) whichever i pull in the browse pane.
Below calculated measure would show the holdings for whatever the level you have specified.
([Measures].[Holdings], ClosingPeriod( [DIM BI DATE].[Calendar].[WEEK], [DIM BI DATE].[Calendar].CurrentMember ))
But this measure shows the value for only week attribute.
for month,([Measures].[Holdings],ClosingPeriod( [DIM BI DATE].[Calendar].[Month], [DIM BI DATE].[Calendar].CurrentMember ))
But my aim is to create a calculated measure to give closing value for any level.
What this does is, it calculates the median of the medians over the period of 3 years. But, what I'm looking for is the overall median of the underlying measure over a period of 3 years.
Which works fine for all my other calculations except this, where I need a distinct count. CNT is a calculated measure. The browser would look like this:
Category Year1 Year2 ..... MDX what I have now MDX what I need
How do you build a query to calculate the time of employement or any space of time for each person in a table? I'm thinking I would want to use a "Current Date" and "Select Datediff" calculations of some sort and reference the hiredate against the current date but I have been unable to find much on this type of query.. Please help..
HiI am hoping someone might be able to help me out with this.I am writing a helpdesk system which records agents logging in and outof the system.I need to write a stored procedure which will show the peak number ofagents logged in concurrently during a specified time period. Withinthe time period, the person viewing the report should be able tospecify intervals at which to see the data.Eg. There is already a table in the system which holds loggedin/logged out data like22/11/2004 14:02 - 22/11/2004 17:3022/11/2004 09:00 - 22/11/2004 17:1222/11/2004 10:25 - 22/11/2004 16:3022/11/2004 11:02 - 22/11/2004 12:3022/11/2004 16:00 - 22/11/2004 17:30The report user can then say for example they want to view databetween 10th November and 12th November broken down into 15 minutesintervals which would produce a table like this:10/11/2004 00:00 - 10/11/2004 00:1510/11/2004 00:15 - 10/11/2004 00:3010/11/2004 00:30 - 10/11/2004 00:4510/11/2004 00:45 - 10/11/2004 01:00 etc etcAgainst each of these time slots, I need to work out the peak numberof concurrent agents logged in using the first table.Can anyone make any suggestions? The time period the report user canchoose are either 15 mins, 30 mins, 45 mins, 1 hour and 1 day.Thanks in advance
There are a lot of questions posted on SQLTEAM asking how to find the beginning of various time periods. The script will create and demo 14 functions that return a datetime for the beginning of a time period relative to the datetime value passed in parameter @DAY.
I put together this script to create these functions for several reasons: 1. To allow people to find them on their own without having to post a question. 2. To allow posted questions to be answered with a reference to this script. 3. To document algorithms that work for the widest possible range of datetime values. All except for the Century and Decade functions work for any datetime value from 1753/01/01 00:00:00.000 through 9999/12/31 23:59:59.997. The Century is limited to datetimes from 1800/01/01 forward, because 1700/01/01 is not valid in SQL Server. The Decade function is limited to datetimes from 1760/01/01 forward, because 1750/01/01 is not valid in SQL Server. 4. And last, you can actually use them on your application.
There is a separate post for function dbo.F_START_OF_WEEK to find the first day of the week at this link: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307
This script was tested with SQL Server 2000 only.
I posted a script for End Date of Time Period Functions here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64759
Other Date/Time Info and Script Links: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762
Edit 2006-11-01: Added dbo.F_START_OF_X_MIN( @DAY ) at the suggestion of Peter.
Edit 2007-02-24: Modified the following functions to use a simpler algorithm, that is shorter, runs faster, and is more suited for use with in-line code: dbo.F_START_OF_30_MIN( @DAY ) dbo.F_START_OF_20_MIN( @DAY ) dbo.F_START_OF_15_MIN( @DAY ) dbo.F_START_OF_10_MIN( @DAY ) dbo.F_START_OF_05_MIN( @DAY )
*/ go if objectproperty(object_id('dbo.F_START_OF_CENTURY'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_CENTURY end go create function dbo.F_START_OF_CENTURY ( @DAY datetime ) returns datetime as /* Function: F_START_OF_CENTURY Finds start of first day of century at 00:00:00.000 for input datetime, @DAY. Valid for all SQL Server datetimes >= 1800-01-01 00:00:00.000 Returns null if @DAY < 1800-01-01 00:00:00.000 */ begin
end go if objectproperty(object_id('dbo.F_START_OF_DECADE'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_DECADE end go create function dbo.F_START_OF_DECADE ( @DAY datetime ) returns datetime as /* Function: F_START_OF_DECADE Finds start of first day of decade at 00:00:00.000 for input datetime, @DAY. Valid for all SQL Server datetimes >= 1760-01-01 00:00:00.000 Returns null if @DAY < 1760-01-01 00:00:00.000 */ begin
end go if objectproperty(object_id('dbo.F_START_OF_YEAR'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_YEAR end go create function dbo.F_START_OF_YEAR ( @DAY datetime ) returns datetime as /* Function: F_START_OF_YEAR Finds start of first day of year at 00:00:00.000 for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
return dateadd(yy,datediff(yy,0,@DAY),0)
end go if objectproperty(object_id('dbo.F_START_OF_QUARTER'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_QUARTER end go create function dbo.F_START_OF_QUARTER ( @DAY datetime ) returns datetime as /* Function: F_START_OF_QUARTER Finds start of first day of quarter at 00:00:00.000 for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
return dateadd(qq,datediff(qq,0,@DAY),0)
end go if objectproperty(object_id('dbo.F_START_OF_MONTH'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_MONTH end go create function dbo.F_START_OF_MONTH ( @DAY datetime ) returns datetime as /* Function: F_START_OF_MONTH Finds start of first day of month at 00:00:00.000 for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
return dateadd(mm,datediff(mm,0,@DAY),0)
end go if objectproperty(object_id('dbo.F_START_OF_DAY'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_DAY end go create function dbo.F_START_OF_DAY ( @DAY datetime ) returns datetime as /* Function: F_START_OF_DAY Finds start of day at 00:00:00.000 for input datetime, @DAY. Valid for all SQL Server datetimes */ begin
return dateadd(dd,datediff(dd,0,@DAY),0)
end go if objectproperty(object_id('dbo.F_START_OF_HOUR'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_HOUR end go create function dbo.F_START_OF_HOUR ( @DAY datetime ) returns datetime as /* Function: F_START_OF_HOUR Finds beginning of hour for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
return dateadd(hh,datediff(hh,0,@DAY),0)
end go if objectproperty(object_id('dbo.F_START_OF_30_MIN'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_30_MIN end go create function dbo.F_START_OF_30_MIN ( @DAY datetime ) returns datetime as /* Function: F_START_OF_30_MIN Finds beginning of 30 minute period for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
end go if objectproperty(object_id('dbo.F_START_OF_20_MIN'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_20_MIN end go create function dbo.F_START_OF_20_MIN ( @DAY datetime ) returns datetime as /* Function: F_START_OF_20_MIN Finds beginning of 20 minute period for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
end go if objectproperty(object_id('dbo.F_START_OF_15_MIN'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_15_MIN end go create function dbo.F_START_OF_15_MIN ( @DAY datetime ) returns datetime as /* Function: F_START_OF_15_MIN Finds beginning of 15 minute period for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
end go if objectproperty(object_id('dbo.F_START_OF_10_MIN'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_10_MIN end go create function dbo.F_START_OF_10_MIN ( @DAY datetime ) returns datetime as /* Function: F_START_OF_10_MIN Finds beginning of 10 minute period for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
end go if objectproperty(object_id('dbo.F_START_OF_05_MIN'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_05_MIN end go create function dbo.F_START_OF_05_MIN ( @DAY datetime ) returns datetime as /* Function: F_START_OF_05_MIN Finds beginning of 5 minute period for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
end go if objectproperty(object_id('dbo.F_START_OF_X_MIN'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_X_MIN end go create function dbo.F_START_OF_X_MIN ( @DAY datetime, @INTERVAL int ) returns datetime as /* Function: F_START_OF_X_MIN Finds beginning of @INTERVAL minute period for input datetime, @DAY. If @INTERVAL = zero, returns @DAY. Valid for all SQL Server datetimes. */ begin
-- Prevent divide by zero error if @INTERVAL = 0 return @DAY
declare @BASE_DAY datetime set @BASE_DAY = dateadd(dd,datediff(dd,0,@Day),0)
end go if objectproperty(object_id('dbo.F_START_OF_MINUTE'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_MINUTE end go create function dbo.F_START_OF_MINUTE ( @DAY datetime ) returns datetime as /* Function: F_START_OF_MINUTE Finds beginning of minute for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
end go if objectproperty(object_id('dbo.F_START_OF_SECOND'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_SECOND end go create function dbo.F_START_OF_SECOND ( @DAY datetime ) returns datetime as /* Function: F_START_OF_SECOND Finds beginning of second for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
insert into @test_dates (DT) select DT = getdate()union all select '17530101 00:00:00.000'union all -- Test start of Decade cutoff select '17591231 23:59:59.997'union all select '17600101 23:04:59.997'union all -- Test start of Century cutoff select '17991231 23:59:59.997'union all select '18000101 00:00:00.000'union all -- Test start of Decade and Century select '19000101 00:00:00.000'union all select '19001231 23:59:59.997'union all select '19400101 00:00:00.000'union all select '19491231 23:59:59.997'union all select '19900101 00:00:00.000'union all select '19991231 23:59:59.997'union all -- For start of Hour testing select @lyear+' 00:00:00.000'union all select @lyear+' 00:59:59.997'union all select @lyear+' 01:00:00.000'union all select @lyear+' 01:59:59.997'union all select @lyear+' 12:00:00.000'union all select @lyear+' 12:59:59.997'union all select @lyear+' 17:00:00.000'union all select @lyear+' 17:59:59.997'union all select @lyear+' 23:00:00.000'union all select @lyear+' 23:59:59.997'union all -- For start of Month, Quarter, and Year testing select @year+'0101 00:00:00.000'union all select @year+'0131 23:59:59.997'union all select @year+'0201 00:00:00.000'union all select @year+'0228 23:59:59.997'union all select @year+'0301 00:00:00.000'union all select @year+'0331 23:59:59.997'union all select @year+'0401 00:00:00.000'union all select @year+'0430 23:59:59.997'union all select @year+'0501 00:00:00.000'union all select @year+'0531 23:59:59.997'union all select @year+'0601 00:00:00.000'union all select @year+'0630 23:59:59.997'union all select @year+'0701 00:00:00.000'union all select @year+'0731 23:59:59.997'union all select @year+'0801 00:00:00.000'union all select @year+'0831 23:59:59.997'union all select @year+'0901 00:00:00.000'union all select @year+'0930 23:59:59.997'union all select @year+'1001 00:00:00.000'union all select @year+'1031 23:59:59.997'union all select @year+'1101 00:00:00.000'union all select @year+'1130 23:59:59.997'union all select @year+'1201 00:00:00.000'union all select @year+'1231 23:59:59.997'union all -- Test start of 5, 10, 15, 20, and 30 min testing select @today+' 23:04:59.997'union all select @today+' 23:09:59.997'union all select @today+' 23:14:59.997'union all select @today+' 23:19:59.997'union all select @today+' 23:24:59.997'union all select @today+' 23:29:59.997'union all select @today+' 23:34:59.997'union all select @today+' 23:39:59.997'union all select @today+' 23:44:59.997'union all select @today+' 23:49:59.997'union all select @today+' 23:54:59.997'union all select @today+' 23:59:59.997'union all select '99991231 23:59:59.997' order by 1
-- Convert dates in @test_dates table to test F_START_OF functions
select TYPE = 'CENTURY' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_CENTURY( DT ),121) from @test_dates order by DT
select TYPE = 'DECADE' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_DECADE( DT ),121) from @test_dates order by DT
select TYPE = 'YEAR' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_YEAR( DT ),121) from @test_dates order by DT
select TYPE = 'QUARTER' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_QUARTER( DT ),121) from @test_dates order by DT
select TYPE = 'MONTH' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_MONTH( DT ),121) from @test_dates order by DT
select TYPE = 'DAY' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_DAY( DT ),121) from @test_dates order by DT
select TYPE = 'HOUR' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_HOUR( DT ),121) from @test_dates order by DT
select TYPE = '30_MIN' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_30_MIN( DT ),121) from @test_dates order by DT
select TYPE = '20_MIN' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_20_MIN( DT ),121) from @test_dates order by DT
select TYPE = '15_MIN' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_15_MIN( DT ),121) from @test_dates order by DT
select TYPE = '10_MIN' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_10_MIN( DT ),121) from @test_dates order by DT
select TYPE = '05_MIN' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_05_MIN( DT ),121) from @test_dates order by DT
select TYPE = 'MINUTE' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_MINUTE( DT ),121) from @test_dates order by DT
select TYPE = 'SECOND' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_SECOND( DT ),121) from @test_dates order by DT /* End of test script */
Hello, I would like to exclude the time period after 5.30pm and before 8.30am in my results. The time is in a 13 digit timestamp format which is the same as a standard unix timestamp with 3 digits which are microseconds.
I used:
dataadd(ss, TTIME/1000, '1970-01-01')AS time
to create a column with a readable time in it.
Here is a screenshot: http://www.abtecnet.com/timescreenshot.jpg
Can anyone help me with this. Thanks very much. Andrew
Can anyone offer any advice. I was wondering whether MS SQL server offeredany facilities to trigger an event after a period of time had lapsed? e.g.once a week check data and if it is out of date email an related address.many thanks
I am using SQL Server 2000. I need to query my database for all thecontracts that came in during a certain time frame (user is promptedfor reportingperiodid).Table - PeriodsFields - Reporting Period id intReporting Period desc varchar(30)Reporting Period Begin Date datetimeReporting Period End Date datetimeIf the user selects a 3 then the begin date is Jan. 1, 2004 and theend date is June 30, 2004.Now I need to calculate did any money come in for each week in thattime frame. I need to create a weekly list of all the weeks in thattime frame. Each time frame begins on a Monday. So my list wouldlook like1/5/20041/12/20041/19/20041/26/2004All the way to the end of that time period.How do I create this weekly list from a given time period using T-SQL?I would appreciate any and all help on this.Thanks,Tony
We are having 8 databases mirrored with witness server, by default automatic failover occure in 10 secs if witness does not get a ping from a principal server. If there any way to increase the failover time perioed.
I would like to create a simple regression equation to predict player win on their next trip. I have tried to create the model using a linear regression tree based on two players (as a test). The result gives me a single node (expected) with only a coefficient instead of a regression equation. I can do this math by hand to get a regression equation and predicted value for the next trip for each player.
The dataset I used for a simple test is.....
Trip # Player Win
1 1001 1,250
1 1002 50
2 1001 1,450
2 1002 75
3 1001 1,600
3 1002 100
4 1001 2,000
4 1002 175
I also tried to predict next trip worth using a forecasting model. I was able to process the model but I was not able to browse the model content in the viewer.
Ultimately, I want to predict next trip worth for individual players off of a cube. The cube has about 1.5- 6M records (multiple records per player) depending on the datasource.
FYI - I have created a working linear regression and a forecasting model off of a cube --- I think I am setting it up correctly.
Hello: Very soon my company will be moving to a 4-4-5 reporting schedule. Basically, what this means is that the first month of the quarter will have 4 weeks, the second will have 4 weeks, and the third will have 5 weeks. Therefore, for the 2007 the dates for Jan, Feb and Mar will be as follows: Jan - 1 - 27 Feb - 28 - 24 Mar - 25 - 31
Currently, I have an SSIS package creating a record for each day in the Time Dimension.
Is there any T-SQL script out there that will help me build a Fiscal calendar such as the one described above?
Hello! Given three dimensions in my cube (Product, Calendar, & Sales). I'm trying to create a calculated measure that will sum 8 weeks of sales starting from the third week in which sales started. For instance, weeks 1-4 had zero sales. Week 5 is the first appearance of any sales. Therefore, the measure would sum the sales of weeks 7-14 (8 weeks starting with the 3rd week after sales begin).
Any ideas?? Keep in mind that every product is going to be different (sales start in different weeks).
while my db is executing a store procedure i try to view the current activity in the managent but it returns to me 'Lock request time out period exceded'. It happens until the store procedure is finished. After that everything is ok. However i can see the activity executing sp_who and the db seems to work ok, maybe a little slow.
I would like to run queries with data that sometimes span two days. The queries require start and end dates as well as start and end times. The following code works fine if the start time is less than the end time:
select * from tst01 where convert(varchar, [DateTime],126) between '2005-09-15' and
'2006-01-27' and convert(varchar, [DateTime],114) between '09:00:00' and
'17:00:00' order by [DateTime]
However, if I try to run a query where the start time is greater than the end time (e.g., start time 5:00pm on one day until 9:00am the next day), the query returns an empty table.
select * from tst01 where convert(varchar, [DateTime],126) between '2005-09-15' and
'2006-01-27' and convert(varchar, [DateTime],114) between '17:00:00' and
'09:00:00' order by [DateTime]
I need a way to indicate that the start and end times span two days. Can anybody help with this?
Hi , i am getting error Lock request time-out period exceeded frequently Could any one explain how can i track the Locking transactions SP_who,SP_lock helps me partially but i need clear solution
I am using SQL Server in a project where I want to fetch the records that were inserted after a time specified in my query.
Suppose 10 records were inserted at 10:00 AM(morning) and 5 were inserted at 10:15 AM( 15 minutes later). I am running a query at 10:20 AM( 5 minutes after the second transaction). I need this query to be such that it selects the records inserted 10 minutes before. So it will show only the records inserted at and after 10:10 AM and willl not show those inserted at 10:00 AM.
Please help me in making such a query.
I am trying and I think that some Date & Time functions will help but still not able to achieve it.
Store Start End Mins of Type 2 only XXXX 02-03-2015 10:04:00 02-03-2015 10:12:00 00:01:30 YYYY 02-03-2015 20:04:00 02-03-2015 20:27:00 00:05:00
So for each Store (Store is unique in the table), I am rolling up the intervals with overlaps to create a single interval.
Now, for each store, I want to find the time period for purely type 2. So if there is an overlap, type 1 has the dominance. And I want the sum of time period of whatever is left for type 2.
I have written this code but not able to address the overlap issue:
alter table [DATA] add Outage float; update [DATA] set Outage = DATEDIFF(SECOND,[Start],[END]) alter table [RESULT] add [Outage_Type1 (%)] float,[Outage_Type2 (%)] float;
I am trying to write a stored procedure that will select information from a SQL table based on a specific time. For example I have a name field and a time field, I want to return just the names that were created between a specific time frame. ex between 3pm and 4pm. Any thoughts?
We're running a SQL-Server 2012 and for a while now my accessing records from bigger tables became tricky.There is a Tomcat-8 running which sometimes can't access these tables at all or only after a long delay. As this happened first I went to the Server-Room and opened the Database with the Management Studio to see if there were any issues. open the Database but expanding the directories for "Tables" or "Views" failed after 10 Seconds with the Error 1222.
I turned the Tomcat-8 off to find out whether some unclosed connections are open. Same result, no changes even after one hour.Another 3rd-Party program which we are using seems to connect via other mechanisms to the SQL-Server (Is there a way to list current connections and their types in the Management-Studio, I'm under the impression this program does a lot of caching, it's much faster than the Management-Studio itself.The question is now how can I find out why these time-outs happen? I'm not an expert in SQL-Servers so.
To give you some context we have a new amendments application (nothing fancy, excel based with SQL Server back end) that allows users to submit amendments to product data (Product Info, PO Prices, Dates etc.). There is also an admin tool that a team uses to action these amendments in the various systems.
The old version of this tool, users submitted amendments by style and could if need be submit multiple amendments against one product at the same time. The new tool, I believe for audit reasons, users submit by amendment type, so for example I would submit a cost price change for a given style.
The issue now is that on the occasions where a user has multiple amendments, they now come through separately. So cost price would be Amendment 1 and a date change would be amendment 2 even though they could be the same product. This could potentially mean that the admin team would be duplicating work if the paperwork is updated and sent after each amendment, whereas before they would make both changes and only send the paperwork once.
Having not built either of these tools, I've been tasked with trying to fix this, my two thoughts being either to amend the user form to somehow capture/ allow users to submit amendments together or try to use the existing data and doing the grouping dynamically in the back end. Use that lag to look at grouping any submitted amendments that occur within 30mins of the first occurrence of that style
This grouping would then be given a joint time so when the 'time lag' period passes the amendments will be visible together.I've tried a few things and a few head on desk moments trying to get a set based approach but haven't been able to get where i want, its either an issue where amendments span an hour, such as 9:59 and then 10:03 or grouping together amendments that happen after the 30mins of the first one.
Here is some sample data
USE FF_Winning_Together; IF OBJECT_ID(N'tempdb..#AmendTest',N'U') IS NOT NULL DROP TABLE #AmendTest; CREATE TABLE #AmendTest ( AmendmentIDINT IDENTITY(1,1)NOT NULL, StyleCHAR(1)NOT NULL, AmendmentStatusVARCHAR(10)NOT NULL, DTDATETIMENOT NULL
