Profiler ....duration

In SQL Server 2000, "Duration" in profiler (which is in milliseconds) taken by the event; does it include time for the network transfer of the results to the client as well ; or its just the duration taken by SQL Server to complete the SQL Statement.All the measurements are serverside only, so no network time. If you want to see the network time then run the query in query analyser but before you start right click and select client side statistics.|||

thanks for the info.

I have a 3rd party application & the queries are generated from the application.

So I wanted to see the total duration. Queries are very dynamic, so running from Query Analyzer may not yield same result w.r.t. time/ network load when the app runs/ etc etc ... So i am trapping it in profiler.

|||Then there is no easy way to get this with the built in tools.

Production experience to share

I wanted to post this in case anyone else runs into it - save you loads of
time.
Yesterday we deployed our latest release of reports - someone forgot to move
the pin on the report in source control and yet the updated stored proc for
the report went out. Net result was the updated stored proc was out there
but the report wasn't. The new stored proc was requesting a new parameter
the report couldn't supply. Normally on our dev box we get the error message
back from reporting services rather quickly (~3 seconds). On our test box it
was taking 3 minutes and production was just hanging.
We are using C# to proxy all report requests (so nobody can hit the box
directly) and the error codes being returned to C# were basically saying that
reporting services was abruptly cutting the connection. We received Denial
of Service Attach messages, Tls errors, SSL/Tls trust errors and timeout
errors. Nothing returned about missing parameters - the exception object
doesn't receive detailed info as to why the connection was killed - only that
it was killed. In order to get the details we will have to modify the code
to go into the HTML returned by the WebRequest object.
I don't know why the report was hanging for as long as it was- I went into
the IIS logs to see what was being requested (fortunately we have to use URL
requests in our case) and when picking through the requests I noticed that it
wasn't passing in a required parameter. (The report didn't request it, so it
didn't pass it). When I recreated the request through report manager I
noticed that it wasn't using all of the parameters expected.
In our attempts we upped the MaxReqPerUser property in RSReportServer.config
file from default (20) to 140 as a guess - it helped for about 5 minutes,
then everybody crashed again.
Microsoft - hopefully the new RS release will interrogate the stored proc at
runtime and return results quicker? Hopefully error integration will be
better with C# next year? I doubt it b/c there may not be a good way to pass
errors to the exception object.
I am sharing this b/c I was on the phone with MS premier support ($$)
yesterday for 7 hours and they offered no creative ideas or really anything
of signifigance. Please add this to your knowledge base so the next guy
might avoid 12 hours of headache.
Any comments are welcomed!
John Hennesey
JohnHennesey82:hotmail.comThanks for sharing...I'll have to remember this one...
"JohnHennesey" <JohnHennesey@.discussions.microsoft.com> wrote in message
news:C285281B-4230-4114-8B3A-E303CB2FADD9@.microsoft.com...
>I wanted to post this in case anyone else runs into it - save you loads of
> time.
> Yesterday we deployed our latest release of reports - someone forgot to
> move
> the pin on the report in source control and yet the updated stored proc
> for
> the report went out. Net result was the updated stored proc was out there
> but the report wasn't. The new stored proc was requesting a new
> parameter
> the report couldn't supply. Normally on our dev box we get the error
> message
> back from reporting services rather quickly (~3 seconds). On our test box
> it
> was taking 3 minutes and production was just hanging.
> We are using C# to proxy all report requests (so nobody can hit the box
> directly) and the error codes being returned to C# were basically saying
> that
> reporting services was abruptly cutting the connection. We received
> Denial
> of Service Attach messages, Tls errors, SSL/Tls trust errors and timeout
> errors. Nothing returned about missing parameters - the exception object
> doesn't receive detailed info as to why the connection was killed - only
> that
> it was killed. In order to get the details we will have to modify the
> code
> to go into the HTML returned by the WebRequest object.
> I don't know why the report was hanging for as long as it was- I went into
> the IIS logs to see what was being requested (fortunately we have to use
> URL
> requests in our case) and when picking through the requests I noticed that
> it
> wasn't passing in a required parameter. (The report didn't request it, so
> it
> didn't pass it). When I recreated the request through report manager I
> noticed that it wasn't using all of the parameters expected.
> In our attempts we upped the MaxReqPerUser property in
> RSReportServer.config
> file from default (20) to 140 as a guess - it helped for about 5 minutes,
> then everybody crashed again.
> Microsoft - hopefully the new RS release will interrogate the stored proc
> at
> runtime and return results quicker? Hopefully error integration will be
> better with C# next year? I doubt it b/c there may not be a good way to
> pass
> errors to the exception object.
> I am sharing this b/c I was on the phone with MS premier support ($$)
> yesterday for 7 hours and they offered no creative ideas or really
> anything
> of signifigance. Please add this to your knowledge base so the next guy
> might avoid 12 hours of headache.
> Any comments are welcomed!
> John Hennesey
> JohnHennesey82:hotmail.com

