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!

Large number of INSERT statements - not all are executed

Hello!

I have a developer that is playing around with some SQL statements
using VB.NET. He has a test table in a SQL 2000 database, and he has
about 2000 generated INSERT statements.

When the 2000 INSERT statements are run in SQL query analyzer, all
2000 rows are added to the table. When he tries to send the 2000
statements to SQL Server through his app., a random number of
statements do not get executed. But, SQL Profiler shows that each of
the 2000 statements are getting sent to the server.

I suggested that he add a "GO" statement at the end of the INSERT
block, but the statement fails when that is sent to the server.

I know that this is not the ideal manner to insert bulk data to the
system, but now we are all just curious as to why SQL server doesn't
execute each individual INSERT.

Any thoughts?Hi Dmitri,

Quote:

Originally Posted by

I have a developer that is playing around with some SQL statements
using VB.NET. He has a test table in a SQL 2000 database, and he has
about 2000 generated INSERT statements.
>
When the 2000 INSERT statements are run in SQL query analyzer, all
2000 rows are added to the table. When he tries to send the 2000
statements to SQL Server through his app., a random number of
statements do not get executed. But, SQL Profiler shows that each of
the 2000 statements are getting sent to the server.

How were the statements sent to SQL Server?
One batch with 2k statements or one statement per batch?
What about transactions (autocommit mode)?
What events were set to be captured by Profiler?

Quote:

Originally Posted by

I suggested that he add a "GO" statement at the end of the INSERT
block, but the statement fails when that is sent to the server.

Wrong suggestion. "GO" is not an SQL statement and can be used only in
Query Analyzer (Enterprise Manager, Management Studio). It signals the
end of a batch to MSSQL utilities, SQL Server doesn't understand it at all.

--
Best regards,
Marcin Guzowski
http://guzowski.info|||Dmitri,

SQL Server doesn't just ignore transactions. If the statements are showing
up in SQL Server Profiler, then SQL is executing or attempting to execute
them. There may be other reasons why you are not getting the expected
results. Try these steps:

1. Backup the database.
2. Run a trace. Remove the Existing Connection and Audit events before
starting the trace. They are not necessary for this exercise and create
additional noise. You may want to set up a filter that captures only the
application being tested.
3. Save the trace as a SQL Script.
4. Restore the database.
5. Open the SQL Script and run it.
6. See if there are any errors or warnings that are not being trapped by the
VB.NET application.

-- Bill

"Dmitri" <nienna.gaia@.gmail.comwrote in message
news:1171036327.517795.9440@.s48g2000cws.googlegrou ps.com...

Quote:

Originally Posted by

Hello!
>
I have a developer that is playing around with some SQL statements
using VB.NET. He has a test table in a SQL 2000 database, and he has
about 2000 generated INSERT statements.
>
When the 2000 INSERT statements are run in SQL query analyzer, all
2000 rows are added to the table. When he tries to send the 2000
statements to SQL Server through his app., a random number of
statements do not get executed. But, SQL Profiler shows that each of
the 2000 statements are getting sent to the server.
>
I suggested that he add a "GO" statement at the end of the INSERT
block, but the statement fails when that is sent to the server.
>
I know that this is not the ideal manner to insert bulk data to the
system, but now we are all just curious as to why SQL server doesn't
execute each individual INSERT.
>
Any thoughts?
>

|||Dmitri (nienna.gaia@.gmail.com) writes:

Quote:

Originally Posted by

I have a developer that is playing around with some SQL statements
using VB.NET. He has a test table in a SQL 2000 database, and he has
about 2000 generated INSERT statements.
>
When the 2000 INSERT statements are run in SQL query analyzer, all
2000 rows are added to the table. When he tries to send the 2000
statements to SQL Server through his app., a random number of
statements do not get executed. But, SQL Profiler shows that each of
the 2000 statements are getting sent to the server.
>
I suggested that he add a "GO" statement at the end of the INSERT
block, but the statement fails when that is sent to the server.
>
I know that this is not the ideal manner to insert bulk data to the
system, but now we are all just curious as to why SQL server doesn't
execute each individual INSERT.

Did he send one batch with 2000 statements, or 2000 batches? Without
seeing the code, it's difficult to know what we are talking about.

The most effective way of inserting data this way is:

INSERT tbl (...)
EXEC('SELECT ''thisvalue'', 1, ''thatvalue''
SELECT ''thisothervalue'', 2, ''thatothervalue''
...')

This keeps it down to one INSERT statement, but many small SELECT
statements that are easy to compile. (The alternative SELECT UNION
is very expensive to compile for 2000 rows.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx