ETL / Data mart / Cube question

We are in process of using new Kimball toolkit book as our guide to create another DM. Since we only have one data source, we think we need to pull in all the tables into the staging area. At first we wanted to just pull in minimal data to populate the Dimension/Fact tables and any information needed to derive columns.
However the business area wants a complete snapshot of ALL THE data for this given subject matter. Their reason is that sometimes our CUBES don't have all the information they want, and they want to be able to query the data mart tables against the staging area tables using SQL or Access. Since our data mart load times is first Saturday of the month - this argues in favor of this. If the load process was every day or once a week - it might be better to just query across the data mart and the reporting (replicated production) database - the the data mart tables would be older than the information they were linking against on the replicated report DB using the 1st Saturday approach.
Questions:
1. Is this the correct approach?
2. According to a Microsoft diagram, the load goes from Source to Staging to OLAP DB - which is really just another OLTP DB but the tables are modeled as Dimension/Fact tables, right?
3. Our existing data mart which everyone mostly uses CUBES to access, has a big manual step. After all the ETL happens and the Dim/Fact tables have been updated - we are having to open up SSAS and process the cubes, the first business day after the 1st Saturday, to get them refreshed - can't this be automated within SSIS?
4. There have been some major production changes to our existing data source for our existing production data mart. All the ETL was done on SQL2k with stored proc's and DTS was only used as the policeman to control the flow. All the SCD processes were done and are working fine using SP's. We now have to make changes and are faced with having two complete different systems to manage - one with an ETL using the old method and the 2nd using SSIS. Some of the CUBES from the original data mart are going to be combined with our new cubes we are creating in our new system. To try and make it clearer - we have for example a Client dimension and a Client fact table that first is refreshed monthly with the old ETL, and now our new subject matter is now also going to affect the Client dimension and Client fact table that was created the first project. The old production changes it appears may be many weeks to adjust using the old ETL methods - so it begs the question, since all the ETL is working fine with DTS as flow master - is it perhaps better to get it all working with SSIS calling all the SP's and incorporate all our new ETL within SSIS using the new features that SSIS can offer?
5. When adding a 3rd subject matter data mart - is it a 3rd ETL package or just enhancing the one we have?
____________________
Joe Horton
SQL 2005 Business Intelligence
Aging & Disability Services Administration
I think that for 1, 2 and 3 you're right - you can schedule cube
process withing SSIS (there is a specific task to do that).
For 4 and 5 it's hard to say - it depends from the cost of
implementation and the cost of maintenance... SSIS packages are easier
to maintain in the long term, but you could have an higher cost in the
short term for the initial design. Mixing SSIS and SP's is certainly
feasible, even if it makes the solution harder to manage and also
performance can suffert compared to a native SSIS solution. Anyway, it
all depends by other constraints (volume of data, time of development
available, and so on).
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
On Feb 1, 6:16 pm, "Joe" <hortoris...@.gmail.dot.com> wrote:
> We are in process of using new Kimball toolkit book as our guide to create another DM. Since we only have one data source, we think we need to pull in all the tables into the staging area. At first we wanted to just pull in minimal data to populate the Dimension/Fact tables and any information needed to derive columns.
> However the business area wants a complete snapshot of ALL THE data for this given subject matter. Their reason is that sometimes our CUBES don't have all the information they want, and they want to be able to query the data mart tables against the staging area tables using SQL or Access. Since our data mart load times is first Saturday of the month - this argues in favor of this. If the load process was every day or once a week -it might be better to just query across the data mart and the reporting (replicated production) database - the the data mart tables would be older than the information they were linking against on the replicated report DB using the 1st Saturday approach.
> Questions:
> 1. Is this the correct approach?
> 2. According to a Microsoft diagram, the load goes from Source to Staging to OLAP DB - which is really just another OLTP DB but the tables aremodeled as Dimension/Fact tables, right?
> 3. Our existing data mart which everyone mostly uses CUBES to access, has a big manual step. After all the ETL happens and the Dim/Fact tables have been updated - we are having to open up SSAS and process the cubes, the first business day after the 1st Saturday, to get them refreshed - can't this be automated within SSIS?
> 4. There have been some major production changes to our existing data source for our existing production data mart. All the ETL was done on SQL2k with stored proc's and DTS was only used as the policeman to control the flow. All the SCD processes were done and are working fine using SP's. We now have to make changes and are faced with having two complete different systems to manage - one with an ETL using the old method and the 2nd using SSIS. Some of the CUBES from the original data mart are going to be combined with our new cubes we are creating in our new system. To try and make it clearer - we have for example a Client dimension and a Client fact table that first is refreshed monthly with the old ETL, and now our new subject matter is now also going to affect the Client dimension and Client fact table that was created the first project. The old production changes it appears may be many weeks to adjust using the old ETL methods - so it begs the question, since all the ETL is working fine with DTS as flow master - is it perhaps better to get it all working with SSIS calling all the SP's and incorporate all our new ETL within SSIS using the new features that SSIS can offer?
> 5. When adding a 3rd subject matter data mart - is it a 3rd ETL package or just enhancing the one we have?
> ____________________
> Joe Horton
> SQL 2005 Business Intelligence
> Aging & Disability Services Administration
|||Hi Joe,
I'm involved in a Data Warehouse project. Client is already invested in SQL
Server 2005.
Directly coming to the point..
You must have setup server(s) for your data warehouse. I wanted to know
whether you were involved in Server Sizing / Data Sizing. If yes, then can
you please guide me as to which method / template did you use to size your
data and based on it what hardware (with configuration) did you suggest /
decide?
Please help this is very critical and urgent!
Regards,
Bharat Tamhankar
"Joe" wrote:

>
> We are in process of using new Kimball toolkit book as our guide
> to create another DM. Since we only have one data source, we think we
> need to pull in all the tables into the staging area. At first we wanted
> to just pull in minimal data to populate the Dimension/Fact tables and any
> information needed to derive columns.
> However the business area wants a complete snapshot of ALL THE
> data for this given subject matter. Their reason is that sometimes our
> CUBES don't have all the information they want, and they want to be able
> to query the data mart tables against the staging area tables using SQL or
> Access. Since our data mart load times is first Saturday of the month -
> this argues in favor of this. If the load process was every day or once a
> week - it might be better to just query across the data mart and the
> reporting (replicated production) database - the the data mart tables
> would be older than the information they were linking against on the
> replicated report DB using the 1st Saturday approach.
>
> Questions:
> 1. Is this the correct approach?
> 2. According to a Microsoft diagram, the load goes from Source to
> Staging to OLAP DB - which is really just another OLTP DB but the tables
> are modeled as Dimension/Fact tables, right?
> 3. Our existing data mart which everyone mostly uses CUBES to
> access, has a big manual step. After all the ETL happens and the Dim/Fact
> tables have been updated - we are having to open up SSAS and process the
> cubes, the first business day after the 1st Saturday, to get them
> refreshed - can't this be automated within SSIS?
> 4. There have been some major production changes to our existing
> data source for our existing production data mart. All the ETL was done
> on SQL2k with stored proc's and DTS was only used as the policeman to
> control the flow. All the SCD processes were done and are working fine
> using SP's. We now have to make changes and are faced with having two
> complete different systems to manage - one with an ETL using the old
> method and the 2nd using SSIS. Some of the CUBES from the original data
> mart are going to be combined with our new cubes we are creating in our
> new system. To try and make it clearer - we have for example a Client
> dimension and a Client fact table that first is refreshed monthly with the
> old ETL, and now our new subject matter is now also going to affect the
> Client dimension and Client fact table that was created the first project.
> The old production changes it appears may be many weeks to adjust using
> the old ETL methods - so it begs the question, since all the ETL is
> working fine with DTS as flow master - is it perhaps better to get it all
> working with SSIS calling all the SP's and incorporate all our new ETL
> within SSIS using the new features that SSIS can offer?
> 5. When adding a 3rd subject matter data mart - is it a 3rd ETL
> package or just enhancing the one we have?
>
>
>
> ____________________
> Joe Horton
> SQL 2005 Business Intelligence
> Aging & Disability Services Administration
>
>
>
|||I'm sorry - the existing archetecture was in place before I arrived and as
my expertise is in coding not hardware - I wouldn't have been much help in
that area anyhow.
"Bharat Tamhankar" <BharatTamhankar@.discussions.microsoft.com> wrote in
message news:2B8F4494-BADC-4F47-86BC-383C5C07F0E6@.microsoft.com...[vbcol=seagreen]
> Hi Joe,
> I'm involved in a Data Warehouse project. Client is already invested in
> SQL
> Server 2005.
> Directly coming to the point..
> You must have setup server(s) for your data warehouse. I wanted to know
> whether you were involved in Server Sizing / Data Sizing. If yes, then can
> you please guide me as to which method / template did you use to size your
> data and based on it what hardware (with configuration) did you suggest /
> decide?
> Please help this is very critical and urgent!
> Regards,
> Bharat Tamhankar
>
> "Joe" wrote:

ETL / Data mart / Cube question

