processing cube not pumping data

I am trying to process a cube on a fact query that should return about 600M rows.

When I run the processing, all Dimensions are processed successfully, and it starts processing the Facts, but no row count is shown indicating that it does not actually start streaming the data to Analysis Services. It has been running for 30 minutes and no count of processed rows has appeared.

When I double-click the query and open the query in SQL Server Management studio it runs fine. Also I tried it with set rowcount 100000 and it starts streaming data to query output right away and gets to 100000 rows within a minute or so.

So the query seems optimized and returns the data quickly.

Also tempdb seems to be growing slowly.

Does Analysis Services run the query in some other way (i.e. with different execution plan optimization) than when I do it manually from SQL Management Studio How do I check that

Hello. I have some questions:

• Is the fact table in the same database as your dimension tables
• Is SSAS2005 on the same server as the source database
• Are you using views for the dimension tables and the fact table
• Are you suing any sort of security filter on the fact table/view that allow users only to see certain records

Regards

Thomas Ivarsson

Thomas Ivarsson wrote:

Hello. I have some questions: Is the fact table in the same database as your dimension tables Is SSAS2005 on the same server as the source database Are you using views for the dimension tables and the fact table Are you suing any sort of security filter on the fact table/view that allow users only to see certain records Regards Thomas Ivarsson

1. Yes, the fact table is in the same database. All data is in the same database.

2. SSAS2005 is on the same server. Also the protocol type is LPC (from SQL Activity Monitor)

3. Not using views for dimensions, but using Named Queries in the Data Source View for almost everything - including the fact table which is actually a query.

4. No special security filter. Used all default values.

Also additional note, when I add additional where clause to my fact query to reduce the output the processing runs without flooding tempdb and starts pumping data withing 1-5 minutes.

OK! You are using named queries in the DSV. Does everything work correctly if you use tables or views

I am not sure if your named queries never work or work sometimes.

The next thing i would check in the DSV is that you have correct foreign(logical) keys in the DSV between the dimensions(named queries) and the fact table(named query).

Also check that the same relations are defined within the cube, in the dimension usage tab in BIDS.

Last. Can you have a problem with that the named queries in the dimensions do not have the same members as the dimension keys in the fact table. If you have time members only for 2006 in the time dimension but only fact records for 2005

Regards

Thomas Ivarsson

1. I did not use tables or views because fact set needs to  be produced by a join of 3 tables.

2. The same cube (with same named queries) work well if I add a clause to the Fact (named query) which limits the output rows. When I remove the filtering condition it causes the processing to not pump data and flood tempdb. FYI when I specify the filtering condition I still process about 10M fact rows.

3. All keys and relationship seem to work with a smaller set of data - 10M Fact Rows. (The full set of Fact data is about 630M rows).

4. The dimension usage appears to be correct in BIDS. Also when I process the cube with filtered down fact set (to 10M rows), i am able to connect to cube and browse it in BIDS and it seems to be all good.

5. I had seen missing key value error as you describe but that only happens when it actually pumps fact data and then errors out of the processing. I have seen these errors on filtered fact dataset (10M rows) and eliminted those errors - also reprocessed those dimensions.

SQL Server seems to behave different when the same query is sent from Analysis services vs. when it's run from SQL Management Studio. I pasted the query from Analysis Services processing cube progress window and ran it just find in the SQL Management Studio - starts pumping data within 1-2 seconds and does not flood tempdb.

You seem to have checked everything that I could see as causes for the problem. 10 M fact rows is not that much that will create problems for a standard server.

It is also very hard to see why the named query will not execute without a filter clause. I have searched for file limits of the SSAS2005 data files but I have not found anything this far.

What hardware are you using(32bit/64bit, no of CPU:s, RAM) . Are you using the standard or enterprise version of SSAS2005 SP1

Why are you not able to create a view of the three fact tables With that large data set a named query is not the best choice, for performance reasons.

Any messages in the eventlog when you process without the filter clause Have you also started a trace with SQL server profiler when you process the cube

Regards

Thomas Ivarsson

Thomas Ivarsson wrote:

You seem to have checked everything that I could see as causes for the problem. 10 M fact rows is not that much that will create problems for a standard server. It is also very hard to see why the named query will not execute without a filter clause. I have searched for file limits of the SSAS2005 data files but I have not found anything this far. What hardware are you using(32bit/64bit, no of CPU:s, RAM) . Are you using the standard or enterprise version of SSAS2005 SP1 Why are you not able to create a view of the three fact tables With that large data set a named query is not the best choice, for performance reasons. Any messages in the eventlog when you process without the filter clause Have you also started a trace with SQL server profiler when you process the cube Regards Thomas Ivarsson

10M Rows is the filtered set - it's not the problem. I also tried filtering so that more rows are returned - for example 30M rows also works. However, the 630M rows is causing a problem.

Specifications:

• dual Xeon
• 4GB ram,
• XP Professional SP2.

• SQL Server 2005 SP2 (CTP). Developer version.

I started the profiler now, what can I do with it other than looking at the query that it runs I already tried that query in SQL Management Studio and it runs fine and fast. Are there other checks I can do with the profiler

You are running this load on a workstation, probably with one hard drive I am surprised that one WS can support this amount of data.

I think that your hardware is not sufficient to handle this amount of data. SSAS2005 32 bit only supports 3 GB of RAM, if I am not wrong.

Without knowing anything about your dimensions I would recommend SSAS2005 64 bit(Enterprise edition) with supporting O/S, a lot of RAM and a good disk(I/O) system.

Maybe you will need a 4xCPU-system and SSAS2005 partitions that is included in the SQL Server Enterprise edition.

So, you will need a good server to run this amount of data.

Profiler will not help with these problems.

Happy Holidays

/Thomas Ivarsson

the machine has 2 HDDs.

I am not sure how the workstation argument is an excuse for Analysis Services to not even start to read data.

• The query is optimized and runs fast from SQL Management Studio.
• When Analysis Services tries to run the query, it floods tempdb and does not read even the first 10,000 rows!
• Disk IO and CPU is average or below in that state. - so not sure how this is a hardware issue.
• Analysis Services doesn not try to grab more RAM (because it fails to read data). There is about 1GB if free RAM in that state. SQL Server is alotted 2GB but it stays around 1610MB RAM use.

Since there is not excess HDD use, no excess CPU usage and no excess RAM use, why is this a hardware issue

I do not have all details why this would not work, like if the developer-version have some built-in limitations that will stop you, even if you hardware can support this.

A discussion about if Windows XP supports this or not, I do not have a clue. It is very special hardware that you run this load on.

You are simply trying to load to much data on a WS.

Regards

Thomas Ivarsson

Thomas Ivarsson wrote:

You are simply trying to load to much data on a WS.

I am loading too much data where All this data is already on the hard drive.

I am not trying to load it all into RAM, so I am not sure if any superficial estimations should apply here.

I ran the unfiltered query with SQL profiler on.

The query shows as processing in Cube Process progress (no row counts shown up yet). It has been 10 minutes and the command has not shown up on Profiler yet.

So it looks like the query does not make it to the SQL Server. - Analysis Services is doing something else even through it says it's running the query.

mobigital wrote:

I ran the unfiltered query with SQL profiler on. The query shows as processing in Cube Process progress (no row counts shown up yet). It has been 10 minutes and the command has not shown up on Profiler yet. So it looks like the query does not make it to the SQL Server. - Analysis Services is doing something else even through it says it's running the query.

Additional note:

I used both SQL Server profiler and Analysis Services profiler, here are their messages:

AS profiler reports:

** Processing of the partition has started
** Execute SQL - shows the correct SQL for unfiltered fact data.

SQL Profiler reports:

** stmt_completed:

select
substring('NY',is_read_only+1,1),user_name()
from
sys.databases
where
name=DB_NAME()

** BatchCompleted:

exec [sys].sp_oledb_ro_usrname

** BatchCompleted:

select collationname(0x0904D00034)

So the SQL Statement never shows up on SQL Profiler.

in the meantime tempdb is growing....

NOTE: When I process a filtered fact query, the query shows up on SQL Profiler right away.

Akshai Mirchandani wrote:

If you are seeing tempdb grow, you should be able to see the query that is running to use up resources. Perhaps your Profiler filter against SQL is showing the queries only after they are completed Otherwise, how would tempdb grow I can't explain why it would behave differently when you run it in SSMS -- perhaps you have set the data source to turn on Snapshot Isolation Thanks, Akshai