LastProcessed property on AMO processable objects

Processable objects in AMO (dimensions and partitions in this case) have a LastProcessed property. When does that property get updated? Is it when the processing starts, or when it ends?

The LastProcessed property is updated at the end of a successful process.

During the processing the LastProcessed date will reflect the end of the previous processing (as will the data). When the processing completes successfully the transaction is committed, the new data is made available for querying and the LastProcessed property is updated.

|||Thanks. That's what I figured, I just wanted to make sure.sql

lastnonempty & role playing date dimension

Hello,

I'm just curious if in case of a role-playing date dimention it's possible to somehow tell SSAS to use only one role for LastNonEmpty aggregate?

Like we have a fact table with a few date related members - such as TransactionDate, DateOpened, DateClosed etc.

All measures in this fact table are set to aggregate as LastNonEmpty & everything works just fine as long as only TransactionDate is linked to dimDate.

If any other dates are linked then LastNonEmpty doesn't work properly anymore & we get unpredictable results.

So if it possible to set ONLY TransactionDate to be used as LastNonEmpty & for all other dates just aggregate as sum?

Thanks!

It is always only ONE role-playing time dimension. The problem is, you cannot control which one it is. Sorry, but there is no way to tell directly to SSAS which one. You can keep reordering the cube dimensions in AMO until SSAS picks the right one. After that, if the order doesn't change - it will always use the same one (it is stable algorithm w.r.t. order of dimensions).|||

Cool - thanks for the answer - at least there's a way!

Now - AMO stands for Analysis Management Objects so how do I reorder cube dimensions in AMO?

Is the order in AMO the same as the order in which dimensions are displayed in the dimension usage section of VS?

I'm kind of confused how to move them?

|||If you look at the XML behind Create Cube DDL (or Alter Cube DDL) - the order of cube dimensions there is the one I am talking about.|||

I see what you mean...

so by "reordering the cube dimensions in AMO until SSAS picks the right one" - do you mean reordering just role playing date dimensions or ALL dimensions? I have 5 date related and a LOT more other dimensions so without some further guidance it's not clear to me how to handle all the possible combinations?

BTW is it a coincidence or a rule that the order in XML is the same as the order in which dimensions are displayed in 'dimension usage' pane of VS? Appears to be that way...

|||

> do you mean reordering just role playing date dimensions or ALL dimensions?

Reordering just the cube dimensions which are marked as Time within measure group. So it your case - it would be reordering 5 dimensions. There are really only 5 combinations here - just pick the dimension you want to do LastNonEmpty by and move it to the first, second, etc places. The rest doesn't matter.

|||

Could you please be a bit more specific as the XML file is rather large (20+K lines) & there's way to many places where dimensions are mentioned - one where they're defined, one where the relationships between measures & dimensions are defined - I moved things around there with no effect, third place is where aggregations are defined like:

<Aggregation>

<ID>Aggregation 5</ID>

....

<Dimension>

<CubeDimensionID>Maturity Date</CubeDimensionID>

</Dimension>

but there's so many of these so where exactly do I change the definition for the settings to apply?

Thanks!

|||Look for <MeasureGroups> tag. Then locate <MeasureGroup> tag which mathes your measure group. Then skip over <Measures> collection and go to <Dimensions> collection. This is where you should do reordering.|||

cool - thanks - I got it working!

Last Entry in a Log

(I don't post here often, so in case I'm violating long-standing taboos
of this newsgroup, I apologize in advance for calling a relation a
table, using nulls, and other ignorant, destructive, and comtemptible
terminology.)

I have a table that's keeping a sort of running log of different types
of changes to pieces of data. The table has a foreign key of the data
being changed, the foreign key for the type of change occuring, some
information about the change in a couple more columns, and a timestamp
for each entry. So it's:

dataID
eventID
eventInfo
timestamp

What I'd like to do, if at all possible, is a single SQL query that,
given a dataID, returns the most recent eventInfo and timestamp for
each eventID. Is this possible?