We are in process of using new Kimball toolkit book as our guide t
o create another DM. Since we only have one data source, we think we need t
o pull in all the tables into the staging area. At first we wanted to just
pull in minimal data to populate the Dimension/Fact tables and any informati
on needed to derive columns.
However the business area wants a complete snapshot of ALL THE dat
a for this given subject matter. Their reason is that sometimes our CUBES d
on't have all the information they want, and they want to be able to query t
he data mart tables against the staging area tables using SQL or Access. Si
nce our data mart load times is first Saturday of the month - this argues in
favor of this. If the load process was every day or once a week - it might
be better to just query across the data mart and the reporting (replicated p
roduction) database - the the data mart tables would be older than the infor
mation they were linking against on the replicated report DB using the 1st S
aturday approach.
Questions:
1. Is this the correct approach?
2. According to a Microsoft diagram, the load goes from Source to Stag
ing to OLAP DB - which is really just another OLTP DB but the tables are mod
eled as Dimension/Fact tables, right?
3. Our existing data mart which everyone mostly uses CUBES to access,
has a big manual step. After all the ETL happens and the Dim/Fact tables ha
ve been updated - we are having to open up SSAS and process the cubes, the f
irst business day after the 1st Saturday, to get them refreshed - can't this
be automated within SSIS?
4. There have been some major production changes to our existing data
source for our existing production data mart. All the ETL was done on SQL2k
with stored proc's and DTS was only used as the policeman to control the fl
ow. All the SCD processes were done and are working fine using SP's. We no
w have to make changes and are faced with having two complete different syst
ems to manage - one with an ETL using the old method and the 2nd using SSIS.
Some of the CUBES from the original data mart are going to be combined wit
h our new cubes we are creating in our new system. To try and make it clear
er - we have for example a Client dimension and a Client fact table that fir
st is refreshed monthly with the old ETL, and now our new subject matter is
now also going to affect the Client dimension and Client fact table that was
created the first project. The old production changes it appears may be m
any weeks to adjust using the old ETL methods - so it begs the question, sin
ce all the ETL is working fine with DTS as flow master - is it perhaps bette
r to get it all working with SSIS calling all the SP's and incorporate all o
ur new ETL within SSIS using the new features that SSIS can offer?
5. When adding a 3rd subject matter data mart - is it a 3rd ETL packag
e or just enhancing the one we have?
____________________
Joe Horton
SQL 2005 Business Intelligence
Aging & Disability Services AdministrationI think that for 1, 2 and 3 you're right - you can schedule cube
process withing SSIS (there is a specific task to do that).
For 4 and 5 it's hard to say - it depends from the cost of
implementation and the cost of maintenance... SSIS packages are easier
to maintain in the long term, but you could have an higher cost in the
short term for the initial design. Mixing SSIS and SP's is certainly
feasible, even if it makes the solution harder to manage and also
performance can suffert compared to a native SSIS solution. Anyway, it
all depends by other constraints (volume of data, time of development
available, and so on).
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
On Feb 1, 6:16 pm, "Joe" <hortoris...@.gmail.dot.com> wrote:
> =B7 We are in process of using new Kimball toolkit book as our gu=
ide to create another DM. Since we only have one data source, we think we =
need to pull in all the tables into the staging area. At first we wanted t=
o just pull in minimal data to populate the Dimension/Fact tables and any i=
nformation needed to derive columns. =20
> =B7 However the business area wants a complete snapshot of ALL TH=
E data for this given subject matter. Their reason is that sometimes our C=
UBES don't have all the information they want, and they want to be able to =
query the data mart tables against the staging area tables using SQL or Acc=
ess. Since our data mart load times is first Saturday of the month - this =
argues in favor of this. If the load process was every day or once a week -=
it might be better to just query across the data mart and the reporting (r=
eplicated production) database - the the data mart tables would be older th=
an the information they were linking against on the replicated report DB us=
ing the 1st Saturday approach.
> Questions:
> 1. Is this the correct approach? =20
> 2. According to a Microsoft diagram, the load goes from Source to S=
taging to OLAP DB - which is really just another OLTP DB but the tables are=
modeled as Dimension/Fact tables, right? =20
> 3. Our existing data mart which everyone mostly uses CUBES to acces=
s, has a big manual step. After all the ETL happens and the Dim/Fact table=
s have been updated - we are having to open up SSAS and process the cubes, =
the first business day after the 1st Saturday, to get them refreshed - can'=
t this be automated within SSIS?
> 4. There have been some major production changes to our existing da=
ta source for our existing production data mart. All the ETL was done on S=
QL2k with stored proc's and DTS was only used as the policeman to control t=
he flow. All the SCD processes were done and are working fine using SP's. =
We now have to make changes and are faced with having two complete differe=
nt systems to manage - one with an ETL using the old method and the 2nd usi=
ng SSIS. Some of the CUBES from the original data mart are going to be com=
bined with our new cubes we are creating in our new system. To try and mak=
e it clearer - we have for example a Client dimension and a Client fact tab=
le that first is refreshed monthly with the old ETL, and now our new subjec=
t matter is now also going to affect the Client dimension and Client fact t=
able that was created the first project. The old production changes it ap=
pears may be many weeks to adjust using the old ETL methods - so it begs th=
e question, since all the ETL is working fine with DTS as flow master - is =
it perhaps better to get it all working with SSIS calling all the SP's and =
incorporate all our new ETL within SSIS using the new features that SSIS ca=
n offer? =20
> 5. When adding a 3rd subject matter data mart - is it a 3rd ETL pac=
kage or just enhancing the one we have?
> ____________________
> Joe Horton
> SQL 2005 Business Intelligence
> Aging & Disability Services Administration|||Hi Joe,
I'm involved in a Data Warehouse project. Client is already invested in SQL
Server 2005.
Directly coming to the point..
You must have setup server(s) for your data warehouse. I wanted to know
whether you were involved in Server Sizing / Data Sizing. If yes, then can
you please guide me as to which method / template did you use to size your
data and based on it what hardware (with configuration) did you suggest /
decide?
Please help this is very critical and urgent!
Regards,
Bharat Tamhankar
"Joe" wrote:

>
> We are in process of using new Kimball toolkit book as our guide
> to create another DM. Since we only have one data source, we think we
> need to pull in all the tables into the staging area. At first we wanted
> to just pull in minimal data to populate the Dimension/Fact tables and any
> information needed to derive columns.
> However the business area wants a complete snapshot of ALL THE
> data for this given subject matter. Their reason is that sometimes our
> CUBES don't have all the information they want, and they want to be able
> to query the data mart tables against the staging area tables using SQL or
> Access. Since our data mart load times is first Saturday of the month -
> this argues in favor of this. If the load process was every day or once a
> week - it might be better to just query across the data mart and the
> reporting (replicated production) database - the the data mart tables
> would be older than the information they were linking against on the
> replicated report DB using the 1st Saturday approach.
>
> Questions:
> 1. Is this the correct approach?
> 2. According to a Microsoft diagram, the load goes from Source to
> Staging to OLAP DB - which is really just another OLTP DB but the tables
> are modeled as Dimension/Fact tables, right?
> 3. Our existing data mart which everyone mostly uses CUBES to
> access, has a big manual step. After all the ETL happens and the Dim/Fact
> tables have been updated - we are having to open up SSAS and process the
> cubes, the first business day after the 1st Saturday, to get them
> refreshed - can't this be automated within SSIS?
> 4. There have been some major production changes to our existing
> data source for our existing production data mart. All the ETL was done
> on SQL2k with stored proc's and DTS was only used as the policeman to
> control the flow. All the SCD processes were done and are working fine
> using SP's. We now have to make changes and are faced with having two
> complete different systems to manage - one with an ETL using the old
> method and the 2nd using SSIS. Some of the CUBES from the original data
> mart are going to be combined with our new cubes we are creating in our
> new system. To try and make it clearer - we have for example a Client
> dimension and a Client fact table that first is refreshed monthly with the
> old ETL, and now our new subject matter is now also going to affect the
> Client dimension and Client fact table that was created the first project.
> The old production changes it appears may be many weeks to adjust using
> the old ETL methods - so it begs the question, since all the ETL is
> working fine with DTS as flow master - is it perhaps better to get it all
> working with SSIS calling all the SP's and incorporate all our new ETL
> within SSIS using the new features that SSIS can offer?
> 5. When adding a 3rd subject matter data mart - is it a 3rd ETL
> package or just enhancing the one we have?
>
>
>
> ____________________
> Joe Horton
> SQL 2005 Business Intelligence
> Aging & Disability Services Administration
>
>
>|||I'm sorry - the existing archetecture was in place before I arrived and as
my expertise is in coding not hardware - I wouldn't have been much help in
that area anyhow.
"Bharat Tamhankar" <BharatTamhankar@.discussions.microsoft.com> wrote in
message news:2B8F4494-BADC-4F47-86BC-383C5C07F0E6@.microsoft.com...[vbcol=seagreen]
> Hi Joe,
> I'm involved in a Data Warehouse project. Client is already invested in
> SQL
> Server 2005.
> Directly coming to the point..
> You must have setup server(s) for your data warehouse. I wanted to know
> whether you were involved in Server Sizing / Data Sizing. If yes, then can
> you please guide me as to which method / template did you use to size your
> data and based on it what hardware (with configuration) did you suggest /
> decide?
> Please help this is very critical and urgent!
> Regards,
> Bharat Tamhankar
>
> "Joe" wrote:
>

Errors processing SSAS 2005 Cube against SQL 2000 database

Hi,

I am receiving the following erros when i try to deploy/process my cube. The source db is Sql Server 2000, could this be the issue? I have a connection created (have tried both OLDB providers - SQL Native client and MS OLDB provider) and I am supplying a sql server user name and pass word (datareader permission in db). The 2005 SSAS service is running under a local user account so authentication is not possible using that account. And I have tried various settings under "Impersonation Information" with no luck?

SQL 2K5 sp1 on Win XP sp2

Sql 2k (sp4) on Win2k server

Thanks!

Build started: Project: Aegis, Configuration: Development

Started Building Analysis Services project: Incremental ....

Build complete -- 0 errors, 0 warnings

Deploy started: Project: Aegis, Configuration: Development

Performing a full deployment of the 'Aegis' database to the 'JWILAMETZ\SQLSERVER2005_01' server.

Generating deployment script...

Add Database Aegis

Process Database Aegis

Done

Sending deployment script to the server...

Error -1056899072 : The following system error occurred: Logon failure: unknown user name or bad password. .

Error -1055784860 : Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'IBANK AEGIS PROD DM', Name of 'IBANK AEGIS PROD DM'.

Error -1054932980 : Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'CURRENCY DIM', Name of 'CURRENCY DIM' was being processed.

Error -1054932979 : Errors in the OLAP storage engine: An error occurred while the 'CURRENCY CD' attribute of the 'CURRENCY DIM' dimension from the 'Aegis' database was being processed.

Error -1056899072 : The following system error occurred: Logon failure: unknown user name or bad password. .

Error -1055784860 : Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'IBANK AEGIS PROD DM', Name of 'IBANK AEGIS PROD DM'.

Error -1054932980 : Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'CURRENCY DIM', Name of 'CURRENCY DIM' was being processed.

Error -1054932979 : Errors in the OLAP storage engine: An error occurred while the 'CURRENCY DESC' attribute of the 'CURRENCY DIM' dimension from the 'Aegis' database was being processed.

