LastPeriods Function

Does the LastPeriods function have to be used with the Aggregate function when creating a KPI expression?

Aggregate({[Incident].[Incident].&[1]} *

{LastPeriods(4, [Date].[Year - Quarter].[Quarter].&[2006-01-01T00:00:00])},

[Measures].[TOTAL])

If so, is it better to just do:

Aggregate({[Incident].[Incident].&[1]} *

{[Date].[Year - Quarter].[Quarter].&[2005-01-01T00:00:00] : [Date].[Year - Quarter].[Quarter].&[2006-01-01T00:00:00]},

[Measures].[TOTAL])

Whether Aggregate() should be used depends on the definition of the KPI - what are the business rules for it?

Also, {[Date].[Year - Quarter].[Quarter].&[2005-01-01T00:00:00] : [Date].[Year - Quarter].[Quarter].&[2006-01-01T00:00:00]} is probably equivalent to LastPeriods(5, [Date].[Year - Quarter].[Quarter].&[2006-01-01T00:00:00]) - so should 4 or 5 quarters be included?

Last Modified Field

I'm creating a last modified field that I'll be putting into several of my tables in my current database and in many more to come so I want to get the most optimal way of doing this established so I don't end up propogating a problem. The field will be updated with the current time and date each time the row is modified. I created a trigger to do this, but I don't know if this is the best way. I'm not real experienced with MS SQL so I'm certainly open to suggestions. Here's the trigger code I have now.

UPDATE s
SET LastModified = getdate()
FROM secUser s
join inserted i on s.UID = i.UID

Any suggestions would be appreciated.

Thanks,
Nate BaxleyIn this case, the best choice is instead of update trigger.

Good luck!

Large Sample Database required for testing

We are creating a search engine in MS SQL using FullText etc.

we need to run some tests against a database that is near to realistic data. We have been able to generate a database of thousands of usernames, first names etc. but we can't figure out where to obtain a database full of data that contains product descriptions, product titles etc.

Does anyone have any idea where we could obtain (or even buy if need be) a large database (over 10,000 records) of sample data that we could run test queries against, so that we can optimise the queries, provide execution time estimates etc.

We have downloaded the adventureworks sample database, but there are not nearly enough records in it.

Any help would be much appreciated.

Create one on your won, there are many data generation tools outside there generating either random data or data based on your own data while obfuscating it. The MS product for this is Visual Studio for Database professionals which has a great data generator.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de