Many thanks.
-Eric(eric404@.gmail.com) writes:
> I have a table that's keeping a sort of running log of different types
> of changes to pieces of data. The table has a foreign key of the data
> being changed, the foreign key for the type of change occuring, some
> information about the change in a couple more columns, and a timestamp
> for each entry. So it's:
> dataID
> eventID
> eventInfo
> timestamp
> What I'd like to do, if at all possible, is a single SQL query that,
> given a dataID, returns the most recent eventInfo and timestamp for
> each eventID. Is this possible?

SELECT a.eventID, a.eventInfo, a.timestamp
FROM tbl a
JOIN (SELECT eventID, timestamp = MAX(timestamp)
FROM tbl b
WHERE dataID = @.dataid)
GRUOP BY eventID) AS b ON a.eventID = b.eventID
AND a.timestamp = b.timestamp
WHERE a.dataID = @.dataid

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Is your "timestamp" column actually a DATETIME or SMALLDATETIME column? If
so, try the following query. Don't use the name "timestamp", which refers to
a different datatype in SQL Server. The TIMESTAMP datatype has nothing to do
with date and time so if your column is in fact a TIMESTAMP then you ought
to add a DATETIME column instead.

SELECT eventid, eventinfo, timestamp
FROM YourTable AS T
WHERE timestamp =
(SELECT MAX(timestamp)
FROM YourTable
WHERE eventid = T. eventid
AND dataid = @.dataid)
AND dataid = @.dataid ;

--
David Portas
SQL Server MVP
--|||Thanks for the help, it works great.

Large view problem

I have a considerably large view that is pulling data from tables and other view, using user defined functions, and using case statements. This view is taking a lot of time to load.

I was under the impression that SQL server keeps the views uptodate, so selecting data from them is as fast as selecting from a table. It now seems like SQL server rebuilds the view evertime something accesses it.