Error -1056899072 : The following system error occurred: Logon failure: unknown user name or bad password. .

Error -1055784860 : Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'IBANK AEGIS PROD DM', Name of 'IBANK AEGIS PROD DM'.

Error -1054932980 : Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'CURRENCY DIM', Name of 'CURRENCY DIM' was being processed.

Error -1054932979 : Errors in the OLAP storage engine: An error occurred while the 'DATE ENTERED' attribute of the 'CURRENCY DIM' dimension from the 'Aegis' database was being processed.

Error -1056899072 : The following system error occurred: Logon failure: unknown user name or bad password. .

Error -1055784860 : Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'IBANK AEGIS PROD DM', Name of 'IBANK AEGIS PROD DM'.

Error -1054932980 : Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'CURRENCY DIM', Name of 'CURRENCY DIM' was being processed.

Error -1054932979 : Errors in the OLAP storage engine: An error occurred while the 'EXCHANGE RATE' attribute of the 'CURRENCY DIM' dimension from the 'Aegis' database was being processed.

Deploy complete -- 16 errors, 0 warnings

========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========

========== Deploy: 0 succeeded, 1 failed, 0 skipped ==========

First try and see if you connect to your SQL Server 2000 from AS machine.

Login into AS machine under credentials of your local user account and try connecting to your SQL Server machine.

The credentals many customers choosing for their Data Source object is ImpersonateServiceAccount.

Please note. Once you saved user name and password as part of your connection sting, you will have to re-submit username and password every time you update your datasource.

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

|||

Willajo,

can you please let me know how could you solve? I am getting the same error.

thanks a lot in advance...

Errors processing SSAS 2005 Cube against SQL 2000 database

Hi,

I am receiving the following erros when i try to deploy/process my cube. The source db is Sql Server 2000, could this be the issue? I have a connection created (have tried both OLDB providers - SQL Native client and MS OLDB provider) and I am supplying a sql server user name and pass word (datareader permission in db). The 2005 SSAS service is running under a local user account so authentication is not possible using that account. And I have tried various settings under "Impersonation Information" with no luck?

SQL 2K5 sp1 on Win XP sp2

Sql 2k (sp4) on Win2k server

Thanks!

Build started: Project: Aegis, Configuration: Development

Started Building Analysis Services project: Incremental ....

Build complete -- 0 errors, 0 warnings

Deploy started: Project: Aegis, Configuration: Development

Performing a full deployment of the 'Aegis' database to the 'JWILAMETZ\SQLSERVER2005_01' server.

Generating deployment script...

Add Database Aegis

Process Database Aegis

Done

Sending deployment script to the server...

Error -1056899072 : The following system error occurred: Logon failure: unknown user name or bad password. .

Error -1055784860 : Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'IBANK AEGIS PROD DM', Name of 'IBANK AEGIS PROD DM'.

Error -1054932980 : Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'CURRENCY DIM', Name of 'CURRENCY DIM' was being processed.

Error -1054932979 : Errors in the OLAP storage engine: An error occurred while the 'CURRENCY CD' attribute of the 'CURRENCY DIM' dimension from the 'Aegis' database was being processed.

Error -1056899072 : The following system error occurred: Logon failure: unknown user name or bad password. .

Error -1055784860 : Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'IBANK AEGIS PROD DM', Name of 'IBANK AEGIS PROD DM'.

Error -1054932980 : Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'CURRENCY DIM', Name of 'CURRENCY DIM' was being processed.

Error -1054932979 : Errors in the OLAP storage engine: An error occurred while the 'CURRENCY DESC' attribute of the 'CURRENCY DIM' dimension from the 'Aegis' database was being processed.

Error -1056899072 : The following system error occurred: Logon failure: unknown user name or bad password. .

Error -1055784860 : Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'IBANK AEGIS PROD DM', Name of 'IBANK AEGIS PROD DM'.

Error -1054932980 : Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'CURRENCY DIM', Name of 'CURRENCY DIM' was being processed.

Error -1054932979 : Errors in the OLAP storage engine: An error occurred while the 'DATE ENTERED' attribute of the 'CURRENCY DIM' dimension from the 'Aegis' database was being processed.

Error -1056899072 : The following system error occurred: Logon failure: unknown user name or bad password. .

Error -1055784860 : Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'IBANK AEGIS PROD DM', Name of 'IBANK AEGIS PROD DM'.

Error -1054932980 : Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'CURRENCY DIM', Name of 'CURRENCY DIM' was being processed.

Error -1054932979 : Errors in the OLAP storage engine: An error occurred while the 'EXCHANGE RATE' attribute of the 'CURRENCY DIM' dimension from the 'Aegis' database was being processed.

Deploy complete -- 16 errors, 0 warnings

========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========

========== Deploy: 0 succeeded, 1 failed, 0 skipped ==========

First try and see if you connect to your SQL Server 2000 from AS machine.

Login into AS machine under credentials of your local user account and try connecting to your SQL Server machine.

The credentals many customers choosing for their Data Source object is ImpersonateServiceAccount.

Please note. Once you saved user name and password as part of your connection sting, you will have to re-submit username and password every time you update your datasource.

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

|||

Willajo,

can you please let me know how could you solve? I am getting the same error.

thanks a lot in advance...

errors processing cube