Product Sales Forecasting.

Hi,

We are trying to forcast product sales for next three months based on their sales for previous 12 months. In this case, Microsoft Time Series algorithm requires the sales data to be present for each product for past 12 months (?). However, our products have typical life span of 6 months and obviously the new products will not have sales before they were added. Any help will be very much appreciated.

Thanks

Riju

Riju -

A few pointers that have been very successful for us in forecasting sales and inventory:

1. Can you build store clustering models to increase the data set size?
2. For new products you can also try correlating sales from similar existing products. We have utilized attributes from existing products and mapped their sales as a predictor for new product sales.

Here is a whitepaper that provides a detailed inventory forecasting example from Project REAL http://apollodatatech.com/company/shell.html?projreal

Hope this helps,

Jeff

Please visit Apollo Data Technologies http://apollodatatech.com/|||

Hi Jeff,

Thanks for the response.

It seems that the Out-of-stock predictive model in REAL project is based in the Decision Tree algorithm which does not require the data to be present for each period. I am having problem to use Time Series algorithm becuase my products typically have sales for about six month and i am trying to use the last 12 months data to predict the sales of next 3 months.

Thanks

Riju

|||

This must be a common problem in forcasting. Isn't there any way to handle this?

Thanks

Riju

|||You should read the paper referenced by apollo. In these circumstances you likely need to build a tree model to predict based on similar products, not use time series as there's not enough historical data.|||

Hi Jamie,

Thanks for the response.

I read the paper referenced by Apollow which gave the detail information on how to predict the product sales using the Decision Tree algorithm. However, i was trying to get some help on how to use the Time Series algorithm in my situation. Thanks to your response which stated that the Time Series algorithm is not applicable in my situation.

Riju

producing a date time report in SQL/DTS

I have need to produce a report (excel sheet actually) from SQL that
would run each Tuesday and each Friday of every w.
What needs to be on the Tuesday report is everything that came in from
the Friday midnight time, until the Monday midnight time. The friday
report(sheet) would have everything that came in from Midnight Monday
evening, thru midnight Thursday. The next Tuesday report would have
everything from Midnight Thursday thru midnight Monday, and so on.
I know I can schedule the jobs to run on that interval, but how do I
selectively pick the records I want? There is a datetime field on the
table, "submit date" and what I am basically doing is a select * from
tbl_literature_orders where date > x.
Any ideas?
Thanks,
BC"Blasting Cap" schrieb:
> I have need to produce a report (excel sheet actually) from SQL that
> would run each Tuesday and each Friday of every w.
> What needs to be on the Tuesday report is everything that came in from
> the Friday midnight time, until the Monday midnight time. The friday
> report(sheet) would have everything that came in from Midnight Monday
> evening, thru midnight Thursday. The next Tuesday report would have
> everything from Midnight Thursday thru midnight Monday, and so on.
> I know I can schedule the jobs to run on that interval, but how do I
> selectively pick the records I want? There is a datetime field on the
> table, "submit date" and what I am basically doing is a select * from
> tbl_literature_orders where date > x.
> Any ideas?
> Thanks,
> BC
Try it with two jobs, one for Tuesday, one for Friday, and set the execution
time of the job appropriately. Search for your data by difference:
select * from MyTable where datefield > dateadd(d, -3, GetDate()) -- Friday
and
select * from MyTable where datefield > dateadd(d, -4, GetDate()) -- Tuesday|||Just use the DATEPART() or DATENAME() functions to determine which day it
is. Then use DATEADD() with the appropriate days to get the from and to
that you need for your WHERE clause.
Andrew J. Kelly SQL MVP
"Blasting Cap" <goober@.christian.net> wrote in message
news:eiUP1w9CGHA.4080@.TK2MSFTNGP09.phx.gbl...
>I have need to produce a report (excel sheet actually) from SQL that would
>run each Tuesday and each Friday of every w.
> What needs to be on the Tuesday report is everything that came in from the
> Friday midnight time, until the Monday midnight time. The friday
> report(sheet) would have everything that came in from Midnight Monday
> evening, thru midnight Thursday. The next Tuesday report would have
> everything from Midnight Thursday thru midnight Monday, and so on.
> I know I can schedule the jobs to run on that interval, but how do I
> selectively pick the records I want? There is a datetime field on the
> table, "submit date" and what I am basically doing is a select * from
> tbl_literature_orders where date > x.
> Any ideas?
> Thanks,
> BCsql

