.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

SSAS2005 Cube - Watch Customer on two points in Time

Posted By:      Posted Date: September 29, 2010    Points: 0   Category :Sql Server


I need some help on the following problem:

I have got a customer status, let's say N for New, A for Active, I for Inaktive.

When a customer buys the first time his status will be N.
When he buys a second time his status changes to A.
After some time without an order his status is set to I.

This status changes are recorderd in a history table.

The question:

How many customers with status "N" at date(t1) and how many of those customers have status "N", "A" and "I" at date(t2).

e.g. on 01. Jan. 2010 i have 500 customers with status "N".
On 01. Jul. 2010 i want to know how many of those 500 customers from the 01. Jan. have status "A".
Let' say 200 have status A.

Output should be t1_U = 500 and t2_A = 200.

Would it be posible to do answer this question in a cube?
I have tried out some things but didn't get an plausible result.
With 2 messures (one for point1 in time, a second for point2 in time) it will not give me 200 in the second messure, but 220. I think a there is missing a relationship between the two messures on customer base.
So at point 2 there are 20 more customers with status "A" on point2 in time thats right, but they didn't exist on poi

View Complete Post

More Related Resource Links

Returning Customer Count of all categories in each time period



Lets say I have the following Table:

CustomerNumber | Date | MovieCategory
1 |2010-01-01|A
1 |2010-02-01|A
1 |2010-03-01|A
1 |2010-04-01|A
1 |2010-05-01|A
1 |2010-06-01|D
1 |2010-07-01|A
2 |2010-01-01|A
2 |2010-02-01|A
2 |2010-03-01|D
2 |2010-04-01|D
2 |2010-05-01|D
2 |2010-06-01|C
2 |2010-07-01|C
3 |2010-01-01|B
3 |2010-02-01|B
3 |2010-03-01|B
3 |2010-04-01|C
3 |2010-05-01|C

What I want to do is count the number of customers for each movie type in each date. Now I started with the following simple query

Select Date, MovieCategory, Count(CustomerNumber)
From AboveTable
Group By Date, MovieCategory
Order by Date, MovieCategory

which would be okay, but if no customer has a MovieCategory of say 'B' on a particular date then the results of the above query won't return a row that has

Date| B| 0     (date here could be any date).


which is really what I need. Is there a good way to

Customer Timer Job runs at different time


Hi All,

I have a custom timer job that I set to run at 8AM every day.(code below)

It does not run at 8AM instead it runs randomly during the day(but only once though)

How do I make sure that it runs at 8AM every day. Anything wrong in my code:

Thanks in advance.



override void FeatureActivated(SPFeatureReceiverProperties properties)



// register the the current web


SSAS Cube to give an error when cube not available during refresh time



I am currently using the below query to know my last cube refresh time,

My cube refesh time is not fixed , I am trying to create a report which shows a status of cube availability i.e status should show red when cube is not available and when cube returns some value then it's green . How do I do that.

SELECT Cube_Name, Last_Data_Update


How to query SSAS cube based on a time span?



sorry, I'm new to SSAS. something i couldn't figure out, plase help me.

I have a fact table: Log   and a dimision table: Time

in the Time table, all the record are in date format detailed to hour. such as 2010-06-01 23:00:00

and in the log table, each log record has a time column.

Now I want to query based on two time parameters other program passed me.

e.g. , I want to know how many log records between 2010-01-01 02:00:00 and 2010-10-27 12:00:00.

but when i do the query, the problem is on the time spot '2010-01-01 02:00:00', maybe no log record avaliable.

so the query return nothing! The following is my query.

select NON EMPTY{[Log].[Subject].children} on 1,
NON EMPTY{[Measures].[Quantity]} on 0
 ( SELECT ( STRTOMEMBER('[Time].[Date].&[2010-05-01T23:00:00]') : STRTOMEMBER('[Time].[Date].&[2010-05-24T23:00:00]') ) on 0
   from [Db TMIC])

if Time.Date has no member of 2010-05-05 23:00:00, I got nothing!


I also tried use filter to get the nearest value, but still don't work.

select {[Measures].[Quantity]} on 0,

Filter([Time].[Date], [Time].[Date].CurrentMember < '2010-05-05T23:00:00') on 1
from [Db TMIC]


Can any one help how to solve the problem? To query based on two time values.

Question on Creating Aggregations based on Time data in a Cube



I have a fact table that lists projects and the site visit dates that related to those projects.   For each site visit date there are a number of associated KPIs.

However to make life a little bit more complicated these KPIs are prorated by year so for each site visit there may be several rows in the fact table that relate to each year ie 2005,  2006 etc...

The issue I am having is that I have been given a requirement to view each KPI at the site visit level.   My first idea was to create an aggregate table for each site visit with totals for each year ie  2005 sales total,  2006 sales total etc...    The issue is that totals can be for any year between 2005 and 2014 and clearly creating all these hard coded totals means there will be a huge number of KPIs required with in-built time logic at the site visit level ie for each KPI I would need a separate measure for each possible year.