i'm facing some difficulties in trying to process a 3 dimensional cube.
Everytime i try to process this cube it'll complain that "A member with key
"(something)" was found in the fact table but not found in the level
(dimension level's name) of the dimension (dimension name)
anyone got similar experience in this? i've check that the fact table ids
are all present in the dimension tables... what could be wrong? please
help...
You need to reprocess your dimension. When a dimension is processed the data
in it is stored in a different data structure independent of the actual
dimension tables, and additions to the dimension tables won't be included in
the dimension unless you process it again.
Jacco Schalkwijk
SQL Server MVP
"Nestor" <n3570r@.yahoo.com> wrote in message
news:ODIW9DN9EHA.2788@.TK2MSFTNGP15.phx.gbl...
> i'm facing some difficulties in trying to process a 3 dimensional cube.
> Everytime i try to process this cube it'll complain that "A member with
> key "(something)" was found in the fact table but not found in the level
> (dimension level's name) of the dimension (dimension name)
> anyone got similar experience in this? i've check that the fact table ids
> are all present in the dimension tables... what could be wrong? please
> help...
>

errors processing cube

i'm facing some difficulties in trying to process a 3 dimensional cube.
Everytime i try to process this cube it'll complain that "A member with key
"(something)" was found in the fact table but not found in the level
(dimension level's name) of the dimension (dimension name)
anyone got similar experience in this? i've check that the fact table ids
are all present in the dimension tables... what could be wrong? please
help...You need to reprocess your dimension. When a dimension is processed the data
in it is stored in a different data structure independent of the actual
dimension tables, and additions to the dimension tables won't be included in
the dimension unless you process it again.
Jacco Schalkwijk
SQL Server MVP
"Nestor" <n3570r@.yahoo.com> wrote in message
news:ODIW9DN9EHA.2788@.TK2MSFTNGP15.phx.gbl...
> i'm facing some difficulties in trying to process a 3 dimensional cube.
> Everytime i try to process this cube it'll complain that "A member with
> key "(something)" was found in the fact table but not found in the level
> (dimension level's name) of the dimension (dimension name)
> anyone got similar experience in this? i've check that the fact table ids
> are all present in the dimension tables... what could be wrong? please
> help...
>

Errors in the OLAP storage engine: The attribute key cannot be found

I'm somewhat new to using SQL server analaysis services so bear with me on this post.

I am getting the following error when trying ot process a simple cube I built:

Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_vwInvoice, Column: Invoice_x0020_Date, Value: 3/7/2004 10:00:00 AM.

This cube use an Invoice table as the fact table and then a "geography" table as a dimensions table. I am using the server built time dimension which seems to be the problem. It seems to not like the "Invoice Date" field which is a datetime field. This appears to happen on imported data from a legacy application where that application didn't store time so we defaulted the time to 10:00 AM. My guess is that the Invoice Date field has to be unique? If that is the case, any idea on how to get aroudn that?

Thanks for your time,

Marc

Hi,

The error means that the '3/7/2004 10:00:00 AM' value from the 'dbo_vwInvoice' fact table cannot be found in the time dimension table. You mentioned that the time dimension is the server generated one. The server time dimensions only go down to the day level (not hour, minute and second), thus the value will indeed not be found.

If you do not need to do analysis on the hour/minute/second level (in other words, if you do not use the time part of the 'Invoice Date' from from the 'dbo_vwInvoice' fact table), then you can do this:

- create a calculated column (in the DataSourceView, you don't need to alter the actual relational table) in the 'dbo_vwInvoice' fact table to only keep the date part of the 'Invoice Date' column

- then join the fact table (in the 'Dimension Usage' tab of the cube editor) with the time dimension table by that calculated column

Adrian Dumitrascu

|||

You know, I was kind of thinking that but I couldn't find any documentation stating that. Thanks for the response. I would have thought it would inheritly figure out that the field is a datetime field and to just extract the date portion. Easy enough to fix though.

Thanks again,

Marc

|||

Well I spoke too soon. I'm still getting the error. I did a convert(varchar(10),invoice date,101) as the calculated field and still came up with this errror:

Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_vwInvoice, Column: InvoiceDateOnly, Value: 3/29/1998. Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: Date of Dimension: TimeDimension from Database: WinSTIS Analysis Services Project, Cube: Winstis, Measure Group: Vw Invoice, Partition: Vw Invoice, Record: 11.

Any ideas?

Thanks,

Marc

|||

Sorry for the confusion. I figured it out. It was because I didn't have a large enough date range on the time dimension...

Marc

Errors in the metadata manager

Hello,
I'm trying to process a cube and i'm getting the following error message. I'm not working in Analysis Services Project 4, i created another project. I deleted Analysis Services Project 4 and every project i try to execute i get this error message.

"Error 1 File system error: Error opening file; \\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Analysis Services Project4.0.db\TBLE~MC.0.cub.xml is not a disk file or file is not accessible. Errors in the metadata manager. An error occurred when loading the TBLE~MC cube, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Analysis Services Project4.0.db\TBLE~MC.0.cub.xml'. File system error: The following error occurred while writing to the file 'MSSQLServerOLAPService': The event log file is full. . 0 0 "

Any ideas how to solve this problem?

Thank you in advance.

Looks like the data folder is corrupted. Could you please try the following steps:

1. Stop the Analysis Services service
2. Delete everything in your C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\%YOUR_PROJECTNAME% folder
3. Restart the Analysis Services service
4. Re-deploy your project

Hope this helps,

Artur

|||

I already try this but without stopping the analysis services and didn't work.

I will try it again... thanks for the help:)

|||

You should really stop Analysis Server :)

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

Errors in the high-level relational engine

I'm getting the following error back when trying to process in a new measure group into an existing cube in 2005...

"Errors in the high-level relational engine. The data source view does not contain a definition for the 'dbo_view_FACT_CPR_Hours' table or view. The Source property may not have been set."

I'm in the process of migrating my 2000 objects to 2005. I've basically copied over the 2000 relational db over to a 2005 SQL server and am going through and recreating the cubes from scratch. I was having this problem with another fact table and was able to remedy by creating a named query rather then hitting the view directly (even though the named query pulled the view straight). This hasn't worked for all of the tables giving me problems though. I know it's not a permission issue because I can hit some, but not all the tables. The bizarre thing is, I can browse the data in the dsv so I know it is recognizing the datasource, it just isn't recognizing it in the cube.

When I try to go to the partition tab in the cube builder it throws an "object block not set" error and crashes, or just crashes so I'm pretty sure it has something to do with this.

Is anyone else experiencing this problem? Help!

It looks like Analysis Services cannot find a DSV table 'dbo_view_FACT_CPR_Hours' . One of Analysis Services objects is based on this table.

It is very possible you can query tables in DSV and see the data, but if one of the dimension attributes references old table or a view, you will get error like one you are seeing above.

There is nothing much you can do, but carefully go through the cube desing and clean problem after problem. What can help you is: Try and create XMLA script for your database and then search for a 'dbo_view_FACT_CPR_Hours' table. This way you should be able to find an error faster.

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

|||

That's the problem though, there is a 'dbo_view_FACT_CPR_Hours' table in the dsv. I can't understand why AS can't find it when processing the cube. I built the measure group in the cube referencing it that source in the dsv. The cube will process fine without that measure group but as soon as I add it in again and try to process it bombs.

I'll continue to investigate the code on my end. I'll let you know if I come up with something.

Thanks for your help.

Errors in High-level Relational Engine and OLAP storage Engine

Hi all,,

Could someone help me out and explain to me why my two fact tables process successfully but the process still fails when i try to process the cube.

This is the detailed error message.

Ronald

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Object>
<DatabaseID>FirstCube</DatabaseID>
<CubeID>MSCPROJECT</CubeID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>
Processing Cube 'MSCPROJECT' completed successfully.
Start time: 6/20/2006 1:52:34 PM; End time: 6/20/2006 1:52:35 PM; Duration: 0:00:01
Processing Measure Group 'Patient Diagnosis' completed successfully.
Start time: 6/20/2006 1:52:35 PM; End time: 6/20/2006 1:52:35 PM; Duration: 0:00:00
Processing Partition 'Patient Diagnosis' completed successfully.
Start time: 6/20/2006 1:52:35 PM; End time: 6/20/2006 1:52:35 PM; Duration: 0:00:00
Processing Measure Group 'Prescription' completed successfully.
Start time: 6/20/2006 1:52:35 PM; End time: 6/20/2006 1:52:35 PM; Duration: 0:00:00
Processing Partition 'Prescription' completed successfully.
Start time: 6/20/2006 1:52:35 PM; End time: 6/20/2006 1:52:35 PM; Duration: 0:00:00
Errors and Warnings from Response
Errors in the high-level relational engine. The data source view does not contain a definition for the 'dbo_Prescription' table or view. The Source property may not have been set.
Errors in the OLAP storage engine: An error occurred while processing the 'Prescription' partition of the 'Prescription' measure group for the 'MSCPROJECT' cube from the FirstCube database.
Errors in the high-level relational engine. The database operation was cancelled because of an earlier failure.
Errors in the OLAP storage engine: An error occurred while processing the 'Patient Diagnosis' partition of the 'Patient Diagnosis' measure group for the 'MSCPROJECT' cube from the FirstCube database.

Check your DSV - data source view.
Looks like your DSV is missing 'dbo_Prescription' table.

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

|||

Hi Edward,

It looks like as if I have dbo_Prescription table in my data source View.

I will check it out tomorrow morning and i will get back to you.

Thank you for your time.

Ronald

|||

Hi Edward, In my data source view i have the dbo.Prescription table not dbo_Prescription.

Do these namings really matter.

Ronald

|||

The naming is really important. Without you specifying names correctly Analysis Server has no idea how to construct a valid SQL query.

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

|||

Edward,

In My data source view, I have the dbo.Prescription table. You know dbo.Prescription and dbo.PatientDiagnosis are my fact tables. I have failed to understand this since i have these two tables in my data source view.

Please help out.

Is this connected with SQL SERVER 2005 service pack1.

Ronald

Below is the same error i get when i process the cube.

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Object>
<DatabaseID>Analysis Services Project1</DatabaseID>
<CubeID>MSCPROJECT</CubeID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>
Processing Dimension 'Initial Visit' completed successfully.
Start time: 6/21/2006 11:59:20 AM; End time: 6/21/2006 11:59:21 AM; Duration: 0:00:01
Processing Dimension Attribute '(All)' completed successfully.
Start time: 6/21/2006 11:59:20 AM; End time: 6/21/2006 11:59:20 AM; Duration: 0:00:00
Processing Dimension Attribute 'Physician Id' completed successfully. 8 rows have been read.
Start time: 6/21/2006 11:59:20 AM; End time: 6/21/2006 11:59:20 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_InitialVisit].[PhysicianId] AS [dbo_InitialVisitPhysicianId0_0]
FROM [dbo].[InitialVisit] AS [dbo_InitialVisit]
Processing Dimension Attribute 'Nurse ID' completed successfully. 7 rows have been read.
Start time: 6/21/2006 11:59:20 AM; End time: 6/21/2006 11:59:20 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_InitialVisit].[NurseID] AS [dbo_InitialVisitNurseID0_0]
FROM [dbo].[InitialVisit] AS [dbo_InitialVisit]
Processing Dimension Attribute 'Initial Visit' completed successfully. 51 rows have been read.
Start time: 6/21/2006 11:59:20 AM; End time: 6/21/2006 11:59:20 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_InitialVisit].[PatientId] AS [dbo_InitialVisitPatientId0_0],[dbo_InitialVisit].[Date] AS [dbo_InitialVisitDate0_1],[dbo_InitialVisit].[PhysicianId] AS [dbo_InitialVisitPhysicianId0_2],[dbo_InitialVisit].[NurseID] AS [dbo_InitialVisitNurseID0_3]
FROM [dbo].[InitialVisit] AS [dbo_InitialVisit]
Processing Dimension 'Project' completed successfully.
Start time: 6/21/2006 11:59:20 AM; End time: 6/21/2006 11:59:21 AM; Duration: 0:00:01
Processing Dimension Attribute '(All)' completed successfully.
Start time: 6/21/2006 11:59:20 AM; End time: 6/21/2006 11:59:20 AM; Duration: 0:00:00
Processing Dimension Attribute 'Project Name' completed successfully. 4 rows have been read.
Start time: 6/21/2006 11:59:20 AM; End time: 6/21/2006 11:59:20 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_Project].[ProjectName] AS [dbo_ProjectProjectName0_0]
FROM [dbo].[Project] AS [dbo_Project]
Processing Dimension Attribute 'Project' completed successfully. 4 rows have been read.
Start time: 6/21/2006 11:59:20 AM; End time: 6/21/2006 11:59:20 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_Project].[ProjectId] AS [dbo_ProjectProjectId0_0],[dbo_Project].[ProjectName] AS [dbo_ProjectProjectName0_1]
FROM [dbo].[Project] AS [dbo_Project]
Processing Dimension 'Medicine Type' completed successfully.
Start time: 6/21/2006 11:59:20 AM; End time: 6/21/2006 11:59:21 AM; Duration: 0:00:01
Processing Dimension Attribute '(All)' completed successfully.
Start time: 6/21/2006 11:59:20 AM; End time: 6/21/2006 11:59:20 AM; Duration: 0:00:00
Processing Dimension Attribute 'Name' completed successfully. 4 rows have been read.
Start time: 6/21/2006 11:59:20 AM; End time: 6/21/2006 11:59:21 AM; Duration: 0:00:01
SQL queries 1
SELECT
DISTINCT
[dbo_MedicineType].[Name] AS [dbo_MedicineTypeName0_0]
FROM [dbo].[MedicineType] AS [dbo_MedicineType]
Processing Dimension Attribute 'Medicine Type' completed successfully. 4 rows have been read.
Start time: 6/21/2006 11:59:20 AM; End time: 6/21/2006 11:59:21 AM; Duration: 0:00:01
SQL queries 1
SELECT
DISTINCT
[dbo_MedicineType].[MedicineTypeCode] AS [dbo_MedicineTypeMedicineTypeCode0_0],[dbo_MedicineType].[Name] AS [dbo_MedicineTypeName0_1]
FROM [dbo].[MedicineType] AS [dbo_MedicineType]
Processing Dimension 'Patient' completed successfully.
Start time: 6/21/2006 11:59:20 AM; End time: 6/21/2006 11:59:21 AM; Duration: 0:00:01
Processing Dimension Attribute '(All)' completed successfully.
Start time: 6/21/2006 11:59:20 AM; End time: 6/21/2006 11:59:20 AM; Duration: 0:00:00
Processing Dimension Attribute 'DateOfBirth' completed successfully. 49 rows have been read.
Start time: 6/21/2006 11:59:21 AM; End time: 6/21/2006 11:59:21 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_Patient].[DateOfBirth] AS [dbo_PatientDateOfBirth0_0]
FROM [dbo].[Patient] AS [dbo_Patient]
Processing Dimension Attribute 'Gender' completed successfully. 3 rows have been read.
Start time: 6/21/2006 11:59:21 AM; End time: 6/21/2006 11:59:21 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_Patient].[Gender] AS [dbo_PatientGender0_0]
FROM [dbo].[Patient] AS [dbo_Patient]
Processing Dimension Attribute 'PatientId' completed successfully. 50 rows have been read.
Start time: 6/21/2006 11:59:21 AM; End time: 6/21/2006 11:59:21 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_Patient].[PatientId] AS [dbo_PatientPatientId0_0],[dbo_Patient].[DateOfBirth] AS [dbo_PatientDateOfBirth0_1],[dbo_Patient].[Gender] AS [dbo_PatientGender0_2]
FROM [dbo].[Patient] AS [dbo_Patient]
Processing Hierarchy 'PatientId - DateOfBirth - Gender' completed successfully.
Start time: 6/21/2006 11:59:21 AM; End time: 6/21/2006 11:59:21 AM; Duration: 0:00:00
Processing Dimension 'Diagnosis' completed successfully.
Start time: 6/21/2006 11:59:21 AM; End time: 6/21/2006 11:59:21 AM; Duration: 0:00:00
Processing Dimension Attribute '(All)' completed successfully.
Start time: 6/21/2006 11:59:21 AM; End time: 6/21/2006 11:59:21 AM; Duration: 0:00:00
Processing Dimension Attribute 'Diagnosis Name' completed successfully. 5 rows have been read.
Start time: 6/21/2006 11:59:21 AM; End time: 6/21/2006 11:59:21 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_Diagnosis].[Diagnosis name] AS [dbo_DiagnosisDiagnosis_x0020_name0_0]
FROM [dbo].[Diagnosis] AS [dbo_Diagnosis]
Processing Dimension Attribute 'Description' completed successfully. 4 rows have been read.
Start time: 6/21/2006 11:59:21 AM; End time: 6/21/2006 11:59:21 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_Diagnosis].[Description] AS [dbo_DiagnosisDescription0_0]
FROM [dbo].[Diagnosis] AS [dbo_Diagnosis]
Processing Dimension Attribute 'Diagnosis' completed successfully. 5 rows have been read.
Start time: 6/21/2006 11:59:21 AM; End time: 6/21/2006 11:59:21 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_Diagnosis].[DiagnosisCode] AS [dbo_DiagnosisDiagnosisCode0_0],[dbo_Diagnosis].[Diagnosis name] AS [dbo_DiagnosisDiagnosis_x0020_name0_1],[dbo_Diagnosis].[Description] AS [dbo_DiagnosisDescription0_2]
FROM [dbo].[Diagnosis] AS [dbo_Diagnosis]
Processing Dimension 'Medical Provider' completed successfully.
Start time: 6/21/2006 11:59:21 AM; End time: 6/21/2006 11:59:22 AM; Duration: 0:00:01
Processing Dimension Attribute '(All)' completed successfully.
Start time: 6/21/2006 11:59:21 AM; End time: 6/21/2006 11:59:21 AM; Duration: 0:00:00
Processing Dimension Attribute 'Name' completed successfully. 15 rows have been read.
Start time: 6/21/2006 11:59:21 AM; End time: 6/21/2006 11:59:21 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_MedicalProvider].[Name] AS [dbo_MedicalProviderName0_0]
FROM [dbo].[MedicalProvider] AS [dbo_MedicalProvider]
Processing Dimension Attribute 'Gender' completed successfully. 3 rows have been read.
Start time: 6/21/2006 11:59:21 AM; End time: 6/21/2006 11:59:21 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_MedicalProvider].[Gender] AS [dbo_MedicalProviderGender0_0]
FROM [dbo].[MedicalProvider] AS [dbo_MedicalProvider]
Processing Dimension Attribute 'Indicator Nurseor Physician' completed successfully. 4 rows have been read.
Start time: 6/21/2006 11:59:21 AM; End time: 6/21/2006 11:59:21 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_MedicalProvider].[IndicatorNurseorPhysician] AS [dbo_MedicalProviderIndicatorNurseorPhysician0_0]
FROM [dbo].[MedicalProvider] AS [dbo_MedicalProvider]
Processing Dimension Attribute 'Physician Id' completed successfully. 9 rows have been read.
Start time: 6/21/2006 11:59:21 AM; End time: 6/21/2006 11:59:22 AM; Duration: 0:00:01
SQL queries 1
SELECT
DISTINCT
[dbo_MedicalProvider].[PhysicianId] AS [dbo_MedicalProviderPhysicianId0_0]
FROM [dbo].[MedicalProvider] AS [dbo_MedicalProvider]
Processing Dimension Attribute 'Nurse Id' completed successfully. 9 rows have been read.
Start time: 6/21/2006 11:59:21 AM; End time: 6/21/2006 11:59:22 AM; Duration: 0:00:01
SQL queries 1
SELECT
DISTINCT
[dbo_MedicalProvider].[NurseId] AS [dbo_MedicalProviderNurseId0_0]
FROM [dbo].[MedicalProvider] AS [dbo_MedicalProvider]
Processing Dimension Attribute 'Medical Provider' completed successfully. 15 rows have been read.
Start time: 6/21/2006 11:59:21 AM; End time: 6/21/2006 11:59:21 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_MedicalProvider].[MedicalProviderId] AS [dbo_MedicalProviderMedicalProviderId0_0],[dbo_MedicalProvider].[Name] AS [dbo_MedicalProviderName0_1],[dbo_MedicalProvider].[Gender] AS [dbo_MedicalProviderGender0_2],[dbo_MedicalProvider].[IndicatorNurseorPhysician] AS [dbo_MedicalProviderIndicatorNurseorPhysician0_3],[dbo_MedicalProvider].[PhysicianId] AS [dbo_MedicalProviderPhysicianId0_4],[dbo_MedicalProvider].[NurseId] AS [dbo_MedicalProviderNurseId0_5]
FROM [dbo].[MedicalProvider] AS [dbo_MedicalProvider]
Processing Dimension 'Allergy' completed successfully.
Start time: 6/21/2006 11:59:21 AM; End time: 6/21/2006 11:59:22 AM; Duration: 0:00:01
Processing Dimension Attribute '(All)' completed successfully.
Start time: 6/21/2006 11:59:21 AM; End time: 6/21/2006 11:59:21 AM; Duration: 0:00:00
Processing Dimension Attribute 'Allergy Name' completed successfully. 6 rows have been read.
Start time: 6/21/2006 11:59:21 AM; End time: 6/21/2006 11:59:22 AM; Duration: 0:00:01
SQL queries 1
SELECT
DISTINCT
[dbo_Allergy].[AllergyName] AS [dbo_AllergyAllergyName0_0]
FROM [dbo].[Allergy] AS [dbo_Allergy]
Processing Dimension Attribute 'Allergy' completed successfully. 6 rows have been read.
Start time: 6/21/2006 11:59:21 AM; End time: 6/21/2006 11:59:22 AM; Duration: 0:00:01
SQL queries 1
SELECT
DISTINCT
[dbo_Allergy].[AllergyCode] AS [dbo_AllergyAllergyCode0_0],[dbo_Allergy].[AllergyName] AS [dbo_AllergyAllergyName0_1]
FROM [dbo].[Allergy] AS [dbo_Allergy]
Processing Dimension 'Medicine' completed successfully.
Start time: 6/21/2006 11:59:21 AM; End time: 6/21/2006 11:59:22 AM; Duration: 0:00:01
Processing Dimension Attribute '(All)' completed successfully.
Start time: 6/21/2006 11:59:21 AM; End time: 6/21/2006 11:59:21 AM; Duration: 0:00:00
Processing Dimension Attribute 'Name' completed successfully. 19 rows have been read.
Start time: 6/21/2006 11:59:21 AM; End time: 6/21/2006 11:59:22 AM; Duration: 0:00:01
SQL queries 1
SELECT
DISTINCT
[dbo_Medicine].[Name] AS [dbo_MedicineName0_0]
FROM [dbo].[Medicine] AS [dbo_Medicine]
Processing Dimension Attribute 'Medicine' completed successfully. 20 rows have been read.
Start time: 6/21/2006 11:59:21 AM; End time: 6/21/2006 11:59:22 AM; Duration: 0:00:01
SQL queries 1
SELECT
DISTINCT
[dbo_Medicine].[MedicineCode] AS [dbo_MedicineMedicineCode0_0],[dbo_Medicine].[Name] AS [dbo_MedicineName0_1]
FROM [dbo].[Medicine] AS [dbo_Medicine]
Processing Dimension 'Services' completed successfully.
Start time: 6/21/2006 11:59:21 AM; End time: 6/21/2006 11:59:22 AM; Duration: 0:00:01
Processing Dimension Attribute '(All)' completed successfully.
Start time: 6/21/2006 11:59:21 AM; End time: 6/21/2006 11:59:22 AM; Duration: 0:00:01
Processing Dimension Attribute 'Service Title' completed successfully. 36 rows have been read.
Start time: 6/21/2006 11:59:22 AM; End time: 6/21/2006 11:59:22 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_Services].[Service_Title] AS [dbo_ServicesService_Title0_0]
FROM [dbo].[Services] AS [dbo_Services]
Processing Dimension Attribute 'Service Order' completed successfully. 18 rows have been read.
Start time: 6/21/2006 11:59:22 AM; End time: 6/21/2006 11:59:22 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_Services].[Service_Order] AS [dbo_ServicesService_Order0_0]
FROM [dbo].[Services] AS [dbo_Services]
Processing Dimension Attribute 'Services' completed successfully. 36 rows have been read.
Start time: 6/21/2006 11:59:22 AM; End time: 6/21/2006 11:59:22 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_Services].[ServiceId] AS [dbo_ServicesServiceId0_0],[dbo_Services].[Service_Title] AS [dbo_ServicesService_Title0_1],[dbo_Services].[Service_Order] AS [dbo_ServicesService_Order0_2]
FROM [dbo].[Services] AS [dbo_Services]
Processing Dimension 'Return Visit' completed successfully.
Start time: 6/21/2006 11:59:22 AM; End time: 6/21/2006 11:59:23 AM; Duration: 0:00:01
Processing Dimension Attribute '(All)' completed successfully.
Start time: 6/21/2006 11:59:22 AM; End time: 6/21/2006 11:59:22 AM; Duration: 0:00:00
Processing Dimension Attribute 'Physician Id' completed successfully. 8 rows have been read.
Start time: 6/21/2006 11:59:22 AM; End time: 6/21/2006 11:59:22 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_ReturnVisit].[PhysicianId] AS [dbo_ReturnVisitPhysicianId0_0]
FROM [dbo].[ReturnVisit] AS [dbo_ReturnVisit]
Processing Dimension Attribute 'Nurse Id' completed successfully. 9 rows have been read.
Start time: 6/21/2006 11:59:22 AM; End time: 6/21/2006 11:59:22 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_ReturnVisit].[NurseId] AS [dbo_ReturnVisitNurseId0_0]
FROM [dbo].[ReturnVisit] AS [dbo_ReturnVisit]
Processing Dimension Attribute 'Time Start' completed successfully. 11 rows have been read.
Start time: 6/21/2006 11:59:22 AM; End time: 6/21/2006 11:59:22 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_ReturnVisit].[TimeStart] AS [dbo_ReturnVisitTimeStart0_0]
FROM [dbo].[ReturnVisit] AS [dbo_ReturnVisit]
Processing Dimension Attribute 'Time End' completed successfully. 22 rows have been read.
Start time: 6/21/2006 11:59:22 AM; End time: 6/21/2006 11:59:22 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_ReturnVisit].[TimeEnd] AS [dbo_ReturnVisitTimeEnd0_0]
FROM [dbo].[ReturnVisit] AS [dbo_ReturnVisit]
Processing Dimension Attribute 'Return Visit' completed successfully. 50 rows have been read.
Start time: 6/21/2006 11:59:22 AM; End time: 6/21/2006 11:59:22 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_ReturnVisit].[PatientId] AS [dbo_ReturnVisitPatientId0_0],[dbo_ReturnVisit].[Date] AS [dbo_ReturnVisitDate0_1],[dbo_ReturnVisit].[PhysicianId] AS [dbo_ReturnVisitPhysicianId0_2],[dbo_ReturnVisit].[NurseId] AS [dbo_ReturnVisitNurseId0_3],[dbo_ReturnVisit].[TimeStart] AS [dbo_ReturnVisitTimeStart0_4],[dbo_ReturnVisit].[TimeEnd] AS [dbo_ReturnVisitTimeEnd0_5]
FROM [dbo].[ReturnVisit] AS [dbo_ReturnVisit]
Processing Dimension 'Severity' completed successfully.
Start time: 6/21/2006 11:59:22 AM; End time: 6/21/2006 11:59:23 AM; Duration: 0:00:01
Processing Dimension Attribute '(All)' completed successfully.
Start time: 6/21/2006 11:59:22 AM; End time: 6/21/2006 11:59:22 AM; Duration: 0:00:00
Processing Dimension Attribute 'Severity Name' completed successfully. 6 rows have been read.
Start time: 6/21/2006 11:59:22 AM; End time: 6/21/2006 11:59:23 AM; Duration: 0:00:01
SQL queries 1
SELECT
DISTINCT
[dbo_Severity].[SeverityName] AS [dbo_SeveritySeverityName0_0]
FROM [dbo].[Severity] AS [dbo_Severity]
Processing Dimension Attribute 'Severity' completed successfully. 6 rows have been read.
Start time: 6/21/2006 11:59:22 AM; End time: 6/21/2006 11:59:23 AM; Duration: 0:00:01
SQL queries 1
SELECT
DISTINCT
[dbo_Severity].[SeverityCode] AS [dbo_SeveritySeverityCode0_0],[dbo_Severity].[SeverityName] AS [dbo_SeveritySeverityName0_1]
FROM [dbo].[Severity] AS [dbo_Severity]
Processing Cube 'MSCPROJECT' completed successfully.
Start time: 6/21/2006 11:59:23 AM; End time: 6/21/2006 11:59:24 AM; Duration: 0:00:01
Processing Measure Group 'Patient Diagnosis' completed successfully.
Start time: 6/21/2006 11:59:23 AM; End time: 6/21/2006 11:59:24 AM; Duration: 0:00:01
Processing Partition 'Patient Diagnosis' completed successfully.
Start time: 6/21/2006 11:59:23 AM; End time: 6/21/2006 11:59:24 AM; Duration: 0:00:01
Processing Measure Group 'Prescription' completed successfully.
Start time: 6/21/2006 11:59:23 AM; End time: 6/21/2006 11:59:24 AM; Duration: 0:00:01
Processing Partition 'Prescription' completed successfully.
Start time: 6/21/2006 11:59:23 AM; End time: 6/21/2006 11:59:24 AM; Duration: 0:00:01
Errors and Warnings from Response
Errors in the high-level relational engine. The data source view does not contain a definition for the 'dbo_Prescription' table or view. The Source property may not have been set.
Errors in the OLAP storage engine: An error occurred while processing the 'Prescription' partition of the 'Prescription' measure group for the 'MSCPROJECT' cube from the Analysis Services Project1 database.
Errors in the high-level relational engine. The database operation was cancelled because of an earlier failure.
Errors in the OLAP storage engine: An error occurred while processing the 'Patient Diagnosis' partition of the 'Patient Diagnosis' measure group for the 'MSCPROJECT' cube from the Analysis Services Project1 database.