Proclarity Dashboard Performance issues

Hi,

I had a problem with the performance. It generally takes time for all the application to populate the content in the window when it was called first time and for sucessive calls it will populate the page quickly. but in case of my proclarity dashboard it takes more time when it was called as many times. can anyone help me to resolve this problem.

Thanks,

madhan

Are you saying that the second time you displayed a page from RS, it took as long as the first time? If so check the log file and see if the appdomain was recycled. There is "shutdownReason" in the log file that may be of interest. Make sure you are not touching any file under the RS directory each time you load your dashboard.

Processor activity is high.

I used %Processor Time counter from System Monitor to know whether the
processor activity is peak or not, and it showed continually above 80%.
Should I upgrade server by adding additional processor?
Is there any other alternatives?
Thanks
Robert Lie
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:uJlS%23vbWFHA.3044@.TK2MSFTNGP10.phx.gbl...
>I used %Processor Time counter from System Monitor to know whether the
>processor activity is peak or not, and it showed continually above 80%.
> Should I upgrade server by adding additional processor?
> Is there any other alternatives?
Determine what is using all the CPU cycles and optimize it.
David
|||Hi,
Execute a profiler (With Duration and CPU) when the usage is high. Save the
Profiler output to a table and query the stored procedures / TSQL which uses
more CPU. This will help you to identify the code which eats your CPU. Use
the Execution plans and Index tuning wizard to tune the procedure.
Mostly you will be able to solve the CPU bottleneck, if you still have
issues then probably you need to add one more Processor.
Thanks
Hari
SQL Server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:uJlS%23vbWFHA.3044@.TK2MSFTNGP10.phx.gbl...
>I used %Processor Time counter from System Monitor to know whether the
>processor activity is peak or not, and it showed continually above 80%.
> Should I upgrade server by adding additional processor?
> Is there any other alternatives?
> Thanks
> Robert Lie

Processor activity is high.

I used %Processor Time counter from System Monitor to know whether the
processor activity is peak or not, and it showed continually above 80%.
Should I upgrade server by adding additional processor?
Is there any other alternatives?
Thanks
Robert Lie"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:uJlS%23vbWFHA.3044@.TK2MSFTNGP10.phx.gbl...
>I used %Processor Time counter from System Monitor to know whether the
>processor activity is peak or not, and it showed continually above 80%.
> Should I upgrade server by adding additional processor?
> Is there any other alternatives?
Determine what is using all the CPU cycles and optimize it.
David|||Hi,
Execute a profiler (With Duration and CPU) when the usage is high. Save the
Profiler output to a table and query the stored procedures / TSQL which uses
more CPU. This will help you to identify the code which eats your CPU. Use
the Execution plans and Index tuning wizard to tune the procedure.
Mostly you will be able to solve the CPU bottleneck, if you still have
issues then probably you need to add one more Processor.
Thanks
Hari
SQL Server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:uJlS%23vbWFHA.3044@.TK2MSFTNGP10.phx.gbl...
>I used %Processor Time counter from System Monitor to know whether the
>processor activity is peak or not, and it showed continually above 80%.
> Should I upgrade server by adding additional processor?
> Is there any other alternatives?
> Thanks
> Robert Lie

Processor activity is high.

I used %Processor Time counter from System Monitor to know whether the
processor activity is peak or not, and it showed continually above 80%.
Should I upgrade server by adding additional processor?
Is there any other alternatives?
Thanks
Robert Lie"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:uJlS%23vbWFHA.3044@.TK2MSFTNGP10.phx.gbl...
>I used %Processor Time counter from System Monitor to know whether the
>processor activity is peak or not, and it showed continually above 80%.
> Should I upgrade server by adding additional processor?
> Is there any other alternatives?
Determine what is using all the CPU cycles and optimize it.
David|||Hi,
Execute a profiler (With Duration and CPU) when the usage is high. Save the
Profiler output to a table and query the stored procedures / TSQL which uses
more CPU. This will help you to identify the code which eats your CPU. Use
the Execution plans and Index tuning wizard to tune the procedure.
Mostly you will be able to solve the CPU bottleneck, if you still have
issues then probably you need to add one more Processor.
Thanks
Hari
SQL Server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:uJlS%23vbWFHA.3044@.TK2MSFTNGP10.phx.gbl...
>I used %Processor Time counter from System Monitor to know whether the
>processor activity is peak or not, and it showed continually above 80%.
> Should I upgrade server by adding additional processor?
> Is there any other alternatives?
> Thanks
> Robert Lie

Processing Time Perfomance

