Having 2 CALCULATE statements no longer works after installing SP2!!!

I received the following email from a collegue of mine:

I think we may have found an issue with SQL SERVER 2005 SP2

1. Some of the Analysis Services Properties when you click the advanced tab are missing!

2. The MDX scrip behaviour is not the same.

In our cubes we had two CALCULATE statements - first to aggregate the data as normal, then some code to calculate MTD and YTD figures and then another CALCULATE statement. This enabled us to calculate percentages later on in the script without having to sum up all the data at leaf levels first.

With SP2, this no longer works!!! All our percentages are now being added up (like all other measures) which is clearly wrong!

I am not sure how to deal with this. Do we need to inform the clients not to upgrade to SP2 OR can Microsoft resolve this?

Any ideas?

Actually, two CALCULATE statements are the same as one CALCULATE statement w.r.t. aggregating data. The only difference could be if you have unary operators/custom rollups etc. So I suspect the use of two CALCULATE in your scenario was redundant. There is probably something else going on. If you will paste your MDX Script here, perhaps somebody will be able to figure it out.|||Thanks for the reply Mosha. I will get my collegue to post an example.|||

Hello Mosha,

Attached is the MDX Script.

All it does is - FIRST calculates MTD and YTD values

Then calcualtes some percentages.

Prior to Service Pack 2, these percentage calculations displayed correct number when MTD was selected on the FLOW dimension.

Post Service Pack 2, its seems that the percentages are getting calculated first and then the MTD logic is being applied resulting in the addition of all percentages from the lowest level which is not the desired behaviour.

I have tried removing the second calculate statement and it still does not work in sp2.

thanks for your help.

/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;

/* Calculate Values for Flow Dimension */
SCOPE([Flow].[KeyFlow].&[MTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Month],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

SCOPE([Flow].[KeyFlow].&[YTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Year],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

/*Calculate again for the flow calculations to aggregate */
CALCULATE;

/*Scope Total Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[14]);
[Measures].[Dealership Measure Value]= [Measure Type].[Measure Type].[Measure].&[1] + [Measure Type].[Measure Type].[Measure].&[2] + [Measure Type].[Measure Type].[Measure].&[3];
END SCOPE;

/*Scope %Leads From Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[16]);
[Measures].[Dealership Measure Value]=
CASE [Measure Type].[Measure Type].[Measure].&[14]
WHEN 0 THEN null
WHEN null THEN null
ELSE [Measure Type].[Measure Type].[Measure].&[4]/[Measure Type].[Measure Type].[Measure].&[14]
END;
END SCOPE;

|||IN THIs MDX Script, the second CALCULATE does nothing and can be safely removed with either SP2 or SP1 or RTM. The problem is somewhere else. It does look like a regression in SP2 to me, because according to this script, it really should compute percentages after MTD and YTD. I recommend opening a case with product support.|||

Just to add to the issue.

The percentages are not even added up. I am not sure what values it is calculating. We will raise a call with support.

Punita

|||I am having a problem with MTD() and YTD() after installing SP2. I have had to repair alot of queries since we went to SP2. I replaced the MTD and YTD functions with the PeriodsToDate() function and I was back in business. Hope that helps.

Having 2 CALCULATE statements no longer works after installing SP2!!!

I received the following email from a collegue of mine:

I think we may have found an issue with SQL SERVER 2005 SP2

1. Some of the Analysis Services Properties when you click the advanced tab are missing!

2. The MDX scrip behaviour is not the same.

In our cubes we had two CALCULATE statements - first to aggregate the data as normal, then some code to calculate MTD and YTD figures and then another CALCULATE statement. This enabled us to calculate percentages later on in the script without having to sum up all the data at leaf levels first.

With SP2, this no longer works!!! All our percentages are now being added up (like all other measures) which is clearly wrong!

I am not sure how to deal with this. Do we need to inform the clients not to upgrade to SP2 OR can Microsoft resolve this?

Any ideas?

Actually, two CALCULATE statements are the same as one CALCULATE statement w.r.t. aggregating data. The only difference could be if you have unary operators/custom rollups etc. So I suspect the use of two CALCULATE in your scenario was redundant. There is probably something else going on. If you will paste your MDX Script here, perhaps somebody will be able to figure it out.|||Thanks for the reply Mosha. I will get my collegue to post an example.|||

Hello Mosha,

Attached is the MDX Script.

All it does is - FIRST calculates MTD and YTD values

Then calcualtes some percentages.

Prior to Service Pack 2, these percentage calculations displayed correct number when MTD was selected on the FLOW dimension.

Post Service Pack 2, its seems that the percentages are getting calculated first and then the MTD logic is being applied resulting in the addition of all percentages from the lowest level which is not the desired behaviour.

I have tried removing the second calculate statement and it still does not work in sp2.

thanks for your help.

/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;

/* Calculate Values for Flow Dimension */
SCOPE([Flow].[KeyFlow].&[MTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Month],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

SCOPE([Flow].[KeyFlow].&[YTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Year],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

/*Calculate again for the flow calculations to aggregate */
CALCULATE;

/*Scope Total Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[14]);
[Measures].[Dealership Measure Value]= [Measure Type].[Measure Type].[Measure].&[1] + [Measure Type].[Measure Type].[Measure].&[2] + [Measure Type].[Measure Type].[Measure].&[3];
END SCOPE;

/*Scope %Leads From Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[16]);
[Measures].[Dealership Measure Value]=
CASE [Measure Type].[Measure Type].[Measure].&[14]
WHEN 0 THEN null
WHEN null THEN null
ELSE [Measure Type].[Measure Type].[Measure].&[4]/[Measure Type].[Measure Type].[Measure].&[14]
END;
END SCOPE;

|||IN THIs MDX Script, the second CALCULATE does nothing and can be safely removed with either SP2 or SP1 or RTM. The problem is somewhere else. It does look like a regression in SP2 to me, because according to this script, it really should compute percentages after MTD and YTD. I recommend opening a case with product support.|||

Just to add to the issue.

The percentages are not even added up. I am not sure what values it is calculating. We will raise a call with support.

Punita

|||I am having a problem with MTD() and YTD() after installing SP2. I have had to repair alot of queries since we went to SP2. I replaced the MTD and YTD functions with the PeriodsToDate() function and I was back in business. Hope that helps.

Having 2 CALCULATE statements no longer works after installing SP2!!!

I received the following email from a collegue of mine:

I think we may have found an issue with SQL SERVER 2005 SP2

1. Some of the Analysis Services Properties when you click the advanced tab are missing!

2. The MDX scrip behaviour is not the same.

In our cubes we had two CALCULATE statements - first to aggregate the data as normal, then some code to calculate MTD and YTD figures and then another CALCULATE statement. This enabled us to calculate percentages later on in the script without having to sum up all the data at leaf levels first.

With SP2, this no longer works!!! All our percentages are now being added up (like all other measures) which is clearly wrong!

I am not sure how to deal with this. Do we need to inform the clients not to upgrade to SP2 OR can Microsoft resolve this?

Any ideas?

Actually, two CALCULATE statements are the same as one CALCULATE statement w.r.t. aggregating data. The only difference could be if you have unary operators/custom rollups etc. So I suspect the use of two CALCULATE in your scenario was redundant. There is probably something else going on. If you will paste your MDX Script here, perhaps somebody will be able to figure it out.|||Thanks for the reply Mosha. I will get my collegue to post an example.|||

Hello Mosha,

Attached is the MDX Script.

All it does is - FIRST calculates MTD and YTD values

Then calcualtes some percentages.

Prior to Service Pack 2, these percentage calculations displayed correct number when MTD was selected on the FLOW dimension.

Post Service Pack 2, its seems that the percentages are getting calculated first and then the MTD logic is being applied resulting in the addition of all percentages from the lowest level which is not the desired behaviour.

I have tried removing the second calculate statement and it still does not work in sp2.

thanks for your help.

/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;

/* Calculate Values for Flow Dimension */
SCOPE([Flow].[KeyFlow].&[MTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Month],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

SCOPE([Flow].[KeyFlow].&[YTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Year],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

/*Calculate again for the flow calculations to aggregate */
CALCULATE;

/*Scope Total Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[14]);
[Measures].[Dealership Measure Value]= [Measure Type].[Measure Type].[Measure].&[1] + [Measure Type].[Measure Type].[Measure].&[2] + [Measure Type].[Measure Type].[Measure].&[3];
END SCOPE;

/*Scope %Leads From Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[16]);
[Measures].[Dealership Measure Value]=
CASE [Measure Type].[Measure Type].[Measure].&[14]
WHEN 0 THEN null
WHEN null THEN null
ELSE [Measure Type].[Measure Type].[Measure].&[4]/[Measure Type].[Measure Type].[Measure].&[14]
END;
END SCOPE;

|||IN THIs MDX Script, the second CALCULATE does nothing and can be safely removed with either SP2 or SP1 or RTM. The problem is somewhere else. It does look like a regression in SP2 to me, because according to this script, it really should compute percentages after MTD and YTD. I recommend opening a case with product support.|||

Just to add to the issue.

The percentages are not even added up. I am not sure what values it is calculating. We will raise a call with support.

Punita

|||I am having a problem with MTD() and YTD() after installing SP2. I have had to repair alot of queries since we went to SP2. I replaced the MTD and YTD functions with the PeriodsToDate() function and I was back in business. Hope that helps.

Having 2 CALCULATE statements no longer works after installing SP2!!!

I received the following email from a collegue of mine:

I think we may have found an issue with SQL SERVER 2005 SP2

1. Some of the Analysis Services Properties when you click the advanced tab are missing!

2. The MDX scrip behaviour is not the same.

In our cubes we had two CALCULATE statements - first to aggregate the data as normal, then some code to calculate MTD and YTD figures and then another CALCULATE statement. This enabled us to calculate percentages later on in the script without having to sum up all the data at leaf levels first.

With SP2, this no longer works!!! All our percentages are now being added up (like all other measures) which is clearly wrong!

I am not sure how to deal with this. Do we need to inform the clients not to upgrade to SP2 OR can Microsoft resolve this?

Any ideas?

Actually, two CALCULATE statements are the same as one CALCULATE statement w.r.t. aggregating data. The only difference could be if you have unary operators/custom rollups etc. So I suspect the use of two CALCULATE in your scenario was redundant. There is probably something else going on. If you will paste your MDX Script here, perhaps somebody will be able to figure it out.|||Thanks for the reply Mosha. I will get my collegue to post an example.|||

Hello Mosha,

Attached is the MDX Script.

All it does is - FIRST calculates MTD and YTD values

Then calcualtes some percentages.

Prior to Service Pack 2, these percentage calculations displayed correct number when MTD was selected on the FLOW dimension.

Post Service Pack 2, its seems that the percentages are getting calculated first and then the MTD logic is being applied resulting in the addition of all percentages from the lowest level which is not the desired behaviour.

I have tried removing the second calculate statement and it still does not work in sp2.

thanks for your help.

/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;

/* Calculate Values for Flow Dimension */
SCOPE([Flow].[KeyFlow].&[MTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Month],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

SCOPE([Flow].[KeyFlow].&[YTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Year],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

/*Calculate again for the flow calculations to aggregate */
CALCULATE;

/*Scope Total Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[14]);
[Measures].[Dealership Measure Value]= [Measure Type].[Measure Type].[Measure].&[1] + [Measure Type].[Measure Type].[Measure].&[2] + [Measure Type].[Measure Type].[Measure].&[3];
END SCOPE;

/*Scope %Leads From Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[16]);
[Measures].[Dealership Measure Value]=
CASE [Measure Type].[Measure Type].[Measure].&[14]
WHEN 0 THEN null
WHEN null THEN null
ELSE [Measure Type].[Measure Type].[Measure].&[4]/[Measure Type].[Measure Type].[Measure].&[14]
END;
END SCOPE;

|||IN THIs MDX Script, the second CALCULATE does nothing and can be safely removed with either SP2 or SP1 or RTM. The problem is somewhere else. It does look like a regression in SP2 to me, because according to this script, it really should compute percentages after MTD and YTD. I recommend opening a case with product support.|||

Just to add to the issue.

The percentages are not even added up. I am not sure what values it is calculating. We will raise a call with support.

Punita

|||I am having a problem with MTD() and YTD() after installing SP2. I have had to repair alot of queries since we went to SP2. I replaced the MTD and YTD functions with the PeriodsToDate() function and I was back in business. Hope that helps.

Having 2 CALCULATE statements no longer works after installing SP2!!!

I received the following email from a collegue of mine:

I think we may have found an issue with SQL SERVER 2005 SP2

1. Some of the Analysis Services Properties when you click the advanced tab are missing!

2. The MDX scrip behaviour is not the same.

In our cubes we had two CALCULATE statements - first to aggregate the data as normal, then some code to calculate MTD and YTD figures and then another CALCULATE statement. This enabled us to calculate percentages later on in the script without having to sum up all the data at leaf levels first.

With SP2, this no longer works!!! All our percentages are now being added up (like all other measures) which is clearly wrong!

I am not sure how to deal with this. Do we need to inform the clients not to upgrade to SP2 OR can Microsoft resolve this?

Any ideas?

Actually, two CALCULATE statements are the same as one CALCULATE statement w.r.t. aggregating data. The only difference could be if you have unary operators/custom rollups etc. So I suspect the use of two CALCULATE in your scenario was redundant. There is probably something else going on. If you will paste your MDX Script here, perhaps somebody will be able to figure it out.|||Thanks for the reply Mosha. I will get my collegue to post an example.|||

Hello Mosha,

Attached is the MDX Script.

All it does is - FIRST calculates MTD and YTD values

Then calcualtes some percentages.

Prior to Service Pack 2, these percentage calculations displayed correct number when MTD was selected on the FLOW dimension.

Post Service Pack 2, its seems that the percentages are getting calculated first and then the MTD logic is being applied resulting in the addition of all percentages from the lowest level which is not the desired behaviour.

I have tried removing the second calculate statement and it still does not work in sp2.

thanks for your help.

/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;

/* Calculate Values for Flow Dimension */
SCOPE([Flow].[KeyFlow].&[MTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Month],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

SCOPE([Flow].[KeyFlow].&[YTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Year],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

/*Calculate again for the flow calculations to aggregate */
CALCULATE;

/*Scope Total Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[14]);
[Measures].[Dealership Measure Value]= [Measure Type].[Measure Type].[Measure].&[1] + [Measure Type].[Measure Type].[Measure].&[2] + [Measure Type].[Measure Type].[Measure].&[3];
END SCOPE;

/*Scope %Leads From Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[16]);
[Measures].[Dealership Measure Value]=
CASE [Measure Type].[Measure Type].[Measure].&[14]
WHEN 0 THEN null
WHEN null THEN null
ELSE [Measure Type].[Measure Type].[Measure].&[4]/[Measure Type].[Measure Type].[Measure].&[14]
END;
END SCOPE;

|||IN THIs MDX Script, the second CALCULATE does nothing and can be safely removed with either SP2 or SP1 or RTM. The problem is somewhere else. It does look like a regression in SP2 to me, because according to this script, it really should compute percentages after MTD and YTD. I recommend opening a case with product support.|||

Just to add to the issue.

The percentages are not even added up. I am not sure what values it is calculating. We will raise a call with support.

Punita

|||I am having a problem with MTD() and YTD() after installing SP2. I have had to repair alot of queries since we went to SP2. I replaced the MTD and YTD functions with the PeriodsToDate() function and I was back in business. Hope that helps.sql

have a problem to browse the data in analysis server

:mad: Dear Sir/Madam,

I have a problem to browse the data in analysis server, it displayed: unspecified error, my os is windows xp with sp1 and sql server2000 installed, Do i need to install AS SP3 on the server, but, what are AS SP3 stand for, any other solution?

Rgds,
chenwhat are AS SP3 stand for, any other solution?AS SP3 would be Analysis Services, Service Pack 3 (http://www.microsoft.com/sql/downloads/2000/sp3.asp)

-PatP

Hardware Specifications

Is there any documented

hardware specification for Analysis Services 2005/2000? This is one question asked frequently to me by people implementing AS 2005. I could not find any documents on this. It would be great if any recommendations are available on this.

Thanks,

S Suresh

Such information is often provided by hardware vendors.

For instance;
http://h18004.www1.hp.com/products/servers/software/microsoft/sqlserver2005.html?jumpid=reg_R1002_USEN
www.dell.com/sql

You can also take a look at the existing case studies like Project REAL http://www.microsoft.com/sql/solutions/bi/projectreal.mspx
see what are the data sizes and what hardware used.

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

Hang while trying to browse thr cube

I am working on the Analysis Services

When I am in Business Intelligence Studio and I right-click on the

cube and I choose browse to open the cube-browser, the Cube-Browser

HANGS FOREVER. it never finishes opening. I always end up using the

taskmgr to kill off Business Intelligent Studio. How is this HANGING

problem fixed?

Please make sure whether cube was processed sucesfully before you are browsing it. and also you have sificient permissions to browse the cube.

Thanks

|||Hello,

I have the same problems.
We have a central MSSQL 2005 (OS: Windows 2000) instance and several clients without a local MSSQL Installation. The people create cubes and there exist 2 problems:
- When the user wants to create/deploy the cube on the central instance he needs administrator rights on the base directory of the mssql installation. (I gave the user that right but it is not the way i want to fix the problem)
- It is not possible that a user can browse a cube. I tested the same process on a machine without any firewall (cleint side) and it works. This machine was not in the same network like the other clients. I tested it again and turned off the windows firewall on the clients in the same network, but nothing changed.

Could anybody help me?

Tim

Handling Data Integrity Issues in SQL2000

Hello,
I’m looking for some help with analysis services.
I ahve a very simple fact table which has 2 columns
Account Number and Investment Objective.
Fact table
Account Number Investment Objective
12345678 A
22222222 A
33333333 B
44444444 X
A dimension is needed for the investment objective
So I have a lookup table which is
Value Description
A Growth
B No-Growth
There is not an X value in the lookup table so when I look at the count for
accounts I only get 3. The account 44444444 never shows up.
Is there a way to have 44444444 or any other account that might get a value
not in the lookup table to fall into an ‘Unknown’ type description?
I understand the best way to solve is to make sure I have a value in the
lookup table for every value that is in the investment objective fact, the
problem is I cant control what might get added to it, and we want to be able
to have an unknown description and have everything that falls out of the
range of the lookup go into that. This will allow the users of the cube to
find the bad entries and fix them.
Of course this is sample data and the real tables have millions of records
and 100’s of columns, but I think the basic concept applies.
Any help or a direction to go in would be greatly appreciated. BTW this is
SQL2000
ThanksHello AppDev,
I am assuming you are using a Star schema in a SQL Server database
somewhere. The Star Schema is used as a basis for the OLAP cube you
are creating.
Remember Analysis Services requires all dimension records to be unique
and fact records to have a relationship with all dimensions. This means
Fact records that do not have a correlating dimension record will not
appear in your cube. (This only happens when you included dimensions
that do not have a relationship with all fact records)
There is a quick solution to this. Create an 'Unknown' record in
your dimension table. When populating the fact table you can return the
unknown surrogate key value to join to the unknown dimension record.
This will make unknown records appear in your cube.
Hope this helps
Myles Matheson
Data Warehouse Architect|||(microsoft.public.sqlserver.olap is a better newsgroup for a posting like
this, but here goes)
My first gut feel is that you should not be allowing this to occur. This is
basic RI between a fact table and the dimension. You should be processing
your dimension to pickup additions prior to processing the fact table. This
will ensure that this doesn't occur if RI is in-place on the RDBMS.
The row is disappearing from the fact table because the default SQL
statement is an inner join between the fact table and the dimension table.
Thus the row will not be returned to Analysis Services at all . . . we
simply don't see it. The RDBMS eliminates it before we get it.
In SQL2K, you best option is to create an UNKNOWN member in the base
dimension and then load your fact data through a view. In the view use a
CASE clause with an EXISTS and replace the FK being returned based on
whether or not that key exists. If it doesn't exist, then return the UNKNOWN
member.
In SQL2K5, the system supports an unknown member directly and you can load
data w/ an error configuration which tells it to assign invalid FKs with the
system generated unknown member directly.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"appdevtech" <appdevtech@.online.nospam> wrote in message
news:44BBA514-66F1-4FDD-A0D2-11E6BB6D2F13@.microsoft.com...
> Hello,
> I'm looking for some help with analysis services.
> I ahve a very simple fact table which has 2 columns
> Account Number and Investment Objective.
> Fact table
> Account Number Investment Objective
> 12345678 A
> 22222222 A
> 33333333 B
> 44444444 X
> A dimension is needed for the investment objective
> So I have a lookup table which is
> Value Description
> A Growth
> B No-Growth
> There is not an X value in the lookup table so when I look at the count
> for
> accounts I only get 3. The account 44444444 never shows up.
> Is there a way to have 44444444 or any other account that might get a
> value
> not in the lookup table to fall into an 'Unknown' type description?
> I understand the best way to solve is to make sure I have a value in the
> lookup table for every value that is in the investment objective fact, the
> problem is I cant control what might get added to it, and we want to be
> able
> to have an unknown description and have everything that falls out of the
> range of the lookup go into that. This will allow the users of the cube
> to
> find the bad entries and fix them.
> Of course this is sample data and the real tables have millions of records
> and 100's of columns, but I think the basic concept applies.
> Any help or a direction to go in would be greatly appreciated. BTW this
> is
> SQL2000
> Thanks
>|||Thanks Dave,
My gut feeling is to fix the RI issues first also, but in this case I can
use the cube to identify the issues, versus catching the data during import
then writing a report to notify the users of the inconsistent data.
When you say "create an UNKNOWN member in the base dimension” do you mean
adding an additional record to my dimension table?
Value Description
A Growth
B No-Growth
X Unknown
Then in the view if it does not exist, return X(Unknown)?
"Dave Wickert [MSFT]" wrote:

> (microsoft.public.sqlserver.olap is a better newsgroup for a posting like
> this, but here goes)
> My first gut feel is that you should not be allowing this to occur. This i
s
> basic RI between a fact table and the dimension. You should be processing
> your dimension to pickup additions prior to processing the fact table. Thi
s
> will ensure that this doesn't occur if RI is in-place on the RDBMS.
> The row is disappearing from the fact table because the default SQL
> statement is an inner join between the fact table and the dimension table.
> Thus the row will not be returned to Analysis Services at all . . . we
> simply don't see it. The RDBMS eliminates it before we get it.
> In SQL2K, you best option is to create an UNKNOWN member in the base
> dimension and then load your fact data through a view. In the view use a
> CASE clause with an EXISTS and replace the FK being returned based on
> whether or not that key exists. If it doesn't exist, then return the UNKNO
WN
> member.
> In SQL2K5, the system supports an unknown member directly and you can load
> data w/ an error configuration which tells it to assign invalid FKs with t
he
> system generated unknown member directly.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI SystemsTeam
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "appdevtech" <appdevtech@.online.nospam> wrote in message
> news:44BBA514-66F1-4FDD-A0D2-11E6BB6D2F13@.microsoft.com...
>
>|||Hello Myles,
For example, you could add a new record in the dimension table with
"value"="unknown". You could create a view fact1 and use this as the fact
table when you create a cube
create view fact1 as
select f.AccountNumber,
case when exists (select d.value from dim1 d where d.value=f.objectvalue )
then f.[Investment Objective]
else 'unknown'
end
as InvestmentObjective,
f.number
from fact f
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
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.
| Thread-Topic: Handling Data Integrity Issues in SQL2000
| thread-index: AcVzZJNkPr6fSXC8RMqPvlgseXtMiA==
| X-WBNR-Posting-Host: 12.155.246.10
| From: "examnotes" <appdevtech@.online.nospam>
| References: <44BBA514-66F1-4FDD-A0D2-11E6BB6D2F13@.microsoft.com>
<#BtbY5rcFHA.1324@.tk2msftngp13.phx.gbl>
| Subject: Re: Handling Data Integrity Issues in SQL2000
| Date: Fri, 17 Jun 2005 10:47:05 -0700
| Lines: 98
| Message-ID: <6CC7E76E-3E1C-49BE-B976-71D0D694B5AB@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 8bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.datawarehouse
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.datawarehouse:1828
| X-Tomcat-NG: microsoft.public.sqlserver.datawarehouse
|
| Thanks Dave,
| My gut feeling is to fix the RI issues first also, but in this case I can
| use the cube to identify the issues, versus catching the data during
import
| then writing a report to notify the users of the inconsistent data.
|
| When you say "create an UNKNOWN member in the base dimension” do you
mean
| adding an additional record to my dimension table?
|
| Value Description
| A Growth
| B No-Growth
| X Unknown
|
| Then in the view if it does not exist, return X(Unknown)?
|
| "Dave Wickert [MSFT]" wrote:
|
| > (microsoft.public.sqlserver.olap is a better newsgroup for a posting
like
| > this, but here goes)
| >
| > My first gut feel is that you should not be allowing this to occur.
This is
| > basic RI between a fact table and the dimension. You should be
processing
| > your dimension to pickup additions prior to processing the fact table.
This
| > will ensure that this doesn't occur if RI is in-place on the RDBMS.
| >
| > The row is disappearing from the fact table because the default SQL
| > statement is an inner join between the fact table and the dimension
table.
| > Thus the row will not be returned to Analysis Services at all . . . we
| > simply don't see it. The RDBMS eliminates it before we get it.
| >
| > In SQL2K, you best option is to create an UNKNOWN member in the base
| > dimension and then load your fact data through a view. In the view use
a
| > CASE clause with an EXISTS and replace the FK being returned based on
| > whether or not that key exists. If it doesn't exist, then return the
UNKNOWN
| > member.
| >
| > In SQL2K5, the system supports an unknown member directly and you can
load
| > data w/ an error configuration which tells it to assign invalid FKs
with the
| > system generated unknown member directly.
| > --
| > Dave Wickert [MSFT]
| > dwickert@.online.microsoft.com
| > Program Manager
| > BI SystemsTeam
| > SQL BI Product Unit (Analysis Services)
| > --
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| >
| > "appdevtech" <appdevtech@.online.nospam> wrote in message
| > news:44BBA514-66F1-4FDD-A0D2-11E6BB6D2F13@.microsoft.com...
| > > Hello,
| > > I'm looking for some help with analysis services.
| > >
| > > I ahve a very simple fact table which has 2 columns
| > > Account Number and Investment Objective.
| > >
| > > Fact table
| > > Account Number Investment Objective
| > > 12345678 A
| > > 22222222 A
| > > 33333333 B
| > > 44444444 X
| > >
| > > A dimension is needed for the investment objective
| > > So I have a lookup table which is
| > >
| > > Value Description
| > > A Growth
| > > B No-Growth
| > >
| > > There is not an X value in the lookup table so when I look at the
count
| > > for
| > > accounts I only get 3. The account 44444444 never shows up.
| > > Is there a way to have 44444444 or any other account that might get a
| > > value
| > > not in the lookup table to fall into an 'Unknown' type description?
| > >
| > > I understand the best way to solve is to make sure I have a value in
the
| > > lookup table for every value that is in the investment objective
fact, the
| > > problem is I cant control what might get added to it, and we want to
be
| > > able
| > > to have an unknown description and have everything that falls out of
the
| > > range of the lookup go into that. This will allow the users of the
cube
| > > to
| > > find the bad entries and fix them.
| > >
| > > Of course this is sample data and the real tables have millions of
records
| > > and 100's of columns, but I think the basic concept applies.
| > >
| > > Any help or a direction to go in would be greatly appreciated. BTW
this
| > > is
| > > SQL2000
| > > Thanks
| > >
| >
| >
| >
|

Handling Data Integrity Issues in SQL2000

Hello,
I’m looking for some help with analysis services.
I ahve a very simple fact table which has 2 columns
Account Number and Investment Objective.
Fact table
Account NumberInvestment Objective
12345678A
22222222A
33333333B
44444444X
A dimension is needed for the investment objective
So I have a lookup table which is
ValueDescription
AGrowth
BNo-Growth
There is not an X value in the lookup table so when I look at the count for
accounts I only get 3. The account 44444444 never shows up.
Is there a way to have 44444444 or any other account that might get a value
not in the lookup table to fall into an ‘Unknown’ type description?
I understand the best way to solve is to make sure I have a value in the
lookup table for every value that is in the investment objective fact, the
problem is I cant control what might get added to it, and we want to be able
to have an unknown description and have everything that falls out of the
range of the lookup go into that. This will allow the users of the cube to
find the bad entries and fix them.
Of course this is sample data and the real tables have millions of records
and 100’s of columns, but I think the basic concept applies.
Any help or a direction to go in would be greatly appreciated. BTW this is
SQL2000
Thanks
Hello AppDev,
I am assuming you are using a Star schema in a SQL Server database
somewhere. The Star Schema is used as a basis for the OLAP cube you
are creating.
Remember Analysis Services requires all dimension records to be unique
and fact records to have a relationship with all dimensions. This means
Fact records that do not have a correlating dimension record will not
appear in your cube. (This only happens when you included dimensions
that do not have a relationship with all fact records)
There is a quick solution to this. Create an 'Unknown' record in
your dimension table. When populating the fact table you can return the
unknown surrogate key value to join to the unknown dimension record.
This will make unknown records appear in your cube.
Hope this helps
Myles Matheson
Data Warehouse Architect
|||(microsoft.public.sqlserver.olap is a better newsgroup for a posting like
this, but here goes)
My first gut feel is that you should not be allowing this to occur. This is
basic RI between a fact table and the dimension. You should be processing
your dimension to pickup additions prior to processing the fact table. This
will ensure that this doesn't occur if RI is in-place on the RDBMS.
The row is disappearing from the fact table because the default SQL
statement is an inner join between the fact table and the dimension table.
Thus the row will not be returned to Analysis Services at all . . . we
simply don't see it. The RDBMS eliminates it before we get it.
In SQL2K, you best option is to create an UNKNOWN member in the base
dimension and then load your fact data through a view. In the view use a
CASE clause with an EXISTS and replace the FK being returned based on
whether or not that key exists. If it doesn't exist, then return the UNKNOWN
member.
In SQL2K5, the system supports an unknown member directly and you can load
data w/ an error configuration which tells it to assign invalid FKs with the
system generated unknown member directly.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"appdevtech" <appdevtech@.online.nospam> wrote in message
news:44BBA514-66F1-4FDD-A0D2-11E6BB6D2F13@.microsoft.com...
> Hello,
> I'm looking for some help with analysis services.
> I ahve a very simple fact table which has 2 columns
> Account Number and Investment Objective.
> Fact table
> Account Number Investment Objective
> 12345678 A
> 22222222 A
> 33333333 B
> 44444444 X
> A dimension is needed for the investment objective
> So I have a lookup table which is
> Value Description
> A Growth
> B No-Growth
> There is not an X value in the lookup table so when I look at the count
> for
> accounts I only get 3. The account 44444444 never shows up.
> Is there a way to have 44444444 or any other account that might get a
> value
> not in the lookup table to fall into an 'Unknown' type description?
> I understand the best way to solve is to make sure I have a value in the
> lookup table for every value that is in the investment objective fact, the
> problem is I cant control what might get added to it, and we want to be
> able
> to have an unknown description and have everything that falls out of the
> range of the lookup go into that. This will allow the users of the cube
> to
> find the bad entries and fix them.
> Of course this is sample data and the real tables have millions of records
> and 100's of columns, but I think the basic concept applies.
> Any help or a direction to go in would be greatly appreciated. BTW this
> is
> SQL2000
> Thanks
>
|||Thanks Dave,
My gut feeling is to fix the RI issues first also, but in this case I can
use the cube to identify the issues, versus catching the data during import
then writing a report to notify the users of the inconsistent data.
When you say "create an UNKNOWN member in the base dimension” do you mean
adding an additional record to my dimension table?
Value Description
A Growth
B No-Growth
X Unknown
Then in the view if it does not exist, return X(Unknown)?
"Dave Wickert [MSFT]" wrote:

> (microsoft.public.sqlserver.olap is a better newsgroup for a posting like
> this, but here goes)
> My first gut feel is that you should not be allowing this to occur. This is
> basic RI between a fact table and the dimension. You should be processing
> your dimension to pickup additions prior to processing the fact table. This
> will ensure that this doesn't occur if RI is in-place on the RDBMS.
> The row is disappearing from the fact table because the default SQL
> statement is an inner join between the fact table and the dimension table.
> Thus the row will not be returned to Analysis Services at all . . . we
> simply don't see it. The RDBMS eliminates it before we get it.
> In SQL2K, you best option is to create an UNKNOWN member in the base
> dimension and then load your fact data through a view. In the view use a
> CASE clause with an EXISTS and replace the FK being returned based on
> whether or not that key exists. If it doesn't exist, then return the UNKNOWN
> member.
> In SQL2K5, the system supports an unknown member directly and you can load
> data w/ an error configuration which tells it to assign invalid FKs with the
> system generated unknown member directly.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI SystemsTeam
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "appdevtech" <appdevtech@.online.nospam> wrote in message
> news:44BBA514-66F1-4FDD-A0D2-11E6BB6D2F13@.microsoft.com...
>
>
|||Hello Myles,
For example, you could add a new record in the dimension table with
"value"="unknown". You could create a view fact1 and use this as the fact
table when you create a cube
create view fact1 as
select f.AccountNumber,
case when exists (select d.value from dim1 d where d.value=f.objectvalue )
then f.[Investment Objective]
else 'unknown'
end
as InvestmentObjective,
f.number
from fact f
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
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.
| Thread-Topic: Handling Data Integrity Issues in SQL2000
| thread-index: AcVzZJNkPr6fSXC8RMqPvlgseXtMiA==
| X-WBNR-Posting-Host: 12.155.246.10
| From: "=?Utf-8?B?YXBwZGV2dGVjaA==?=" <appdevtech@.online.nospam>
| References: <44BBA514-66F1-4FDD-A0D2-11E6BB6D2F13@.microsoft.com>
<#BtbY5rcFHA.1324@.tk2msftngp13.phx.gbl>
| Subject: Re: Handling Data Integrity Issues in SQL2000
| Date: Fri, 17 Jun 2005 10:47:05 -0700
| Lines: 98
| Message-ID: <6CC7E76E-3E1C-49BE-B976-71D0D694B5AB@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 8bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.datawarehouse
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.datawarehouse:1828
| X-Tomcat-NG: microsoft.public.sqlserver.datawarehouse
|
| Thanks Dave,
| My gut feeling is to fix the RI issues first also, but in this case I can
| use the cube to identify the issues, versus catching the data during
import
| then writing a report to notify the users of the inconsistent data.
|
| When you say "create an UNKNOWN member in the base dimension” do you
mean
| adding an additional record to my dimension table?
|
| Value Description
| A Growth
| B No-Growth
| X Unknown
|
| Then in the view if it does not exist, return X(Unknown)?
|
| "Dave Wickert [MSFT]" wrote:
|
| > (microsoft.public.sqlserver.olap is a better newsgroup for a posting
like
| > this, but here goes)
| >
| > My first gut feel is that you should not be allowing this to occur.
This is
| > basic RI between a fact table and the dimension. You should be
processing
| > your dimension to pickup additions prior to processing the fact table.
This
| > will ensure that this doesn't occur if RI is in-place on the RDBMS.
| >
| > The row is disappearing from the fact table because the default SQL
| > statement is an inner join between the fact table and the dimension
table.
| > Thus the row will not be returned to Analysis Services at all . . . we
| > simply don't see it. The RDBMS eliminates it before we get it.
| >
| > In SQL2K, you best option is to create an UNKNOWN member in the base
| > dimension and then load your fact data through a view. In the view use
a
| > CASE clause with an EXISTS and replace the FK being returned based on
| > whether or not that key exists. If it doesn't exist, then return the
UNKNOWN
| > member.
| >
| > In SQL2K5, the system supports an unknown member directly and you can
load
| > data w/ an error configuration which tells it to assign invalid FKs
with the
| > system generated unknown member directly.
| > --
| > Dave Wickert [MSFT]
| > dwickert@.online.microsoft.com
| > Program Manager
| > BI SystemsTeam
| > SQL BI Product Unit (Analysis Services)
| > --
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| >
| > "appdevtech" <appdevtech@.online.nospam> wrote in message
| > news:44BBA514-66F1-4FDD-A0D2-11E6BB6D2F13@.microsoft.com...
| > > Hello,
| > > I'm looking for some help with analysis services.
| > >
| > > I ahve a very simple fact table which has 2 columns
| > > Account Number and Investment Objective.
| > >
| > > Fact table
| > > Account Number Investment Objective
| > > 12345678 A
| > > 22222222 A
| > > 33333333 B
| > > 44444444 X
| > >
| > > A dimension is needed for the investment objective
| > > So I have a lookup table which is
| > >
| > > Value Description
| > > A Growth
| > > B No-Growth
| > >
| > > There is not an X value in the lookup table so when I look at the
count
| > > for
| > > accounts I only get 3. The account 44444444 never shows up.
| > > Is there a way to have 44444444 or any other account that might get a
| > > value
| > > not in the lookup table to fall into an 'Unknown' type description?
| > >
| > > I understand the best way to solve is to make sure I have a value in
the
| > > lookup table for every value that is in the investment objective
fact, the
| > > problem is I cant control what might get added to it, and we want to
be
| > > able
| > > to have an unknown description and have everything that falls out of
the
| > > range of the lookup go into that. This will allow the users of the
cube
| > > to
| > > find the bad entries and fix them.
| > >
| > > Of course this is sample data and the real tables have millions of
records
| > > and 100's of columns, but I think the basic concept applies.
| > >
| > > Any help or a direction to go in would be greatly appreciated. BTW
this
| > > is
| > > SQL2000
| > > Thanks
| > >
| >
| >
| >
|