Errors from training mining models in SSIS

I am in the process of creating an Integration Services package to automate the process of training mining models and getting predictions. Until recently, I have been processing the models directly from Business Intelligence Studio without any problems. However, when I try to use the exact same training set as an input to the Data Mining Model Training destination, I get several errors. Here is the output:

[Mining Models [1]] Error: Parser: An error occurred during pipeline processing.

[Mining Models [1]] Error: Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.

[Mining Models [1]] Error: Errors in the OLAP storage engine: An error occurred while the 'CPT MODIFIER' attribute of the 'BCCA DMS ~MC-CLAIM LIN~5' dimension from the 'BCCA LRG DMS TEST' database was being processed.

[Mining Models [1]] Error: File system error: The record ID is incorrect. Physical file: . Logical file: .

[Mining Models [1]] Error: Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.

[Mining Models [1]] Error: Errors in the OLAP storage engine: An error occurred while the 'BILL TYPE' attribute of the 'BCCA DMS ~MC-CLAIM LIN~5' dimension from the 'BCCA LRG DMS TEST' database was being processed.

[Mining Models [1]] Error: File system error: The record ID is incorrect. Physical file: . Logical file: .

[DTS.Pipeline] Error: The ProcessInput method on component "Mining Models" (1) failed with error code 0x80004005. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