First, a general question. What is a "reasonable" time to expect the
ProcessingTime to take. Now I know that is pretty vague and largely depends
on the grouping/filtering defined in the RDL.
Our problem. We have a several main reports that each embed 7-10 subreports
from a pool of 15 or so defined subreports. This was primarily designed this
way for the flexibility and ease of maintenance of selecting/moving around
the subreports in each main report.
I understand that a main report executing subreports cannot take advantage
of multi-threading the queries and instead run each sequentially, correct?
Even so, we have tuned the database very well and the data retrieval (from
execution log) is taking about 2 seconds for about 15 queries in tables with
millions of rows. Incidentally we are using a custom DPE.
Highlevel details of each subreport. I can provide the RDLs, if necessary:
-Each subreport is one page (8.5x11) long and contains a pie chart and bar
chart data region and a table data region.
-The dataset for the table, pie and bar are the same, but the table and pie
do an aditional grouping on a string value on an average of 10-100 rows.
-Two other data regions of about 10 rows exist, but do not do any grouping.
-One of the subreports does do filtering on a range of 10 to potentially
~5000 rows, but is slow in processing even with 10 rows.
-Last, in the table dataregion (which is never bigger than 15 rows after
grouping), an external assembly is called to do some minor formatting for
each row (font, navigation, etc.)
The problem is that the processing time is taking near a minute -- which is
unacceptable given our application is online and the business expectation is
to take about 5 seconds, no more than 10. Cached reports are super-fast, but
the reports are such that it is unlikely that a cache would be used.
So, what can we do to improve perfomance? It does not appear that moving
the subreports into one report will gain us much, even with mult-threaded
queries, as our database is tuned to the hilt already.
-We tried disabling the caching (thinking perhaps the time to write the
cache files was expensive), but no. By the way, does the time to write the
cache fall into "processing time"?
-We could potentially break the data in two (one for the pie and table and
one for the bar) and omit the grouping.
-We could also return two datasets for the subreport that is doing
filtering.
-Our ReportServer and SQLReportCatalog are on the same server (SQL Standard)
and the server is pretty beefy (2 proc 2GHz). I assume nothing in the
processing part is multi-threaded, so one one proc gets used, right? Are
there any config settings to improve performance?
I want to avoid changing around the RDL to remove all filtering and grouping
if it will only shave a few seconds of the minute anyhow.
Thoughts, suggestions requested. Thanks, DavidMy suggestion is to run each subreport individually and see what time you
get for them. See if a particular subreport is giving you the problem. Since
your data is not large for each subreport (ignoring the one that could have
5,000 since you said having smaller data is still a problem) it seems to me
that you should be doing substantially better here than you are.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"David Swanson" <DavidSwansonSDG@.newsgroup.nospam> wrote in message
news:4511C346-8482-42D1-9192-1BE85B9089B5@.microsoft.com...
> First, a general question. What is a "reasonable" time to expect the
> ProcessingTime to take. Now I know that is pretty vague and largely
depends
> on the grouping/filtering defined in the RDL.
> Our problem. We have a several main reports that each embed 7-10
subreports
> from a pool of 15 or so defined subreports. This was primarily designed
this
> way for the flexibility and ease of maintenance of selecting/moving around
> the subreports in each main report.
> I understand that a main report executing subreports cannot take advantage
> of multi-threading the queries and instead run each sequentially, correct?
> Even so, we have tuned the database very well and the data retrieval (from
> execution log) is taking about 2 seconds for about 15 queries in tables
with
> millions of rows. Incidentally we are using a custom DPE.
> Highlevel details of each subreport. I can provide the RDLs, if
necessary:
> -Each subreport is one page (8.5x11) long and contains a pie chart and bar
> chart data region and a table data region.
> -The dataset for the table, pie and bar are the same, but the table and
pie
> do an aditional grouping on a string value on an average of 10-100 rows.
> -Two other data regions of about 10 rows exist, but do not do any
grouping.
> -One of the subreports does do filtering on a range of 10 to potentially
> ~5000 rows, but is slow in processing even with 10 rows.
> -Last, in the table dataregion (which is never bigger than 15 rows after
> grouping), an external assembly is called to do some minor formatting for
> each row (font, navigation, etc.)
> The problem is that the processing time is taking near a minute -- which
is
> unacceptable given our application is online and the business expectation
is
> to take about 5 seconds, no more than 10. Cached reports are super-fast,
but
> the reports are such that it is unlikely that a cache would be used.
> So, what can we do to improve perfomance? It does not appear that moving
> the subreports into one report will gain us much, even with mult-threaded
> queries, as our database is tuned to the hilt already.
> -We tried disabling the caching (thinking perhaps the time to write the
> cache files was expensive), but no. By the way, does the time to write
the
> cache fall into "processing time"?
> -We could potentially break the data in two (one for the pie and table and
> one for the bar) and omit the grouping.
> -We could also return two datasets for the subreport that is doing
> filtering.
> -Our ReportServer and SQLReportCatalog are on the same server (SQL
Standard)
> and the server is pretty beefy (2 proc 2GHz). I assume nothing in the
> processing part is multi-threaded, so one one proc gets used, right? Are
> there any config settings to improve performance?
> I want to avoid changing around the RDL to remove all filtering and
grouping
> if it will only shave a few seconds of the minute anyhow.
> Thoughts, suggestions requested. Thanks, David

processing time issue

Hi all,

I have a quick question regarding the processing time.

I created view in db for the count purposes with huge fact table. ( I'm using dimension table which

joins the 1 fact and 1 dimension to count the key for the fast data retrieval)

If I write the query for the count in management studio and it gives a results within 5 sec.

But, if I add this view in DSV and process the cubes than it takes around 50 minutes.

This causes due to the Fact table? or what else?

I don't know what to check and please give me some comments.

Thanks in advance.

Have you run a profiler trace against the server. You could probably trace either SQL or SSAS to capture the exact SQL statement that is being executed. If this view is being used as a fact table SSAS will do a full scan of it and do a look up for each row to each of the associated dimensions. Doing a trace against SSAS might give you some more hints on what is going on. There are also a number of SSAS specific counters in Performance Monitor that might give some insight into what is going on.|||

Hi Darren,

Thanks for your good tips.

As you mentioned above, the view looks up for each row of associated dimension (fact table). In the Fact, total number of rows is around 153,000,000 and it makes cube processing very slow now.

So to improve better performance and faster processing what would you recommend in this situation? I only need the count of store number not that huge fact table.

Please give me some comments.

Thanks.

|||

There would be a couple of possible approaches to improving the processing performance.