Any other alternatives?   The underlying fact table will of course show the end user a list of site visits and the KPIs relating to each year,  but the end user wants to be able to view the data in a repivoted format so that site visit returns 1 row with the prorated date columns appearing vertically across.   For the time being I cannot see that this is possible without having a huge agg

Reduce Load Time of Cube Data using SSIS


Hi, I have an issue where I am trying to reduce the time it takes to load SSAS cube data to SQL table using SSIS.

Within a Data flow task I have an OEDB source where in I select "command from variable" where the variable holds the dynamic MDX. I then have a data conv, lookup, derived transform and OLEDB destination respectively.

However the load takes some 2.5 hours for inserting 1 million rows. Any pointers as to how I may reduce it?

I have around 110 columns returned from the source MDX and it has 1 million rows as I said.

the lookup is to get a key not present in cube. since the lookup table is huge, i am using partial cache and the derived column transform just adds a string (hard coded) value of "ETL" and a time using getdate(). Data conversion is for the obvious conv for ntext data (returned from MDX to corresponding datatypes in SQL table...I have the fast load option in the OLEDB destination.

I can't use openquery though. Let me know what may I do to reduce the load time.

Round off time to the nearest minute

How would you round this up to the nearest minute? There isn't a built in function to do this so you have to use a little bit of maths to get there. There are 60 seconds in a minute. We already have 38 seconds on the clock. So we need to add on 60 - 38 = 22 more seconds.

Performance Tests: Precise Run Time Measurements with System.Diagnostics.Stopwatch

Everybody who does performance optimization stumbles sooner or later over the Stopwatch class in the System.Diagnostics namespace. And everybody has noticed that the measurements of the same function on the same computer can differ 25% -30% in run time. This article shows how single threaded test programs must be designed to get an accuracy of 0.1% - 0.2% out of the Stopwatch class. With this accuracy, algorithms can be tested and compared.

Date and Time Functions in SQLSERVER

Date and time functions allow you to manipulate columns and variables with DATETIME and SMALLDATETIME data types.

1 DATEPART Function
2 DATENAME Function
3 DAY, MONTH, and YEAR Functions
5 DATEADD Functions
6 DATEDIFF Function
7 More SQL Server Functions

Data Types - Date and Time in SqlServer

Date and time values can be stored with either the DATETIME or SMALLDATETIME data type. The difference between the two is that SMALLDATETIME supports a smaller range of dates and does not give the same level of precision when accounting for time. The DATETIME data type can hold values from January 1st of 1753 to December 31st of 9999. The time is stored to the 1 three hundredths of a second and each value takes up 8 bytes of storage. The SMALLDATETIME data type can hold values between January 1st 1900 and June 6th of 2079. The time is tracked to the minute and each value takes up 4 bytes of storage. The majority of business applications can live happily with SMALLDATETIME, however, if you are in an environment where each second matters or you need to make estimates to the distant future (or past) then you have to resort to DATETIME. If you fail to specify the time when inserting a value into a DATETIME or SMALLDATETIME column, a default of midnight is used. If you fail to specify the date portion the default of January 1, 1900 is used.

How to programmatically add controls to Windows forms at run time by using Visual C#

Create a Windows Forms Application
Start Visual Studio .NET or Visual Studio 2005 or a later version, and create a new Visual C# Windows Application project named WinControls. Form1 is added to the project by default.
Double-click Form1 to create and view the Form1_Load event procedure.
Add private instance variables to the Form1 class to work with common Windows controls. The Form1 class starts as follows:

.NET 4 Web Application Startup Time

I was chatting with Jonathan Hawkins and some of the folks on the ASP.NET team about performance and Jonathan mentioned the startup time for large ASP.NET applications is improved on .NET 4. There are some improvements in the CLR and in ASP.NET itself that helped. If you have a giant app, you should do some tests.

Built-in Functions - Date and Time Functions

Date and time functions allow you to manipulate columns and variables with DATETIME and SMALLDATETIME data types.

Essential SQL Server Date, Time and DateTime Functions

The essential date and time functions that every SQL Server database should have to ensure that you can easily manipulate dates and times without the need for any formatting considerations at all.

Date/Time Conversions Using SQL Server

There are many instances when dates and times don't show up at your doorstep in the format you'd like it to be, nor does the output of a query fit the needs of the people viewing it. One option is to format the data in the application itself. Another option is to use the built-in functions SQL Server provides to format the date string for you.

Date and Time Data Types and Functions

The following sections in this topic provide an overview of all Transact-SQL date and time data types and functions. For information and examples that are common to date and time data types and functions

Calculate distance, bearing and more between Latitude/Longitude points

This page presents a variety of calculations for latitude/longitude points, with the formulæ and code fragments for implementing them.

All these formulæ are for calculations on the basis of a spherical earth (ignoring ellipsoidal effects) - which is accurate enough* for most purposes. [In fact, the earth is very slightly ellipsoidal; using a spherical model gives errors typically up to 0.3% - see notes for further details].
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend