Advice on Partitioning and Processing

I'd appreciate some advice on the following:

I'm building a cube which is based on data in a SQL 2000 DB. There are around 1.2 billion rows of fact data in 26 tables. Each table uses approx 6 Gig. There are 4 Dimensions, 3 dimensions have less than a 1000 members with the 4th having 65000. For convenience I am creating 26 Cube partitions aligned to the DB tables, each partition will have around 50 million rows. Does this sound like a reasonable proposal? The reason I ask is that I am seeing very poor performance whilst calling ProcessData. I have processed various numbers of partitions in parallel and cannot achieve the same times that I can get on the existing AS2000 rig using the Parallel Processing Utility.

I have tested the data read by writing a .net application to execute the same DB queries in parallel that the partitions use (achieving 200 million rows in 20 minutes) and there are no issues related to network, disk or the DB server.

Processing seems to start well but around a minute in the Network utilisation drops down to 10 percent and CPU utilisation to 30%. It then goes back up to 100% for a short period and then drops again. This is repeated continually until processing is finished. At current speeds it will take 26 hours to complete as opposed to 10 hours on AS2000. The box is a 64-bit 4way hyperthreaded with 32 GB of RAM. AS2005 is Enterprise with SP1.

Any suggestions would be appreciated as I'm running out of ideas!

Rob.

You should check yourself against best practices you would find the project REAL implemetation http://www.microsoft.com/sql/solutions/bi/projectreal.mspx

I would say the partition size is a bit big. You should try and partition some more for and split a single partition in two. But that doesnt look like a major problem.

Question for you: in AS2005 you processing partitions in parallel. How many? Is the pattern the same for any number of partitions?

What OLEDB provider you are using to read data from SQL Server?

The reading rates looks to be slow. See where the bottleneck is. In your test application, the fair comparison not only when you execute SQL query, but you also retrevie all the data returned by the query.

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

AdventureWorks Report Samples Problem

Hi,
I am trying to deploy the AdventureWorks OLAP cube in C:\Program
Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services
Project but unfortunately the folder "AdventureWorks Analysis Services
Project" does not exist nor does the file Adventure Works DW.dwproj. I have
installed the samples using the Microsoft SQL Server 2005 Samples installer.
Where can I find this directory and project?
Kind regards,
TrevorHi Trevor,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to launch
AdventureWorks Analysis Services Project but cannot find it in the
identified folder. If I have misunderstood your concern, please feel free
to point it out.
Based on my knowledge, please perform the steps below to make sure you have
installed AdventureWorksDW sample datawarehouse and AdventureWorks Sample
OLAP.
- Open Add or remove Programs in Control Panel
- Select Microsoft SQL Server 2005, click Change
- Select Workstation Components under SQL Server 2005 common components,
click Next
- After System Configuration Check, click Next.
- Click Change Installed Components
- Expand Documentation, Samples and Sample Databases, Expand Sample
Databases, Expand AdventureWorksDW Sample Data Warehouse. Make sure
everything was selected and then click Next
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Adventure Works Cube

Hi all,

I try to browser the Adventure Work Cube from the SQL Server Business Intelligence Development Sudio, but it give me the wrong result. Fisrt I have paste the Date.Fiscal hierachy in the Filter Filter and select July 1 2001 and July 2 2001, then I paste the Sales Amount measure in the Data Field. The result is $517,737.44. Now I paste the Date.Fiscal Year attribute in the Row Fields, but the result is change to $503,80592. The result is wrong. The sales amount has been excluded July 2 2001 data. Why this happen?

Thanks,

Thomas,

I've recreated the steps you described. I get the same $517,737.44 value you described. When I drag the Date.[Fiscal Year] attribute hierarchy to the rows, I continue to get $517,737.44.

Do you know what service pack you are working with?

Thanks,
Bryan

|||

Hi Bryan

I am using AS 9.00.3.042. When you filter the day, where you drag the Date.Fiscal hierarchy. If I put the Date.Fiscal hierarchy in the dimension filter panel, then the result is correct. However, if I put the Date.Fiscal hierarchy in the Drop Filter Fields, then the result is not correct.

Thanks,

|||

Sorry, but I'm just not getting the same behavior and I'm using the same SP level. Could you possibly trace the MDX that is submitted for the query (using Profiler) and post that to this thread? That might help determine what's going on.

Thanks,
Bryan

|||

Hi Bryan,

I am using 64bit, do you think that cause the problem. Here is what the MDX looklike:

WITH
SET [Filtered] AS
'
DISTINCT(
{
[Date].[Fiscal].[Date].&[2],
[Date].[Fiscal].[Date].&[1]
}
)
'
SET [Tree] AS
'
HIERARCHIZE(
GENERATE(
[Filtered],
{
ANCESTOR([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Fiscal Year]),
ANCESTOR([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Fiscal Semester]),
ANCESTOR([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Fiscal Quarter]),
ANCESTOR([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Month]),
[Date].[Fiscal].CURRENTMEMBER
}
)
)
'
SELECT [Tree] ON Columns FROM [Adventure Works]

<PropertyList xmlns="urnchemas-microsoft-com:xml-analysis" xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>Adventure Works DW</Catalog><Timeout>3600</Timeout><Content>SchemaData</Content><Format>Multidimensional</Format><AxisFormat>TupleFormat</AxisFormat><ShowHiddenCubes>true</ShowHiddenCubes><DbpropMsmdFlattened2>false</DbpropMsmdFlattened2><SspropInitAppName>Microsoft SQL Server Management Studio</SspropInitAppName><SafetyOptions>2</SafetyOptions><Dialect>MDX</Dialect><ClientProcessID>3696</ClientProcessID><LocaleIdentifier>1033</LocaleIdentifier></PropertyList>