One would be to partition the fact table so that you only process recent records and not the full 153 million.

Another might be to look at doing incremental processing. If you can keep track of which records are new since you last processed, you can just load those.

Finally you could possibly do a "group by" in your view to reduce the granularity, but often reducing the granularity reduces the flexibilty of your design and would generally be a last resort.

Processing Time

Hi,

I am verifying my reports processing time. I get the information from the Reporting Service DB - [ExecutionLogs] table. I have the following information:

[TimeEnd] – time that reports generation ends.

[TimeStart] - time that reports generation starts.

[TimeDataRetrieval] - amount of time spent running the data sources.

[TimeProcessing] - time spent processing the report.

[TimeRendering] - time spent generating the output format.

If this information is correct the following statement should be true:

([TimeEnd] - [TimeStart]) = ([TimeDataRetrieval] + [TimeProcessing] + [TimeRendering])

But it isn't, ([TimeEnd] - [TimeStart]) is always bigger then ([TimeDataRetrieval] + [TimeProcessing] + [TimeRendering]).

Why does this happen?

Regards,

Rodrigo

That's interesting. I've never looked to check that. Are you seeing a big difference? how much is it a % of total execution time?

I suspect that TimeDataRetrieval, TimeProcessing, TimeRendering are each recorded by the subsystem resposible for each of those. I would expect there to be a small amount of overhead for the engine to do it's stuff and transition between the various phases between start and end.

|||

I have the following results for different reports:

End-Start

(ms)

Sum

(ms)

%

5030 3717 73,8966203 17437 12009 68,870792 5047 3374 66,851595 4733 3142 66,3849567 23170 15348 66,2408287 4780 3134 65,5648536 15750 10129 64,3111111 13017 7849 60,2980718 11153 6548 58,7106608 13170 7660 58,1624905 8830 5119 57,9728199

The first column is ([TimeEnd] - [TimeStart])

The second column is ([TimeDataRetrieval] + [TimeProcessing] + [TimeRendering])

How can i find the cause of this difference ?

Regards,

Rodrigo

|||

The difference is consistently large, too large. From your observations i.e. looking at the screen with a stopwatch, which is the "more correct" value?

|||

Do the reports have multiple datasets?

If multiple datasets are present and transactions are not used, SSRS will execute dataset queries in parallel. However, since there is only one data retrieval value in the execution log, it will pick the time of the longest data retrieval thread.

In addition, time data retrieval + processing + rendering does not cover all the time between the start and the end time of the request. Generally however, the other operations performed should just be small fraction compared to ([TimeDataRetrieval] + [TimeProcessing] + [TimeRendering]).

-- Robert

Processing one row at a time

I'm populating a new table based on information in an existing table. The new table is a list of all "items" and contains a primary key. The old table is a database of receipts where items can appear many times in any order.

I have put together the off-the-shelf components to do this, using a lookup transformation to see if the item is already in the new table. Problem is, because there's so much repetition in the old table I need to process the old table one row at a time. Batch processing is generating errors because the lookup doesn't detect duplicates within the buffer.

I tried setting the "DefaultBufferMaxRows" property of the task to 1, but that doesn't seem to have any effect.

To get the data from the old table, I'm using an OLE DB source. To get the data into the new table, I'm using the OLE DB Command transformation with parameters to execute an INSERT statement.

This is a job I have to do exactly once, so I don't care if I have to run it overnight. I'd rather have a simple, easy to understand but inefficient script so I understand what it's doing completely.

Any help on forcing SSIS to process one row at a time?

Lee Silverman
JackRabbit Sports

Could you use an Aggregate transform to eliminate the duplicates, or a GROUP BY in the source select?|||

Could you use an Aggregate transform to eliminate the duplicates, or a GROUP BY in the source select?

Unfortunately no. Items wwith the same unique identifier have often had important changes made to the descriptive fields over the course of the last two or three years. When the Lookup finds a unique identifier that is already in the table, the branch that handles that then goes on to check to see if the descriptive info has changed, and if so updates the data in the table. Likewise, on the side of the lookup where the unique identifier was not found, that branch needs to see handle the case where the new item shares an item lookup number with a previous item, and if so marks the previous item as expired.

The data in the source table is ordered sequentially, and the logic of the script requires that each line item be processed one at a time. It's going to be slow, but once it's done it'll be done.

Lee Silverman
JackRabbit Sports

|||Could you use a For Each Container with the ADO enumerator to loop through your recordset of tableA and then do your manipulations/lookups within that?|||You could use an OLE DB Command to do your inserts and updates, and disable caching on the lookups. That will force the lookups to requery the database for each row. I'm not sure that will work in your case, but it might be worth a try.|||

How does one disable caching on lookups? Do you have to check "enable memory restriction" but then not check "enable caching"?

In any event, I solved the problem by writing a script in T-SQL, and I took the opportunity to teach myself how to use a cursor to scroll through the original table one line at a time. It's running now.

Lee

|||

Lee Silverman wrote:

How does one disable caching on lookups? Do you have to check "enable memory restriction" but then not check "enable caching"?

That is it.

Processing cube from SSAS - OLE DB error

I have made a new dimension and setup the reference in dimension usage. Every time I process the cube i got the following message

Error 1 OLE DB error: OLE DB or ODBC error: Invalid column name 'PReference'.; 42S22. 0 0

The value in this column is 0,1 or 2. I have set the value in all rows to 1 and still get the same error.

I have tried to create a new column - updated value to 1 and refreshed the datasource and changed the dimension and dimension usage and I still got the same error processing the cube.

I have tried to use another integer field in dimension usage and this works fine.

Maybe it is something on the table - i have tried almost all but I can't solve this problem.

Leo Pedersen

The error message "Invalid column name 'PReference'" is saying that an invalid column name was specified in the SQL query that AS generated. Run Profiler on the AS server and capture the SQL query. It should point you to the problem.

Processing after new data

I'm trying to build a cube in the "real world" for the first time. I had my fact table populated with one day's worth of data, set up my time dimension using named calculations based on a datetime within the DS View of the fact table (a la the Larsen book), then built and deployed and everything worked great.

However, when I added the next day's worth of data into the fact table and tried to get it to process incrementally (and then fully), the processing fails and it looks like it's complaining about those named calculations that worked so well on day one. Any thoughts on what I might be missing here? Thanks

Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_fact_ALI, Column: YearOfBid, Value: 2006; Table: dbo_fact_ALI, Column: QuarterOfBid, Value: 2006Q2; Table: dbo_fact_ALI, Column: MonthOfBid, Value: 200607; Table: dbo_fact_ALI, Column: DayOfBid, Value: 20060702; Table: dbo_fact_ALI, Column: HourOfBid, Value: 00:00. Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: Time Key of Dimension: Dim Time from Database: ALI DM 2, Cube: ALI DM 2, Measure Group: Fact ALI, Partition: Fact ALI, Record: 577. Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. Errors in the OLAP storage engine: An error occurred while processing the 'Fact ALI' partition of the 'Fact ALI' measure group for the 'ALI DM 2' cube from the ALI DM 2 database.

You must process each dimension before the cube if you have updated your source system since the last day.

HTH

Thomas Ivarsson

Processing a cube dimension

I am trying to process a time dimension on a cube and get the following error. Can some one please help. How can I fix this error?

Error 1 Errors related to feature availability and configuration: Exceeded the maximum number of partitions in a measure group. The installed server editions allows not more than 3 partitions and not more than 1 writeback partition. 0 0

That is the error if you try to use Standard Edition and partitioning.

Partitioning is an EE-only feature.

_-_-_ Dave

Processing a CUBE

Hi,

I have one doubt in my mind. If I build a cube on dt.st Oct. next time when in 1st Dec. I want to see the same cube with effect upto 1st Dec. do I need to process the cube in order to get effect till 1st Dec.?

Nilkanth Desai

If you have to add new data then yes have to prossess the cube, you can do a incremental process|||

Thanks for your reply.

Nilkanth

Processed Date for a Cube

Hi

I want to be able to display to the users the date/time a cube was last processed. We have SSAS 2005 and presenting the cube through Excel 2003 (with the SSAS 9 OLAP driver).

Thanks

LastProcessed is a property on the object avaliable only through AMO. Dont think Excel will show you this property.

You can possible wirte a AMO code yourself and use Excel programming model to show it.

Another way is to create a dimension with a single memeber based on the column in the relational database where you would keep the current date and during processing you update this dimension to pickup a new value.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks for the suggestions.

I created a table with the current date and use this.

Process: %processor time

Hi,
I am monitoring the above counter on a 4 processor box
and have 2 questions:
(1) the value for 2 instances (there are 5 all together)
is ~200, so how is this a %? Is this to be read as 200/4
so 50%?
(2) the instances are listed as sqlservr #1, sqlservr #2
etc - how can I resolve this to the instance name?
TIA,
Mary
Mary,
1: Yes, divide the total percent by the number of instances. Double check
with Task Manager.
2. What operating system are you using? On Windows XP, Perfmon separates the
counters by instance name.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"Mary" <anonymous@.discussions.microsoft.com> wrote in message
news:112801c4f721$51e21910$a501280a@.phx.gbl...
> Hi,
> I am monitoring the above counter on a 4 processor box
> and have 2 questions:
> (1) the value for 2 instances (there are 5 all together)
> is ~200, so how is this a %? Is this to be read as 200/4
> so 50%?
> (2) the instances are listed as sqlservr #1, sqlservr #2
> etc - how can I resolve this to the instance name?
> TIA,
> Mary
|||Thanks Ron,
I'm using Win 2000 Server. The counters are separated by
instance name for most things, but for Process they are
not - they appear as a numbered list in the bottom right-
hand corner.
Thanks,
Mary

Process: %processor time

Hi,
I am monitoring the above counter on a 4 processor box
and have 2 questions:
(1) the value for 2 instances (there are 5 all together)
is ~200, so how is this a %? Is this to be read as 200/4
so 50%?
(2) the instances are listed as sqlservr #1, sqlservr #2
etc - how can I resolve this to the instance name?
TIA,
MaryMary,
1: Yes, divide the total percent by the number of instances. Double check
with Task Manager.
2. What operating system are you using? On Windows XP, Perfmon separates the
counters by instance name.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Mary" <anonymous@.discussions.microsoft.com> wrote in message
news:112801c4f721$51e21910$a501280a@.phx.gbl...
> Hi,
> I am monitoring the above counter on a 4 processor box
> and have 2 questions:
> (1) the value for 2 instances (there are 5 all together)
> is ~200, so how is this a %? Is this to be read as 200/4
> so 50%?
> (2) the instances are listed as sqlservr #1, sqlservr #2
> etc - how can I resolve this to the instance name?
> TIA,
> Mary|||Thanks Ron,
I'm using Win 2000 Server. The counters are separated by
instance name for most things, but for Process they are
not - they appear as a numbered list in the bottom right-
hand corner.
Thanks,
Mary

Process: %processor time

Hi,
I am monitoring the above counter on a 4 processor box
and have 2 questions:
(1) the value for 2 instances (there are 5 all together)
is ~200, so how is this a %? Is this to be read as 200/4
so 50%?
(2) the instances are listed as sqlservr #1, sqlservr #2
etc - how can I resolve this to the instance name?
TIA,
MaryMary,
1: Yes, divide the total percent by the number of instances. Double check
with Task Manager.
2. What operating system are you using? On Windows XP, Perfmon separates the
counters by instance name.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Mary" <anonymous@.discussions.microsoft.com> wrote in message
news:112801c4f721$51e21910$a501280a@.phx.gbl...
> Hi,
> I am monitoring the above counter on a 4 processor box
> and have 2 questions:
> (1) the value for 2 instances (there are 5 all together)
> is ~200, so how is this a %? Is this to be read as 200/4
> so 50%?
> (2) the instances are listed as sqlservr #1, sqlservr #2
> etc - how can I resolve this to the instance name?
> TIA,
> Mary|||Thanks Ron,
I'm using Win 2000 Server. The counters are separated by
instance name for most things, but for Process they are
not - they appear as a numbered list in the bottom right-
hand corner.
Thanks,
Mary