I have not been able to find an answer as to why this is happening. I found a post regarding a similar problem with processing an OLAP cube in SSIS, but it seems that the author of that post never found an answer. Has anyone else here seen similar errors when processing mining models from Integration Services?

Also, if I process the mining models manually then try to run only predictions in SSIS, I get many of the same errors. I'll keep looking into the problem myself, but I would be very grateful if someone in this forum could shed some light on this issue.

Can you try to sort your input ascendingly by the column that ends up being mapped to the model's key column?

if this does not work, could you please post some additional information:

- what algorithm is used by your mining model?

- are there other mining modles in the same mining structure? If so, what algorithms do they use?

- the columns of your mining model, their data type and content type

- the structure of the Integration Services pipeline (datatypes for the columns and ther mappings)

|||

Bogdan,

Thanks for your quick reply! I was actually able to fix this problem by eliminating one of the columns from my mining structure. It was a varchar field, but it turns out that there was no useful information in that column (every row contained a blank entry). SSIS was throwing an error when trying to build a dimension for that field. Obviously it's no loss to eliminate a column with no real values in it, but I'm still a little confused as to why it would process through an Analysis Services project but not in SSIS.

|||SSIS has different data handling and transformation logic than AS - it may just be that the conversion from the database type to the internal type failed in SSIS but normally works in AS.

Error1Memory error: The operation cannot be completed because the memory quota estimate exceeds

I get the following error when trying to process a cube on the server.

Error 1 Memory error: The operation cannot be completed because the memory quota estimate exceeds the available system memory. 0 0

It runs on a Microsoft Windows NT 5.2 (3790) with the JUNE SQL 2005 Developer release. I has 4 gigs of RAM

This error comes when trying to process all sizes of cubes.

Hi,

I am receiving the same error when I try to process a specific measure group. This measure group has 13 partitions, each partition containing approximately 26 million rows of data.

I have read this microsoft article http://support.microsoft.com/kb/914595/ which details the same error message for processing large dimensions and the solution is to apply service pack 1. I've talked to our dba and he confirmed that we have sp1 applied. My problem is slightly different since I am processing measure groups.

The exact error message that I am receiving is [Analysis Services Execute DDL Task] Error: Memory error: The operation cannot be completed because the memory quota estimate (17010MB) exceeds the available system memory (13308MB).

Any advice would be much appreciated.

Thanks.

Dev88

|||

Try to search this forum for previous posts with similar errors reported. For instance:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=334677&SiteID=1

The problem is: your machine doesnt have enoght memory for Analysis Server to complete processing operation. Before it starts processing Analysis Server tries to estimate how much memory it needs to complete the operation. The error is given if there is shortage of memory.

Things to try:

1. Process objects one by one not in parallel in a single transaction.
2. Try and see how much memory you have on your machine and if you can free some of it for AS to complete the processing.

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

|||

Hi Edward,

Thank you for your reply. I had already previously looked at that other post and tried to process the objects one by one with no success. The error I received still stated that the memory quota estimate of 17 Gigs exceeded the memory available of 13 gigs. Ie, regardless of whether or not I processed objects 1 at time or in parallel Analysis Services still had the same memory quota estimate of 17 gigs.

In any case we have a 64 bit machine with 20 gigs of memory. In order to process these partitions I had to change some of the analysis services properties. I changed the OLAP \ Process \ BufferMemoryLimit, OLAP \ Process Plan \ MemoryLimit, and Memory \TotalMemoryLimit all to 90 percent. All of these properties have to do with the amount of memory that Analysis Services is allowed to use when porcessing dimensions and partitions, specifically MemoryLimit and BufferMemoryLimit. Only then was I able to process these partitions.

In this solution here from microsoft http://support.microsoft.com/kb/914595/ it deals with this same error message for processing dimensions and states how Analysis Services incorrectly estimates the amount of memory needed to process the dimension. Is this the same for Processing measure groups? I have processed cubes with 4 partitions each containing more than 40 million rows of data. The most recent cube that I processed contained 13 partitions with approximately 26 million rows per partition and only then did I received the error. Like I said earlier I increased the amount of memory available for use by AS and only then was I able to process these partitions. My concern is that when I begin to add more data to my cube, I will again receive this error.

So here are my questions:

1.Is this a problem with Analysis Services incorrectly estimating memory quota?

2.Is there anyway to change how AS estimates its memory usage?

3.Is the only solution to purchase more memory?

Any advice would be much appreciated.

Thanks.

|||

Some of the properties you suggest changing would have a little effect at the processing. For some reason I cannot get to the KB article you refer to.
The one property that probaly had the most effect in your situation was BufferMemoryLimit. You can read about it and about other properties in http://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/SSASProperties.doc. I am not sure I would suggest using this option in your case.

As for the options you have:

Let me try to rephase #1 in my list above: Process smallest possible objects one by one ...

In your case processing of entire measure group internally getting resolved into processsing of all it's partition in parallel. Of cource these partition processing jobs are coming at the same time and competeing for memory.
You should try and create command processing partitions explicitly and make sure you control how many partitions are processed in parallel.

Analysis server is not perfect in estimating amount of memory needed for processing, but it is pretty close in it's etimate. And etimation logic there for a reason; It prevents from you from processing for hours before running into shortage of memory and aborting processing.

In your case, I dont think you need more memory.

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

|||

The document that you suggested is the document that I read prior to modifying my AS properties. The property that actually made it possible to process the partition was an undocumented property OLAP \ Process Plan \ MemoryLimit. I agree that increasing the amount of memory available was probably not the best solution but it seem to be the only one that worked. I'm not sure what you mean by 'command processing partitions explicitly to control the number of partitions being process in parallel' but the steps that I am following to control how many partitions are processed in parallel is manual.

In the MS management studio, I browse the down the the partitions directory of my cube. I then right click the partition which I would like to process and then click process. By processing this single partition, does AS perform its estimate as if all of the partitions would be processed in parallel? Or is there another method which I can use to control how many partitions are being processed?

Once again I appreciate all your help and I look forward to hearing your response.

Thanks,

Dev88

|||

Try following:

Navigate in the SQL Management studio to the Partitions node in your measure group. The "Summary" page on the right will list all of your partitions.

Multi-select partitions you would like to process and launch the Processing dialog using Right-Click menu on the summary page. At this moment you will see multiple partitions in the processing dialog window.

Click on the "Change Settings" button and you will see the the dialog window where you can choose the Processing order. You can select how many partitions in parallel you would like to process.

Closing "Change Settings" dialog gets you back to the Processing dialog, here you click on the Script button above and script processing command into SSMS window.

Take a look at the script generated. It has all of your partitions listed. The Parallel MaxParallel= element specifies how many partitions would be processed in parallel.

Here is whitepaper talking about processing http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_asprocarch.asp

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

|||

Hello, I tried what you suggested. I also reviewed that white paper again since I had previously read it a couple of months ago.

I only selected one partition. On the page after I click "Change Settings" I selected that I one partition to process in parallel. After closing that dialog to get back to the processing dialog I click on the Script button to open the xmla script into ssms. Here is the script:

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<ErrorConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<KeyErrorLimit>-1</KeyErrorLimit>

<KeyNotFound>IgnoreError</KeyNotFound>

<NullKeyNotAllowed>IgnoreError</NullKeyNotAllowed>

</ErrorConfiguration>

<Parallel MaxParallel="1">

<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<Object>

<DatabaseID>Cube009</DatabaseID>

<CubeID>B Data</CubeID>

<MeasureGroupID>P Warehouse</MeasureGroupID>

<PartitionID>P Warehouse</PartitionID>

</Object>

<Type>ProcessFull</Type>

<WriteBackTableCreation>UseExisting</WriteBackTableCreation>

</Process>

</Parallel>

</Batch>

As you can see the Parallel MaxParallel=1. I am still receiving the error.

<return xmlns="urn:schemas-microsoft-com:xml-analysis">

<results xmlns="http://schemas.microsoft.com/analysisservices/2003/xmla-multipleresults">

<root xmlns="urn:schemas-microsoft-com:xml-analysis:empty">

<Exception xmlns="urn:schemas-microsoft-com:xml-analysis:exception" />

<Messages xmlns="urn:schemas-microsoft-com:xml-analysis:exception">

<Error ErrorCode="3238199299" Description="Memory error: The operation cannot be completed because the memory quota estimate (17017MB) exceeds the available system memory (13308MB). " Source="Microsoft SQL Server 2005 Analysis Services" HelpFile="" />

<Error ErrorCode="3240034325" Description="Errors in the OLAP storage engine: An error occurred while processing the indexes for the P Warehouse 1 partition of the P Warehouse measure group of the B Data cube from the Cube009 database." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile="" />

</Messages>

</root>

</results>

</return>

Is there anything else that I can try?

Thanks.

|||

At this moment I am thinking ether your patition is too big. You have 20Gb memory avaliable, I can hardly imagine the partition size that cannot process with that much memory. Try partition your data futher.

It is also possible, you've changed some server properites and now server estimation logic is skewed. Get back to the server defaults.

And lastly, it is possible there is a bug in Analysis Server, so you please and try contacting customer service.

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

Error1Memory error: The operation cannot be completed because the memory quota estimate exce

I get the following error when trying to process a cube on the server.

Error 1 Memory error: The operation cannot be completed because the memory quota estimate exceeds the available system memory. 0 0

It runs on a Microsoft Windows NT 5.2 (3790) with the JUNE SQL 2005 Developer release. I has 4 gigs of RAM

This error comes when trying to process all sizes of cubes.

Hi,

I am receiving the same error when I try to process a specific measure group. This measure group has 13 partitions, each partition containing approximately 26 million rows of data.

I have read this microsoft article http://support.microsoft.com/kb/914595/ which details the same error message for processing large dimensions and the solution is to apply service pack 1. I've talked to our dba and he confirmed that we have sp1 applied. My problem is slightly different since I am processing measure groups.

The exact error message that I am receiving is [Analysis Services Execute DDL Task] Error: Memory error: The operation cannot be completed because the memory quota estimate (17010MB) exceeds the available system memory (13308MB).

Any advice would be much appreciated.

Thanks.

Dev88

|||

Try to search this forum for previous posts with similar errors reported. For instance:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=334677&SiteID=1

The problem is: your machine doesnt have enoght memory for Analysis Server to complete processing operation. Before it starts processing Analysis Server tries to estimate how much memory it needs to complete the operation. The error is given if there is shortage of memory.

Things to try:

1. Process objects one by one not in parallel in a single transaction.
2. Try and see how much memory you have on your machine and if you can free some of it for AS to complete the processing.

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

|||

Hi Edward,

Thank you for your reply. I had already previously looked at that other post and tried to process the objects one by one with no success. The error I received still stated that the memory quota estimate of 17 Gigs exceeded the memory available of 13 gigs. Ie, regardless of whether or not I processed objects 1 at time or in parallel Analysis Services still had the same memory quota estimate of 17 gigs.

In any case we have a 64 bit machine with 20 gigs of memory. In order to process these partitions I had to change some of the analysis services properties. I changed the OLAP \ Process \ BufferMemoryLimit, OLAP \ Process Plan \ MemoryLimit, and Memory \TotalMemoryLimit all to 90 percent. All of these properties have to do with the amount of memory that Analysis Services is allowed to use when porcessing dimensions and partitions, specifically MemoryLimit and BufferMemoryLimit. Only then was I able to process these partitions.

In this solution here from microsoft http://support.microsoft.com/kb/914595/ it deals with this same error message for processing dimensions and states how Analysis Services incorrectly estimates the amount of memory needed to process the dimension. Is this the same for Processing measure groups? I have processed cubes with 4 partitions each containing more than 40 million rows of data. The most recent cube that I processed contained 13 partitions with approximately 26 million rows per partition and only then did I received the error. Like I said earlier I increased the amount of memory available for use by AS and only then was I able to process these partitions. My concern is that when I begin to add more data to my cube, I will again receive this error.

So here are my questions:

1.Is this a problem with Analysis Services incorrectly estimating memory quota?

2.Is there anyway to change how AS estimates its memory usage?

3.Is the only solution to purchase more memory?

Any advice would be much appreciated.

Thanks.

|||

Some of the properties you suggest changing would have a little effect at the processing. For some reason I cannot get to the KB article you refer to.
The one property that probaly had the most effect in your situation was BufferMemoryLimit. You can read about it and about other properties in http://download.microsoft.com/download/4/f/8/4f8f2dc9-a9a7-4b68-98cb-163482c95e0b/SSASProperties.doc. I am not sure I would suggest using this option in your case.

As for the options you have:

Let me try to rephase #1 in my list above: Process smallest possible objects one by one ...

In your case processing of entire measure group internally getting resolved into processsing of all it's partition in parallel. Of cource these partition processing jobs are coming at the same time and competeing for memory.
You should try and create command processing partitions explicitly and make sure you control how many partitions are processed in parallel.

Analysis server is not perfect in estimating amount of memory needed for processing, but it is pretty close in it's etimate. And etimation logic there for a reason; It prevents from you from processing for hours before running into shortage of memory and aborting processing.

In your case, I dont think you need more memory.

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

|||

The document that you suggested is the document that I read prior to modifying my AS properties. The property that actually made it possible to process the partition was an undocumented property OLAP \ Process Plan \ MemoryLimit. I agree that increasing the amount of memory available was probably not the best solution but it seem to be the only one that worked. I'm not sure what you mean by 'command processing partitions explicitly to control the number of partitions being process in parallel' but the steps that I am following to control how many partitions are processed in parallel is manual.

In the MS management studio, I browse the down the the partitions directory of my cube. I then right click the partition which I would like to process and then click process. By processing this single partition, does AS perform its estimate as if all of the partitions would be processed in parallel? Or is there another method which I can use to control how many partitions are being processed?