Can someone please tell me more on this. I am now having to re-write everything :-(

Thanks Jag

Shameless bump|||Do you have indexes on the columns being referenced in the view? Are you filtering the view with a WHERE clause and do they also have indexes and how up to date are they?|||

I have never looked into indexes, so I am presuming that the columns do not have any indexes.

The view is being filtered by a where clause. However, the problem is actually building the view. When I use the query analyzer to select all the rows, it still takes 30-40 seconds to return just 252 rows !!!!

What was happening was that I was using a number of view to pull out the last data being entered into different areas. These view where then combined into this large view. I also used user defined functions to pull out other data.

I have had to change this to use triggers and store the data elsewhere. This has cut the time down considerable, but I an still wondering why the problem existed in the first place.

|||

jagdipa:

I have never looked into indexes, so I am presuming that the columns do not have any indexes.

Perhaps its time to look into it now..There are some very good articles @.http://www.sql-server-performance.com/articles.asp

Large Tables, Inserts, and selecting data.

Hello,
I'd like to point out that, obviously, I'm not a Database Admin - but I have
read a fair amount on the topic and examined several case studies similiar t
o
this issue.
Is there a recommended size limit for the size of a table in SQL Server
2000? We have a system that is gathering test data in near real time that is
then inserted into SQL and our admin indicated that after 'a while' inserts
were slowing down as the table got bigger. If the table were properly
normalized and indexed, would the size of the table even matter?
The solution currently implemented is that a new table is created every
hour. Each of these tables contain the same schema, but represent data from
a
specific hour of the day. In other words, you end up with 24 tables per day.
Obviously there are other tables now involved that have to track all of this
and the SQL necessary to select the data appears needlessly complex.
So my question, (along with the one above) is quite simple. Is this a common
practice? Are there addtional case studies, web sites, or whatnot that I can
review before participating in the next design meeting?
Thanks for reading!
Chris>> Is there a recommended size limit for the size of a table in SQL Server
No. The physical resources at your disposal determine the table size
In general, no.
That sounds like a poor approach. Other than increasing your maintenance
overhead, prone-ness to error and overall complexity, it seems to offer
nothing beneficial.
Some folks do it, assuming it is somehow simpler to use several smaller
tables instead of a single one. Such systems are often poorly designed
without any consideration to good logical principles and often fall apart
due to increased complexity and lack of scalability options.
The general recommended practice is to use a single table with an attribute
to distingush the individual sets of test data gathered over each hour,
perhaps using a temporal datatype.
Anith|||From a client (consuming) standpoint, your comments make a lot of sense to m
e
as I feel the same, however my lack of qualifications vs the db admin is
proving difficult.
Very good suggestion attributing the data - I'll be sure to bring this up at
our next meeting.
Are there any other suggestions out there? I just came from another meeting
with the DB folks and they are adamant that when the table becomes 'big'
inserts slow down... that does not make sense to me. The topic was then abou
t
indexing and about how indexing was going to kill the insert process as well
.
I think I'm missing something... thank you for the comments, I'll be looking
forward to more.
Chris
"Anith Sen" wrote:

> No. The physical resources at your disposal determine the table size
>
> In general, no.
>
> That sounds like a poor approach. Other than increasing your maintenance
> overhead, prone-ness to error and overall complexity, it seems to offer
> nothing beneficial.
>
> Some folks do it, assuming it is somehow simpler to use several smaller
> tables instead of a single one. Such systems are often poorly designed
> without any consideration to good logical principles and often fall apart
> due to increased complexity and lack of scalability options.
> The general recommended practice is to use a single table with an attribut
e
> to distingush the individual sets of test data gathered over each hour,
> perhaps using a temporal datatype.
> --
> Anith
>
>|||I'd had thought this discussion would have generated more interest.
Is there a group better suited to this question?
Thanks,
Chris
"Chris Keller" wrote:
> From a client (consuming) standpoint, your comments make a lot of sense to
me
> as I feel the same, however my lack of qualifications vs the db admin is
> proving difficult.
> Very good suggestion attributing the data - I'll be sure to bring this up
at
> our next meeting.
> Are there any other suggestions out there? I just came from another meetin
g
> with the DB folks and they are adamant that when the table becomes 'big'
> inserts slow down... that does not make sense to me. The topic was then ab
out
> indexing and about how indexing was going to kill the insert process as we
ll.
> I think I'm missing something... thank you for the comments, I'll be looki
ng
> forward to more.
> Chris
>
> "Anith Sen" wrote:
>|||> >> The solution currently implemented is that a new table is created every
> That sounds like a poor approach. Other than increasing your maintenance
> overhead, prone-ness to error and overall complexity, it seems to offer
> nothing beneficial.
>
> Some folks do it, assuming it is somehow simpler to use several smaller
> tables instead of a single one. Such systems are often poorly designed
> without any consideration to good logical principles and often fall apart
> due to increased complexity and lack of scalability options.
There are, of course, exceptions to this; you can get a performasnce
boost from using a properly-indexed partiioned design in an OLAP
environment if your data influx is very large. We have a single
partioned view containing 90 days worth of data (split into daily
tables), containing nearly 60 million rows of data. By using a
partioned view, index ss are very effecient.
This approach is very effecient on smaller servers; if I had quad
processors and high-speed drives and lots of RAM, I probably could have
gotten away with a single table.
I would NOT recommend this approach unless you have lots of data to
deal with, but I don't think that it's necessarily a bad design as
suggested in the previous quote.
Stu|||>> I would NOT recommend this approach unless you have lots of data to deal
Regarding exceptions, I agree. Logical design is generally done with data
integrity as the primary goal. As you said, exceptions do exist and are
mostly done for specific purposes on a case by case basis.
For instance, partitioned tables/views are often implemented for performance
reasons; constraints must be put in place for preventing data overlaps;
while multiple views are maintained by the system they logically appears as
a single table to the external user.
Anith|||You mention that indexed search is very fast for your solution - can you
define fast?
What about insert performance on indexed tables? Do you see an improvement
by having your data spread across multiple tables? That is a big question
that I have, specifically the argument is that with a large indexed table
that inserts get slower in relation to the size. I don't see why that would
be the case - but perhaps I'm misisng something.
Chris
"Stu" wrote:

> There are, of course, exceptions to this; you can get a performasnce
> boost from using a properly-indexed partiioned design in an OLAP
> environment if your data influx is very large. We have a single
> partioned view containing 90 days worth of data (split into daily
> tables), containing nearly 60 million rows of data. By using a
> partioned view, index ss are very effecient.
> This approach is very effecient on smaller servers; if I had quad
> processors and high-speed drives and lots of RAM, I probably could have
> gotten away with a single table.
> I would NOT recommend this approach unless you have lots of data to
> deal with, but I don't think that it's necessarily a bad design as
> suggested in the previous quote.
> Stu
>|||Our end users analyze firewall events, which can range from 1 event per
incident to hundreds of thousands of events per incident. On average,
they're pulling back 5000 records in milliseconds; note that's 5000
records out of 60,000,000.
INSERTs will slow down in relation to size if your database is having
to grow to accomodate them, especially if your indexes are physically
located on the same drive as your data. However, partitioning your
data is typically more useful during data SELECTion rather than
INSERTing, because you're providing the optimizer hints by which to
narrow down the s.
We also use the partitioning to archive data; on a daily basis, I spin
off the data older than 90 days to an archive db (which is partitioned
on a monthly basis). When a month of data is "closed", we back that
data up to a portable hard drive, which then goes to storage. That
way, we can retrieve data if we need it, but it's not sucking up disk
space on my servers.
Good references on partitioned views:
http://www.sqlteam.com/Item.ASP?ItemID=684
http://www.windowsitpro.com/SQLServ.../8234/8234.html
Stu|||Thank you for the information Stu - I appreciate it.
I should point out that the solution (as currently implemented) is not
partitioned at all. Tables are created in the same database on the same
server - hourly. And I will say that we are not seeing anything simliar to
the perf you are realizing as it can take up to a minute to retrieve 10k
rows...
Looking at the SQL, it appears that most of the work is being done simply
trying to figure out what tables are involved - it is crazy and convuluted
and therefore I'm looking at what other folks have done to see if I can't
persuade folks to change.
Chris
"Stu" wrote:

> Our end users analyze firewall events, which can range from 1 event per
> incident to hundreds of thousands of events per incident. On average,
> they're pulling back 5000 records in milliseconds; note that's 5000
> records out of 60,000,000.
> INSERTs will slow down in relation to size if your database is having
> to grow to accomodate them, especially if your indexes are physically
> located on the same drive as your data. However, partitioning your
> data is typically more useful during data SELECTion rather than
> INSERTing, because you're providing the optimizer hints by which to
> narrow down the s.
> We also use the partitioning to archive data; on a daily basis, I spin
> off the data older than 90 days to an archive db (which is partitioned
> on a monthly basis). When a month of data is "closed", we back that
> data up to a portable hard drive, which then goes to storage. That
> way, we can retrieve data if we need it, but it's not sucking up disk
> space on my servers.
> Good references on partitioned views:
> http://www.sqlteam.com/Item.ASP?ItemID=684
> http://www.windowsitpro.com/SQLServ.../8234/8234.html
> Stu
>|||The nice thing about partitioned views is that the optimizer knows
where the data comes from, and where it belongs, since it uses mutually
exclusive check constraints to place the data. All of our SQL
statements are run against the view; in other words, we'll issue a
command like:
SELECT Columns
FROM View
WHERE CheckColumn = 'some value'
INSERTS and DELETES and UPDATES are also performed on the view itself,
eliminating the need to figure out where the source table is located.
We create tables daily all on the same server; hourly seems a bit much
unless you're dealing with a heck of a lot of data.

Large SB messages cannot be dispatched ?

Hi All\Remus

I have a very weird SB problem in production, we currently have a microsoft case open but are not getting anywhere with it at the moment.

Perhaps someone out there or Remus can shed some light.

Let me give you some background, we have a service borker application using permenant dialogs between remote store Sql Server Express databases (initiator), that send messages on the same dialog to a central database (target).Over a VERY slow network.

Let me reiterate the network is very slow and experiences frequent timeouts, this is the nature of the network and a reason why went with service broker.

Last week we released a new version of the smart client app that uses SB messaging, the only change to SB was the size of the messages, they went from 20KB to over 200KB. NOTHING else changed, existing dialogs, routes, messages etc are still being used.

As soon as the first new large message was sent to central it could not get there, i pick up the following error in Profiler:

This message was dropped because it could not be dispatched on time. State: 2 1 - Sequenced Message.

I had since run many tests, i have skipped that message and let the normal process run , message are successfully delivered until it comes to a large message again then everything stops and the same errors occurs again.

I can send messages up to 30KB as soon it it is over 50KB they never get to the target and all i see is this error in profiler.

Now i need someone to explain to me what happens in the background, i know SB will break big messages up into smaller pieces, i do not know at what size this happens or how SB keeps track of the progress of a big message.

From my testing and results I am ussuming that the first piece of the message is received and SB keeps checking if packets are being received if SB it not receiving any packets anymore(network timeout) or there is a message to live timeout that expires before the complete message is received. This error occurs and it drops the message and tries again.

This gets me into an inifinite loop due to the slow network and message sizes we never actually get a message delivered, all i get is the error message constantly.

Is there some sort of timeout happening if the whole message connot be received in x seconds ? Is it due to SB checking the status of the message but getting timeouts ?

What i have done as a work around is no longer putting large binary data into the messages, therefore they are back to the original size and everyting works, smaller bianry data messages go through so it has nothing to do with the message data. As soon as a message exceeds +-40KB they cannot be delivered and i get the error above.

P.S i tried sending the same binary data from target to initiator as a test, the message never got there either but the error was different i got the following error:

This forwarded message was dropped because the message could not be delivered within the message time to live.

I cannot find anything in BOL or the net about changing or configuring a message time to live.However i do think this is perhaps the issue.

Thern this was followed by alot of Broker Forwarded Message Sent events from the test service but the message never got there and i never saw the first error again, so i am still confused abuot that too ?

Can anyone please help here ... Thanx

Send me the case number (you still have my mail, don't you?)|||

Hi Remus

the case number is SRQ070831600601.

Nope sorry i do not have your email anymore, lost a laptop in the interim, however all my contact dedtails are on the case details, i have sent though profiler traces, log files etc on the case. Please contact me or let me know if there is anything else i can do to assist.

This case has been standing still since friday, the say they cannot find a Service Broker expert Engineer currently, i really appreciate the help.

Thank You

|||This has been resolved with a Microsoft case, requires a hotfix, to be released in October.

Large SB messages cannot be dispatched ?

Hi All\Remus

I have a very weird SB problem in production, we currently have a microsoft case open but are not getting anywhere with it at the moment.

Perhaps someone out there or Remus can shed some light.

Let me give you some background, we have a service borker application using permenant dialogs between remote store Sql Server Express databases (initiator), that send messages on the same dialog to a central database (target).Over a VERY slow network.

Let me reiterate the network is very slow and experiences frequent timeouts, this is the nature of the network and a reason why went with service broker.

Last week we released a new version of the smart client app that uses SB messaging, the only change to SB was the size of the messages, they went from 20KB to over 200KB. NOTHING else changed, existing dialogs, routes, messages etc are still being used.

As soon as the first new large message was sent to central it could not get there, i pick up the following error in Profiler:

This message was dropped because it could not be dispatched on time. State: 2 1 - Sequenced Message.

I had since run many tests, i have skipped that message and let the normal process run , message are successfully delivered until it comes to a large message again then everything stops and the same errors occurs again.

I can send messages up to 30KB as soon it it is over 50KB they never get to the target and all i see is this error in profiler.

Now i need someone to explain to me what happens in the background, i know SB will break big messages up into smaller pieces, i do not know at what size this happens or how SB keeps track of the progress of a big message.

From my testing and results I am ussuming that the first piece of the message is received and SB keeps checking if packets are being received if SB it not receiving any packets anymore(network timeout) or there is a message to live timeout that expires before the complete message is received. This error occurs and it drops the message and tries again.

This gets me into an inifinite loop due to the slow network and message sizes we never actually get a message delivered, all i get is the error message constantly.

Is there some sort of timeout happening if the whole message connot be received in x seconds ? Is it due to SB checking the status of the message but getting timeouts ?

What i have done as a work around is no longer putting large binary data into the messages, therefore they are back to the original size and everyting works, smaller bianry data messages go through so it has nothing to do with the message data. As soon as a message exceeds +-40KB they cannot be delivered and i get the error above.

P.S i tried sending the same binary data from target to initiator as a test, the message never got there either but the error was different i got the following error:

This forwarded message was dropped because the message could not be delivered within the message time to live.

I cannot find anything in BOL or the net about changing or configuring a message time to live.However i do think this is perhaps the issue.

Thern this was followed by alot of Broker Forwarded Message Sent events from the test service but the message never got there and i never saw the first error again, so i am still confused abuot that too ?

Can anyone please help here ... Thanx

Send me the case number (you still have my mail, don't you?)|||

Hi Remus

the case number is SRQ070831600601.

Nope sorry i do not have your email anymore, lost a laptop in the interim, however all my contact dedtails are on the case details, i have sent though profiler traces, log files etc on the case. Please contact me or let me know if there is anything else i can do to assist.

This case has been standing still since friday, the say they cannot find a Service Broker expert Engineer currently, i really appreciate the help.

Thank You

|||This has been resolved with a Microsoft case, requires a hotfix, to be released in October.

Large objects and the transaction log

Hi there!

I'm a little bit in doubt because of the behavior of the transaction log in the case i have large transactions inserting and deleting a lot of (i.e. 100) large objects (data type image) with a size (each) about 20MB. I expected that the transaction log should grow with 2GB each of this large transactions. But it does'nt.

Are these large transactions not logged - so that in case of disaster all lobs since last diff- or full-backup are lost?

Some experience with that?

Greetings, Torsten

First off, I assume that your database in not in Simple recovery mode, right? If it is, then the transaction log does not accumulate data from transactions that are completed.

Can you describe the transactions a bit more? When you say that LOBs are inserted and deleted in the large transaction, are the same LOBs created and deleted within one transaction, or are you inserting some and deleting others?

Remember also that unless you explicitly shrink the log, it will reach a steady state where it has the capacity used by all transactions between log backups. It should not just keep growing.

What is the size of your log, and how many of these transactions occur between backups?

|||The problem is no problem. I've not recognized that you have to set an explicit file space for lob columns. That has been the default filespace (Primary), so there is no surprise that only the primary filegroup growth...

Large objects and the transaction log

Hi there!

I'm a little bit in doubt because of the behavior of the transaction log in the case i have large transactions inserting and deleting a lot of (i.e. 100) large objects (data type image) with a size (each) about 20MB. I expected that the transaction log should grow with 2GB each of this large transactions. But it does'nt.

Are these large transactions not logged - so that in case of disaster all lobs since last diff- or full-backup are lost?

Some experience with that?

Greetings, Torsten

First off, I assume that your database in not in Simple recovery mode, right? If it is, then the transaction log does not accumulate data from transactions that are completed.

Can you describe the transactions a bit more? When you say that LOBs are inserted and deleted in the large transaction, are the same LOBs created and deleted within one transaction, or are you inserting some and deleting others?

Remember also that unless you explicitly shrink the log, it will reach a steady state where it has the capacity used by all transactions between log backups. It should not just keep growing.

What is the size of your log, and how many of these transactions occur between backups?

|||The problem is no problem. I've not recognized that you have to set an explicit file space for lob columns. That has been the default filespace (Primary), so there is no surprise that only the primary filegroup growth...