CREATE SESSION
MEMBER [Adventure Works].[Date].[Fiscal].[{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Hier33MultiFilter__] AS
'
AGGREGATE({
[Date].[Fiscal].[Date].&[1],
[Date].[Fiscal].[Date].&[2]
})
'

<PropertyList xmlns="urnchemas-microsoft-com:xml-analysis" xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>Adventure Works DW</Catalog><Timeout>3600</Timeout><Content>None</Content><Format>Tabular</Format><ShowHiddenCubes>true</ShowHiddenCubes><DbpropMsmdFlattened2>false</DbpropMsmdFlattened2><SspropInitAppName>Microsoft SQL Server Management Studio</SspropInitAppName><SafetyOptions>2</SafetyOptions><Dialect>MDX</Dialect><ClientProcessID>3696</ClientProcessID><LocaleIdentifier>1033</LocaleIdentifier></PropertyList>

SELECT
NON EMPTY [{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Axis1Set0]
DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS,
{
[Measures].[Sales Amount]
}
ON ROWS
FROM [Adventure Works]
WHERE
(
[{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Hier33MultiFilter__]
)
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR

<PropertyList xmlns="urnchemas-microsoft-com:xml-analysis" xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>Adventure Works DW</Catalog><Timeout>3600</Timeout><Format>Native</Format><ShowHiddenCubes>true</ShowHiddenCubes><DbpropMsmdFlattened2>false</DbpropMsmdFlattened2><SspropInitAppName>Microsoft SQL Server Management Studio</SspropInitAppName><SafetyOptions>2</SafetyOptions><Dialect>MDX</Dialect><ClientProcessID>3696</ClientProcessID><LocaleIdentifier>1033</LocaleIdentifier></PropertyList>

Thanks,

|||

Hi Bryan,

Here is the result:

Date.Fiscal

(Multiple Items)

Fiscal Year Sales Amount

FY2002 $503,805.92

Grand Total $503,805.92

|||

I'm not seeing the set definition for [{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Axis1Set0].

B.

|||

Sorry Bryan,

I forgot to include this:

CREATE SESSION
SET [Adventure Works].[{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Axis1Set0] AS
'
{
{ [Date].[Fiscal Year].[All Periods] },
AddCalculatedMembers([Date].[Fiscal Year].[Fiscal Year].MEMBERS)
}
'

|||

Here's what I've been able to figure out.

You're dropping the Fiscal Hierarchy on to the "Drop Filters Here" section of the interface. This is what generates this session member:

Code Snippet

CREATE SESSION

MEMBER [Adventure Works].[Date].[Fiscal].[{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Hier33MultiFilter__] AS

'

AGGREGATE({

[Date].[Fiscal].[Date].&[1],

[Date].[Fiscal].[Date].&[2]

})

'

The AGGREGATE function is causing you to select the first member of the set. If you change the members to &[2] and &[3], you'll get the value for just July 2, 2001. If you replace AGGREGATE with SUM, you'll get the right result but that's not really an option here.

I can't really say why the AGGREGATE function is behaving this way, but would suggest you use the other filtering option in the browser window. If you look just above the "Drop Filters Here" section, you'll see a grid with an entry that looks like "Select Dimension". Apply your filter here.

What his does that is different is that it creates a subcube which limits the available values. This will give you the results you are looking for:

Code Snippet

CREATE SUBCUBE [Adventure Works] AS ( SELECT ( { [Date].[Fiscal].[Date].&[1], [Date].[Fiscal].[Date].&[2] } ) ON COLUMNS FROM [Adventure Works])

Hope that helps,
Bryan

|||

Hi Bryan,

The reason I drag the Fiscal Hierarchy in the "Drop Filters Here" is I want it to act like Excel Pivot Table. When I browser the cube though Excel Pivot Table, it has the same error. Most of our end user use Excel Pivot Table to brower the cube, but AS2005 give the wrong answer for this case.

Thanks

Thanks

|||

I'm at a bit of a loss to explain this beahviour.

One possible work around is to hide the [Date].Fiscal Year] attribute. I find that having [Date].[Fiscal].[Fiscal Year] and [Date].[Fiscal Year] in the same cube just confuses things,especially when using OWC as it is not really mutliple hierarchy and attribute aware.

What your users would need to do then is to drag [Date].[Fiscal] onto the column and filter it in place. If you start from your example that is showing the incorrect figures and drag [Date].[Fiscal Year] off the grid and drag the [Date].[Fiscal] from the filter to the columns does that give you a grid that looks how you want?

|||

I asked a friend of mine on the product team and it is possible that this issue was fixed in the hotfix rollup that was released for SP2 http://support.microsoft.com/kb/939537 I could not find a specific reference, but there are a couple of broader issues which it might come under. It might be worth testing this in a Virtual PC to see if it corrects the issue.

As with all hotfixes, it is only recommended to install this if it does address a particular issue. Otherwise they recommend waiting for the next service pack, but I have not heard when another service pack is likely to be released. It would not surprise me to see the next SP come out after Windows Server 2008 is released (which should be Q1 next year), but this is just a guess on my behalf.

sql

Adventure Works Cube

Hi all,

I try to browser the Adventure Work Cube from the SQL Server Business Intelligence Development Sudio, but it give me the wrong result. Fisrt I have paste the Date.Fiscal hierachy in the Filter Filter and select July 1 2001 and July 2 2001, then I paste the Sales Amount measure in the Data Field. The result is $517,737.44. Now I paste the Date.Fiscal Year attribute in the Row Fields, but the result is change to $503,80592. The result is wrong. The sales amount has been excluded July 2 2001 data. Why this happen?

Thanks,

Thomas,

I've recreated the steps you described. I get the same $517,737.44 value you described. When I drag the Date.[Fiscal Year] attribute hierarchy to the rows, I continue to get $517,737.44.

Do you know what service pack you are working with?

Thanks,
Bryan

|||

Hi Bryan

I am using AS 9.00.3.042. When you filter the day, where you drag the Date.Fiscal hierarchy. If I put the Date.Fiscal hierarchy in the dimension filter panel, then the result is correct. However, if I put the Date.Fiscal hierarchy in the Drop Filter Fields, then the result is not correct.

Thanks,

|||

Sorry, but I'm just not getting the same behavior and I'm using the same SP level. Could you possibly trace the MDX that is submitted for the query (using Profiler) and post that to this thread? That might help determine what's going on.

Thanks,
Bryan

|||

Hi Bryan,

I am using 64bit, do you think that cause the problem. Here is what the MDX looklike:

WITH
SET [Filtered] AS
'
DISTINCT(
{
[Date].[Fiscal].[Date].&[2],
[Date].[Fiscal].[Date].&[1]
}
)
'
SET [Tree] AS
'
HIERARCHIZE(
GENERATE(
[Filtered],
{
ANCESTOR([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Fiscal Year]),
ANCESTOR([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Fiscal Semester]),
ANCESTOR([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Fiscal Quarter]),
ANCESTOR([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Month]),
[Date].[Fiscal].CURRENTMEMBER
}
)
)
'
SELECT [Tree] ON Columns FROM [Adventure Works]

<PropertyList xmlns="urnchemas-microsoft-com:xml-analysis" xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>Adventure Works DW</Catalog><Timeout>3600</Timeout><Content>SchemaData</Content><Format>Multidimensional</Format><AxisFormat>TupleFormat</AxisFormat><ShowHiddenCubes>true</ShowHiddenCubes><DbpropMsmdFlattened2>false</DbpropMsmdFlattened2><SspropInitAppName>Microsoft SQL Server Management Studio</SspropInitAppName><SafetyOptions>2</SafetyOptions><Dialect>MDX</Dialect><ClientProcessID>3696</ClientProcessID><LocaleIdentifier>1033</LocaleIdentifier></PropertyList>

CREATE SESSION
MEMBER [Adventure Works].[Date].[Fiscal].[{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Hier33MultiFilter__] AS
'
AGGREGATE({
[Date].[Fiscal].[Date].&[1],
[Date].[Fiscal].[Date].&[2]
})
'

<PropertyList xmlns="urnchemas-microsoft-com:xml-analysis" xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>Adventure Works DW</Catalog><Timeout>3600</Timeout><Content>None</Content><Format>Tabular</Format><ShowHiddenCubes>true</ShowHiddenCubes><DbpropMsmdFlattened2>false</DbpropMsmdFlattened2><SspropInitAppName>Microsoft SQL Server Management Studio</SspropInitAppName><SafetyOptions>2</SafetyOptions><Dialect>MDX</Dialect><ClientProcessID>3696</ClientProcessID><LocaleIdentifier>1033</LocaleIdentifier></PropertyList>

SELECT
NON EMPTY [{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Axis1Set0]
DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS,
{
[Measures].[Sales Amount]
}
ON ROWS
FROM [Adventure Works]
WHERE
(
[{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Hier33MultiFilter__]
)
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR

<PropertyList xmlns="urnchemas-microsoft-com:xml-analysis" xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>Adventure Works DW</Catalog><Timeout>3600</Timeout><Format>Native</Format><ShowHiddenCubes>true</ShowHiddenCubes><DbpropMsmdFlattened2>false</DbpropMsmdFlattened2><SspropInitAppName>Microsoft SQL Server Management Studio</SspropInitAppName><SafetyOptions>2</SafetyOptions><Dialect>MDX</Dialect><ClientProcessID>3696</ClientProcessID><LocaleIdentifier>1033</LocaleIdentifier></PropertyList>

Thanks,

|||

Hi Bryan,

Here is the result:

Date.Fiscal

(Multiple Items)

Fiscal Year Sales Amount

FY2002 $503,805.92

Grand Total $503,805.92

|||

I'm not seeing the set definition for [{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Axis1Set0].

B.

|||

Sorry Bryan,

I forgot to include this:

CREATE SESSION
SET [Adventure Works].[{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Axis1Set0] AS
'
{
{ [Date].[Fiscal Year].[All Periods] },
AddCalculatedMembers([Date].[Fiscal Year].[Fiscal Year].MEMBERS)
}
'

|||

Here's what I've been able to figure out.

You're dropping the Fiscal Hierarchy on to the "Drop Filters Here" section of the interface. This is what generates this session member:

Code Snippet

CREATE SESSION

MEMBER [Adventure Works].[Date].[Fiscal].[{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Hier33MultiFilter__] AS

'

AGGREGATE({

[Date].[Fiscal].[Date].&[1],

[Date].[Fiscal].[Date].&[2]

})

'

The AGGREGATE function is causing you to select the first member of the set. If you change the members to &[2] and &[3], you'll get the value for just July 2, 2001. If you replace AGGREGATE with SUM, you'll get the right result but that's not really an option here.

I can't really say why the AGGREGATE function is behaving this way, but would suggest you use the other filtering option in the browser window. If you look just above the "Drop Filters Here" section, you'll see a grid with an entry that looks like "Select Dimension". Apply your filter here.

What his does that is different is that it creates a subcube which limits the available values. This will give you the results you are looking for:

Code Snippet

CREATE SUBCUBE [Adventure Works] AS ( SELECT ( { [Date].[Fiscal].[Date].&[1], [Date].[Fiscal].[Date].&[2] } ) ON COLUMNS FROM [Adventure Works])

Hope that helps,
Bryan

|||

Hi Bryan,

The reason I drag the Fiscal Hierarchy in the "Drop Filters Here" is I want it to act like Excel Pivot Table. When I browser the cube though Excel Pivot Table, it has the same error. Most of our end user use Excel Pivot Table to brower the cube, but AS2005 give the wrong answer for this case.

Thanks

Thanks

|||

I'm at a bit of a loss to explain this beahviour.

One possible work around is to hide the [Date].Fiscal Year] attribute. I find that having [Date].[Fiscal].[Fiscal Year] and [Date].[Fiscal Year] in the same cube just confuses things,especially when using OWC as it is not really mutliple hierarchy and attribute aware.

What your users would need to do then is to drag [Date].[Fiscal] onto the column and filter it in place. If you start from your example that is showing the incorrect figures and drag [Date].[Fiscal Year] off the grid and drag the [Date].[Fiscal] from the filter to the columns does that give you a grid that looks how you want?

|||

I asked a friend of mine on the product team and it is possible that this issue was fixed in the hotfix rollup that was released for SP2 http://support.microsoft.com/kb/939537 I could not find a specific reference, but there are a couple of broader issues which it might come under. It might be worth testing this in a Virtual PC to see if it corrects the issue.

As with all hotfixes, it is only recommended to install this if it does address a particular issue. Otherwise they recommend waiting for the next service pack, but I have not heard when another service pack is likely to be released. It would not surprise me to see the next SP come out after Windows Server 2008 is released (which should be Q1 next year), but this is just a guess on my behalf.

Adventure Works Cube

Hi all,

I try to browser the Adventure Work Cube from the SQL Server Business Intelligence Development Sudio, but it give me the wrong result. Fisrt I have paste the Date.Fiscal hierachy in the Filter Filter and select July 1 2001 and July 2 2001, then I paste the Sales Amount measure in the Data Field. The result is $517,737.44. Now I paste the Date.Fiscal Year attribute in the Row Fields, but the result is change to $503,80592. The result is wrong. The sales amount has been excluded July 2 2001 data. Why this happen?

Thanks,

Thomas,

I've recreated the steps you described. I get the same $517,737.44 value you described. When I drag the Date.[Fiscal Year] attribute hierarchy to the rows, I continue to get $517,737.44.

Do you know what service pack you are working with?

Thanks,
Bryan

|||

Hi Bryan

I am using AS 9.00.3.042. When you filter the day, where you drag the Date.Fiscal hierarchy. If I put the Date.Fiscal hierarchy in the dimension filter panel, then the result is correct. However, if I put the Date.Fiscal hierarchy in the Drop Filter Fields, then the result is not correct.

Thanks,

|||

Sorry, but I'm just not getting the same behavior and I'm using the same SP level. Could you possibly trace the MDX that is submitted for the query (using Profiler) and post that to this thread? That might help determine what's going on.

Thanks,
Bryan

|||

Hi Bryan,

I am using 64bit, do you think that cause the problem. Here is what the MDX looklike:

WITH
SET [Filtered] AS
'
DISTINCT(
{
[Date].[Fiscal].[Date].&[2],
[Date].[Fiscal].[Date].&[1]
}
)
'
SET [Tree] AS
'
HIERARCHIZE(
GENERATE(
[Filtered],
{
ANCESTOR([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Fiscal Year]),
ANCESTOR([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Fiscal Semester]),
ANCESTOR([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Fiscal Quarter]),
ANCESTOR([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Month]),
[Date].[Fiscal].CURRENTMEMBER
}
)
)
'
SELECT [Tree] ON Columns FROM [Adventure Works]

<PropertyList xmlns="urnchemas-microsoft-com:xml-analysis" xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>Adventure Works DW</Catalog><Timeout>3600</Timeout><Content>SchemaData</Content><Format>Multidimensional</Format><AxisFormat>TupleFormat</AxisFormat><ShowHiddenCubes>true</ShowHiddenCubes><DbpropMsmdFlattened2>false</DbpropMsmdFlattened2><SspropInitAppName>Microsoft SQL Server Management Studio</SspropInitAppName><SafetyOptions>2</SafetyOptions><Dialect>MDX</Dialect><ClientProcessID>3696</ClientProcessID><LocaleIdentifier>1033</LocaleIdentifier></PropertyList>

CREATE SESSION
MEMBER [Adventure Works].[Date].[Fiscal].[{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Hier33MultiFilter__] AS
'
AGGREGATE({
[Date].[Fiscal].[Date].&[1],
[Date].[Fiscal].[Date].&[2]
})
'

<PropertyList xmlns="urnchemas-microsoft-com:xml-analysis" xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>Adventure Works DW</Catalog><Timeout>3600</Timeout><Content>None</Content><Format>Tabular</Format><ShowHiddenCubes>true</ShowHiddenCubes><DbpropMsmdFlattened2>false</DbpropMsmdFlattened2><SspropInitAppName>Microsoft SQL Server Management Studio</SspropInitAppName><SafetyOptions>2</SafetyOptions><Dialect>MDX</Dialect><ClientProcessID>3696</ClientProcessID><LocaleIdentifier>1033</LocaleIdentifier></PropertyList>

SELECT
NON EMPTY [{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Axis1Set0]
DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS,
{
[Measures].[Sales Amount]
}
ON ROWS
FROM [Adventure Works]
WHERE
(
[{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Hier33MultiFilter__]
)
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR

<PropertyList xmlns="urnchemas-microsoft-com:xml-analysis" xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>Adventure Works DW</Catalog><Timeout>3600</Timeout><Format>Native</Format><ShowHiddenCubes>true</ShowHiddenCubes><DbpropMsmdFlattened2>false</DbpropMsmdFlattened2><SspropInitAppName>Microsoft SQL Server Management Studio</SspropInitAppName><SafetyOptions>2</SafetyOptions><Dialect>MDX</Dialect><ClientProcessID>3696</ClientProcessID><LocaleIdentifier>1033</LocaleIdentifier></PropertyList>

Thanks,

|||

Hi Bryan,

Here is the result:

Date.Fiscal

(Multiple Items)

Fiscal Year Sales Amount

FY2002 $503,805.92

Grand Total $503,805.92

|||

I'm not seeing the set definition for [{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Axis1Set0].

B.

|||

Sorry Bryan,

I forgot to include this:

CREATE SESSION
SET [Adventure Works].[{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Axis1Set0] AS
'
{
{ [Date].[Fiscal Year].[All Periods] },
AddCalculatedMembers([Date].[Fiscal Year].[Fiscal Year].MEMBERS)
}
'

|||

Here's what I've been able to figure out.

You're dropping the Fiscal Hierarchy on to the "Drop Filters Here" section of the interface. This is what generates this session member:

Code Snippet

CREATE SESSION

MEMBER [Adventure Works].[Date].[Fiscal].[{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Hier33MultiFilter__] AS

'

AGGREGATE({

[Date].[Fiscal].[Date].&[1],

[Date].[Fiscal].[Date].&[2]

})

'

The AGGREGATE function is causing you to select the first member of the set. If you change the members to &[2] and &[3], you'll get the value for just July 2, 2001. If you replace AGGREGATE with SUM, you'll get the right result but that's not really an option here.

I can't really say why the AGGREGATE function is behaving this way, but would suggest you use the other filtering option in the browser window. If you look just above the "Drop Filters Here" section, you'll see a grid with an entry that looks like "Select Dimension". Apply your filter here.

What his does that is different is that it creates a subcube which limits the available values. This will give you the results you are looking for:

Code Snippet

CREATE SUBCUBE [Adventure Works] AS ( SELECT ( { [Date].[Fiscal].[Date].&[1], [Date].[Fiscal].[Date].&[2] } ) ON COLUMNS FROM [Adventure Works])

Hope that helps,
Bryan

|||

Hi Bryan,

The reason I drag the Fiscal Hierarchy in the "Drop Filters Here" is I want it to act like Excel Pivot Table. When I browser the cube though Excel Pivot Table, it has the same error. Most of our end user use Excel Pivot Table to brower the cube, but AS2005 give the wrong answer for this case.

Thanks

Thanks

|||

I'm at a bit of a loss to explain this beahviour.

One possible work around is to hide the [Date].Fiscal Year] attribute. I find that having [Date].[Fiscal].[Fiscal Year] and [Date].[Fiscal Year] in the same cube just confuses things,especially when using OWC as it is not really mutliple hierarchy and attribute aware.

What your users would need to do then is to drag [Date].[Fiscal] onto the column and filter it in place. If you start from your example that is showing the incorrect figures and drag [Date].[Fiscal Year] off the grid and drag the [Date].[Fiscal] from the filter to the columns does that give you a grid that looks how you want?

|||

I asked a friend of mine on the product team and it is possible that this issue was fixed in the hotfix rollup that was released for SP2 http://support.microsoft.com/kb/939537 I could not find a specific reference, but there are a couple of broader issues which it might come under. It might be worth testing this in a Virtual PC to see if it corrects the issue.

As with all hotfixes, it is only recommended to install this if it does address a particular issue. Otherwise they recommend waiting for the next service pack, but I have not heard when another service pack is likely to be released. It would not surprise me to see the next SP come out after Windows Server 2008 is released (which should be Q1 next year), but this is just a guess on my behalf.

Advanced SQL - Group By With Cube

Need some direction on getting these example results:

I need to group by 2-3 different attributes, one of them a calculated value off of a birthdate attribute, but I want to know whether they are over or under a certain age. I don't want to restrict the result set by age, but rather subgroup results by age ranges.

Simplified Ex:
SELECT class, time, count(nameID) as Total
from students
group by class, time
with cube

Ex RS:
class time >15 Total
-- -- -- --
Math 9:30 true 17
Math 9:30 false 8
PE 7:45 true 5
PE 7:45 false 10

Hopefully you get the idea. I can not figure out how to get the evaluated boolean of whether or not thier age is >15.

It is important that result set be a cube to calculate all the total and subgroup totals. I don't even know if this is possible in my case though.

All help greatly appreciated.

SlezakFirst, assuming you are using SQL Server 2000, you need to create a UDF which will calculate the age. You can find one here: http://www.databasejournal.com/img/UDF_GetAge.sql

Next, you can make use of this function in your query:

SELECT class, time, CASE WHEN dbo.GetAge(DateOfBirth,GETDATE()) > 15 THEN 'True' ELSE 'False' END AS '>15', count(nameID) as Total
from students
group by class, time, dbo.GetAge(DateOfBirth,GETDATE())
with cube

Terri|||That was pretty much what I wanted. Thank you greatly!

I did want to group by 'CASE WHEN dbo.GetAge(DateOfBirth,GETDATE()) > 15 THEN 'True' ELSE 'False' END' and not by 'dbo.GetAge(DateOfBirth,GETDATE())', but I was able to figure that out. :p

Much obliged, and I appreciate that GetAge function too.

Slezak

AdomdConnection.Cube() Can''t Find Some Cubes In SQL Server2005

I have established some cubes in SQL Server 2005.

But when I use AdomdConnection.Cube() Function to find these cubes, some of these can't be find.

Can someone tell me what reason may be or what I can do ? Thanks!!!

Program: .NET 2005

Server : SQL Server 2005

OS : Windows 2003

P.S. In these Cubes Can't be found, I delete few cubes and establish them again. Then these cubes can be found.

Hi,

The best way is to use a loop to list out all the cubes and check if all of them occur.

Say,

For each oCube in ODatabases.Cubes

Msgbox(oCube.Name)

Next

If all donot occur it might be an issue related to security. Check you have Database wide Privilege.

Thanks

Subhash Subramanyam

|||

Hi~

I use your way and list all the cubes, but some cubes still can not be listed.

And I'm sure I have the database wide privilege.

AdomdConnection.Cube() Can''t Find Some Cubes In SQL Server2005

I have established some cubes in SQL Server 2005.

But when I use AdomdConnection.Cube() Function to find these cubes, some of these can't be find.

Can someone tell me what reason may be or what I can do ? Thanks!!!

Program: .NET 2005

Server : SQL Server 2005

OS : Windows 2003

P.S. In these Cubes Can't be found, I delete few cubes and establish them again. Then these cubes can be found.

Hi,

The best way is to use a loop to list out all the cubes and check if all of them occur.

Say,

For each oCube in ODatabases.Cubes

Msgbox(oCube.Name)

Next

If all donot occur it might be an issue related to security. Check you have Database wide Privilege.

Thanks

Subhash Subramanyam

|||

Hi~

I use your way and list all the cubes, but some cubes still can not be listed.

And I'm sure I have the database wide privilege.

AdomdConnection.Cube() Can''t Find Some Cubes In SQL Server2005

I have established some cubes in SQL Server 2005.

But when I use AdomdConnection.Cube() Function to find these cubes, some of these can't be find.

Can someone tell me what reason may be or what I can do ? Thanks!!!

Program: .NET 2005

Server : SQL Server 2005

OS : Windows 2003

P.S. In these Cubes Can't be found, I delete few cubes and establish them again. Then these cubes can be found.

Hi,

The best way is to use a loop to list out all the cubes and check if all of them occur.

Say,

For each oCube in ODatabases.Cubes

Msgbox(oCube.Name)

Next

If all donot occur it might be an issue related to security. Check you have Database wide Privilege.

Thanks

Subhash Subramanyam

|||

Hi~

I use your way and list all the cubes, but some cubes still can not be listed.

And I'm sure I have the database wide privilege.

ADOMD.net Can't get the KPI.ID or Cube.ID

When you open an AS project, you will see every object has an ID property, just like KPI.ID and cube.ID and Dimention.ID. And that's diffrent of the Name property. You could chanage the name property to everything you like, but when you created an object, you could not change it's ID.

When i use the ADOMD.NET, I could do this:

Dim myKPIConnection As AdomdConnection
Dim myCubeDef As CubeDef
Dim k As Kpi

myConnectionString = "Data Source=" + myOlapServer + ";Catalog=" + myOlapDatabase + ";Provider=MSOLAP;"

myKPIConnection = New AdomdConnection(myConnectionString)
myKPIConnection.Open()

For i = 0 To myKPIConnection.Cubes.Count - 1

If myKPIConnection.Cubes(i).Type = CubeType.Cube Then

myCubeDef = myKPIConnection.Cubes(i)

k = myKPIConnection.Cubes(i).Kpis(0)

MessageBox.Show(k.Name)

MessageBox.Show(k.Caption)

end if
next

But the question is, there's no k.ID or cubes(i).ID property there!

The k.Name and k.Caption both refer to the kpi's Name property in the AS server in factly.

Is that means we could not use the object 's ID property in the ADOMD.NET?

Another question, in MDX, we could only refer a dimention by it's name, and could not by it's ID, right?

ivanchain wrote:

Is that means we could not use the object 's ID property in the ADOMD.NET?

Another question, in MDX, we could only refer a dimention by it's name, and could not by it's ID, right?

That's right. In ADOMD.NET and MDX all references are done by name. The ID property is mainly used by the administrative APIs

ADOMD.net 9.0 (client) and CubeDef.LastProcessed

What is this property (CubeDef.LastProcessed) supposed to return as it does not return the date the cube was last processed?

Does AMO provide this info more reliably ?

TIA
Dave

Hi Dave,

It's a bug - CubeDef.LastProcessed returns the last time the server was restarted as far as I know. See
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124606

...for more details. Take a look at the comment posted by furmangg on April 28th on this entry on my blog for some code which does what you want:
http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!675.entry

It's also available in the Analysis Services Stored Procedure Project here:
http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures&title=CubeInfo

HTH,

Chris

|||

Thanks Chris,

Its not resolved in SP1 either... Im going to check out AMO.

Cheers
Dave

|||FWIW AMO works for AS2005 when retrieving the last processed datetime. Doesnt work for AS2000 or local cubes.

ADOMD.net 9.0 (client) and CubeDef.LastProcessed

What is this property (CubeDef.LastProcessed) supposed to return as it does not return the date the cube was last processed?

Does AMO provide this info more reliably ?

TIA
Dave

Hi Dave,

It's a bug - CubeDef.LastProcessed returns the last time the server was restarted as far as I know. See
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124606

...for more details. Take a look at the comment posted by furmangg on April 28th on this entry on my blog for some code which does what you want:
http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!675.entry

It's also available in the Analysis Services Stored Procedure Project here:
http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures&title=CubeInfo

HTH,

Chris

|||

Thanks Chris,

Its not resolved in SP1 either... Im going to check out AMO.

Cheers
Dave

|||FWIW AMO works for AS2005 when retrieving the last processed datetime. Doesnt work for AS2000 or local cubes.

ADODB - Cube connection error: Provider could not set DATASOURCE, USERID, or PASSWORD property

When attempting to connect to my cube from the same server, I receive this error:
"Provider could not set DATASOURCE, USERID, or PASSWORD property"

The connection works just fine from my machine, but when going from the server to the server, I profile it, and no connection attempts are made. The server is w2k3 SE sp1, and the successful client is win xp pro sp2.

The method I'm using is an adodb ConnectionClass in C# vs.net 2003. The adodb is using v2.8, and I used the component checker to make sure the server has 2.8 - it does.
I have the user name and password in the connection string,
"Provider=MSOLAP.3;Cache Authentication=True;Persist Security Info=False;Initial Catalog=thecatalog;Data Source=theServerName;"
calling the open like: cn.Open(ConnectionString, "theuser", "thepass", 0);
and if it successfully opens, then I make use of an adomd.cellset.

I'm currently checking component differences between the 2 machines, but does anyone have any specific things to try or specific components to check?

First I would make sure you can establish OLED connection from one machine to another.

You can find here some clues on how to troubleshoot connection problems: http://www.sqljunkies.com/WebLog/edwardm/archive/2006/05/26/21447.aspx

Second, as you already suggested, I would check configuration of components.

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

ADODB - Cube connection error: Provider could not set DATASOURCE, USERID, or PASSWORD proper

When attempting to connect to my cube from the same server, I receive this error:
"Provider could not set DATASOURCE, USERID, or PASSWORD property"

The connection works just fine from my machine, but when going from the server to the server, I profile it, and no connection attempts are made. The server is w2k3 SE sp1, and the successful client is win xp pro sp2.

The method I'm using is an adodb ConnectionClass in C# vs.net 2003. The adodb is using v2.8, and I used the component checker to make sure the server has 2.8 - it does.
I have the user name and password in the connection string,
"Provider=MSOLAP.3;Cache Authentication=True;Persist Security Info=False;Initial Catalog=thecatalog;Data Source=theServerName;"
calling the open like: cn.Open(ConnectionString, "theuser", "thepass", 0);
and if it successfully opens, then I make use of an adomd.cellset.

I'm currently checking component differences between the 2 machines, but does anyone have any specific things to try or specific components to check?

First I would make sure you can establish OLED connection from one machine to another.

You can find here some clues on how to troubleshoot connection problems: http://www.sqljunkies.com/WebLog/edwardm/archive/2006/05/26/21447.aspx

Second, as you already suggested, I would check configuration of components.

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