Once again I appreciate all your help and I look forward to hearing your response.

Thanks,

Dev88

|||

Try following:

Navigate in the SQL Management studio to the Partitions node in your measure group. The "Summary" page on the right will list all of your partitions.

Multi-select partitions you would like to process and launch the Processing dialog using Right-Click menu on the summary page. At this moment you will see multiple partitions in the processing dialog window.

Click on the "Change Settings" button and you will see the the dialog window where you can choose the Processing order. You can select how many partitions in parallel you would like to process.

Closing "Change Settings" dialog gets you back to the Processing dialog, here you click on the Script button above and script processing command into SSMS window.

Take a look at the script generated. It has all of your partitions listed. The Parallel MaxParallel= element specifies how many partitions would be processed in parallel.

Here is whitepaper talking about processing http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_asprocarch.asp

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

|||

Hello, I tried what you suggested. I also reviewed that white paper again since I had previously read it a couple of months ago.

I only selected one partition. On the page after I click "Change Settings" I selected that I one partition to process in parallel. After closing that dialog to get back to the processing dialog I click on the Script button to open the xmla script into ssms. Here is the script:

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<ErrorConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<KeyErrorLimit>-1</KeyErrorLimit>

<KeyNotFound>IgnoreError</KeyNotFound>

<NullKeyNotAllowed>IgnoreError</NullKeyNotAllowed>

</ErrorConfiguration>

<Parallel MaxParallel="1">

<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<Object>

<DatabaseID>Cube009</DatabaseID>

<CubeID>B Data</CubeID>

<MeasureGroupID>P Warehouse</MeasureGroupID>

<PartitionID>P Warehouse</PartitionID>

</Object>

<Type>ProcessFull</Type>

<WriteBackTableCreation>UseExisting</WriteBackTableCreation>

</Process>

</Parallel>

</Batch>

As you can see the Parallel MaxParallel=1. I am still receiving the error.

<return xmlns="urn:schemas-microsoft-com:xml-analysis">

<results xmlns="http://schemas.microsoft.com/analysisservices/2003/xmla-multipleresults">

<root xmlns="urn:schemas-microsoft-com:xml-analysis:empty">

<Exception xmlns="urn:schemas-microsoft-com:xml-analysis:exception" />

<Messages xmlns="urn:schemas-microsoft-com:xml-analysis:exception">

<Error ErrorCode="3238199299" Description="Memory error: The operation cannot be completed because the memory quota estimate (17017MB) exceeds the available system memory (13308MB). " Source="Microsoft SQL Server 2005 Analysis Services" HelpFile="" />

<Error ErrorCode="3240034325" Description="Errors in the OLAP storage engine: An error occurred while processing the indexes for the P Warehouse 1 partition of the P Warehouse measure group of the B Data cube from the Cube009 database." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile="" />

</Messages>

</root>

</results>

</return>

Is there anything else that I can try?

Thanks.

|||

At this moment I am thinking ether your patition is too big. You have 20Gb memory avaliable, I can hardly imagine the partition size that cannot process with that much memory. Try partition your data futher.

It is also possible, you've changed some server properites and now server estimation logic is skewed. Get back to the server defaults.

And lastly, it is possible there is a bug in Analysis Server, so you please and try contacting customer service.

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

Error=cantconvertdata

Hi everyone!

When I try to process a cube via DTS or in a Terminal Server window I get the following error message:

DTSRun OnError: DTSStep_DTSOlapProcess.Task_1, Error = -2147221386 (80040076)
Error string: RECORD=1, _ERROR=CANTCONVERTDATA, COLUMN=30..

The cube runs on:
Win 2000 Advanced SP1 - MS SQL 2000 SP 1

We have found that it works when we keep a user with Admin privileges logged on locally on the server.
Well, it is the solution of the problem but it seems to be a bug anyway.

Has anyone got the same problem or maybe solved the problem another way?

Thanks for your replies

Jacek
:confused:Hello, Can you tell me please how I can solve this problem?? Because I have the same problem. THanks.
Yolanda

Hi everyone!

When I try to process a cube via DTS or in a Terminal Server window I get the following error message:

DTSRun OnError: DTSStep_DTSOlapProcess.Task_1, Error = -2147221386 (80040076)
Error string: RECORD=1, _ERROR=CANTCONVERTDATA, COLUMN=30..

The cube runs on:
Win 2000 Advanced SP1 - MS SQL 2000 SP 1

We have found that it works when we keep a user with Admin privileges logged on locally on the server.
Well, it is the solution of the problem but it seems to be a bug anyway.

Has anyone got the same problem or maybe solved the problem another way?

Thanks for your replies

Jacek
:confused:

Error: The process could not bulk copy out of table '[dbo].[syncobj_0x3944323636373031]

SQL Server 2000 error message:

The process could not bulk copy out of table '[dbo].[syncobj_0x3944323636373031]'.

Snapshot replication was running fine up until last week. The e drive on the system failed. The server was rebooted and the drive returned. Since then the replication has failed. (e drive is the location of the database and log files)

The database is approx. 12 gig. I googled the error message an found several references to the drive being full. So I tried to map the replication to a different drive. The replication fails with the same error.

Also tried to drop and recreate the replication process. Still the same error.

Any other ideas?

TIA

Joel

You dont have enough permission to write to the destination database
or
you Sql service account dont have right to access the replication snapshot folder

Error: 'table that is required for a join cannot be reached'

Hello all,

I'm running into an odd error when trying to process a newly created dimension, I've looked at the 2 postings with a similar error, but they don't appear to pertain. Here is the error:

Error 1 Errors in the high-level relational engine. The 'dbo_Company_Address' table that is required for a join cannot be reached based on the relationships in the data source view.

Error 2 Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Company', Name of 'Company-Location' was being processed.

Error 3 Errors in the OLAP storage engine: An error occurred while the 'Company' attribute of the 'Company-Location' dimension from the 'datawarehouse' database was being processed.

... processing then fails.

I was running into the same error using a data view with the full database schema and working cubes, so I started over, trying to simplify: I created a new project for a new database, created a new DSV, reduced the number of tables and confirmed that all relationships were correct (I did need to add some relationships where the wizard did not pick up on some links). I confirmed that the DSV did show the relation between dbo.Company and dbo.Company_Address. This was discovered by the DSV wizard. The PK and FK columns both show as int(4) in the DSV, as they should... Next, I created a single dimension using the dbo.Company table. Interestingly, the Dimension wizard did NOT pick up on the FK relation to dbo.Company_Address, though it picked up on two other relations, and added tables and hierarchies. I then added the dbo.Company_Address table to the Dimension (without adding any attributes or attribute relations), and processed the project. This processed OK. Next, I Added the PK (Company_Address_RECID) of the dbo.Company_Address table to the dimension attributes, created an attribute relation for that attribute under the Company attribute, and reprocessed the project. I got the above error again...

The database I am trying to process comes from a third-party .NET application, which means that I can't easily change the underlying DB schema. For reference, the source DB is running under MSSQL 2000 (sp4).

I've also done numerous reports against this database (most using these tables and this pretty basic relation), so I don't think there is an issue with the underlying DB, but you never know.

... Anyone have any ideas?

It is hard to say what is going on.

The error indicates specifically problem with DSV. Not with database , not with the way you define your PK FK relationships in the SQL Server database. It is DVS.

I have seen such errors in the past. And in every case I would first look and will think everything is okay. But eventually this got to be DSV.

Try to simplify your case even more. Have only two tables, create only 2 attributes based on each table.

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

|||Edward,

Thanks for answering.

> The error indicates specifically problem with DSV.

That's the impression I was left with, glad to be able to start narrowing issues...

I went ahead and tried narrowing down possibilities. Here are the results:

Changed DSV to contain ONLY the tables for the dimension (Company, Company_Address, Company_Type, Company_Status). Wizard picked up on relations from all but Company_Address.
|||

Feel free to contact me by removing 'noreply.online.' from my Display E-mail.

I will try take a look at your problem.

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

|||Just updating this posting with the off line discussion so far. Edward has been unable to followup on this issue, and I really do need an answer...

--Jason

---

> --Original Message--
> From: Jpiterak
> Sent: Friday, June 09, 2006 9:29 AM
> To: Edward Melomed
> Subject: Error: 'table that is required for a join cannot be reached'
>
> Hello Edward,
> Again, thanks for taking the time to help with this...
>
> I'm attaching the contents of the .dsv and .dim xml to this email... Let
> me know if there is anything else I can send to help track this down (do
> you need the full project? -- I'd just need to cleanse any login
> info...).
>
> Thanks again,
> --Jason
Edward Melomed wrote:
> Are you having a case when a single company can have several addresses?
>
> One way to deal with this problem is to define single "primary" company
> address. So company and address have one-to-one relationship.
> Create a named query in DSV joining Company and Company address tables.
> Make sure you resolve ambiguity with addresses.
>
> If you absolutely need to maintain several addresses for company, you
> can create separate many-to-many dimension for Company address.
>
> Take a look at the AdventureWorks sample project shipped with the AS
> 2005. It has example of many-to-many dimensions.
>
>
> Edward.
> Analysis Services team. Microsoft Corp.
>


Hi Edward,
Thanks again for getting back to me...

The thing is, the relation between Company and Company_Address isn't
many to many, it's one to many (a company has several addresses, each of
which belong to one and only one company...)
All my other one to many relations seem to work fine, this one is just
plain stubbornly refusing to work.

Is there something in the xml schema files that presents the
co->co_addr relationship as something other than one to many, perhaps?

Anyway, thanks again for taking the time.

Take care,
--Jason

|||

Let me try to take another stab at it and explain it in a different way:

In the relational database you have one-to-many relationship between your company address and your company.

But the way you've defined your dimension, is you've marked your company as a key of the dimension and company address as a related attribute. This is just opposite to how you want to define your dimension.

The key attribute should be most granular attribute in your dimension and all other attributes are less granular.

So many examples... Take a look at the AdventureWorks database. You have Customer dimension there.

Any dimension you look, you see the relationships are one-to-many between attributes and the key attribute in the dimension.

Makes sense?

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