"Unable to count the members of the level" error

Hi guys,
I encountering the following error whenever I try to create a new dimension
in the MS Analysis Server.
"Unable to count the members of the 'Area Code' level.Please check your
table joins."
I have check my table joins, all the valid. Also I am using the snowflake
schema.
How to resolve the issue?
Thanks
Akber.
When you process the dimension, the actual SQL that Analysis Server sends to
SQL Server is shown in the processing window. You can copy this and execute
it yourself in Query Analyzer to see if the results you get are what you
expect.
Jacco Schalkwijk
SQL Server MVP
"Akber" <Akber@.discussions.microsoft.com> wrote in message
news:92E7058A-4C42-481E-8179-ABACF327271E@.microsoft.com...
> Hi guys,
> I encountering the following error whenever I try to create a new
> dimension
> in the MS Analysis Server.
> "Unable to count the members of the 'Area Code' level.Please check your
> table joins."
> I have check my table joins, all the valid. Also I am using the snowflake
> schema.
> How to resolve the issue?
> --
> Thanks
> Akber.
|||You might also consider enabling the system-wide processing log file. This
is a best practice with Analysis Services.
To enable it, bring up Analysis Manager. Right-click on the server and
select "Properties..."
Then go to the "Logging" tab. Enable the processing log file and specify
some well known location, e.g. C:\OLAP_Processing.txt
Then all of the SQL Statements, begin/end times, # of records processed,
etc. -- everything that comes out in the processing dialog box -- will also
be written to the system-wide processing log file. This allows you to keep
long-term history of what was processed, when, and by-whom.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:ugLq4LqhEHA.3548@.TK2MSFTNGP09.phx.gbl...
> When you process the dimension, the actual SQL that Analysis Server sends
to
> SQL Server is shown in the processing window. You can copy this and
execute[vbcol=seagreen]
> it yourself in Query Analyzer to see if the results you get are what you
> expect.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Akber" <Akber@.discussions.microsoft.com> wrote in message
> news:92E7058A-4C42-481E-8179-ABACF327271E@.microsoft.com...
snowflake
>
|||and whats appends if you create the same SQL query in the query analyzer,
does the same query (with the same join) return something?
you can catch the query through the log or through the SQL Server profiler.
also, does your tables have some data ?
another way to understand what AS do is to put youself some value in the
dimension count property of your dimension level (put 10 for example)
then process the dimension and you'll see (in the process dialog box) again
the query used by AS to retreive the database values.
"Akber" <Akber@.discussions.microsoft.com> a crit dans le message de news:
92E7058A-4C42-481E-8179-ABACF327271E@.microsoft.com...
> Hi guys,
> I encountering the following error whenever I try to create a new
> dimension
> in the MS Analysis Server.
> "Unable to count the members of the 'Area Code' level.Please check your
> table joins."
> I have check my table joins, all the valid. Also I am using the snowflake
> schema.
> How to resolve the issue?
> --
> Thanks
> Akber.

"Unable to count the members of the level" error

Hi guys,
I encountering the following error whenever I try to create a new dimension
in the MS Analysis Server.
"Unable to count the members of the 'Area Code' level.Please check your
table joins."
I have check my table joins, all the valid. Also I am using the snowflake
schema.
How to resolve the issue'
Thanks
Akber.When you process the dimension, the actual SQL that Analysis Server sends to
SQL Server is shown in the processing window. You can copy this and execute
it yourself in Query Analyzer to see if the results you get are what you
expect.
Jacco Schalkwijk
SQL Server MVP
"Akber" <Akber@.discussions.microsoft.com> wrote in message
news:92E7058A-4C42-481E-8179-ABACF327271E@.microsoft.com...
> Hi guys,
> I encountering the following error whenever I try to create a new
> dimension
> in the MS Analysis Server.
> "Unable to count the members of the 'Area Code' level.Please check your
> table joins."
> I have check my table joins, all the valid. Also I am using the snowflake
> schema.
> How to resolve the issue'
> --
> Thanks
> Akber.|||You might also consider enabling the system-wide processing log file. This
is a best practice with Analysis Services.
To enable it, bring up Analysis Manager. Right-click on the server and
select "Properties..."
Then go to the "Logging" tab. Enable the processing log file and specify
some well known location, e.g. C:\OLAP_Processing.txt
Then all of the SQL Statements, begin/end times, # of records processed,
etc. -- everything that comes out in the processing dialog box -- will also
be written to the system-wide processing log file. This allows you to keep
long-term history of what was processed, when, and by-whom.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:ugLq4LqhEHA.3548@.TK2MSFTNGP09.phx.gbl...
> When you process the dimension, the actual SQL that Analysis Server sends
to
> SQL Server is shown in the processing window. You can copy this and
execute
> it yourself in Query Analyzer to see if the results you get are what you
> expect.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Akber" <Akber@.discussions.microsoft.com> wrote in message
> news:92E7058A-4C42-481E-8179-ABACF327271E@.microsoft.com...
snowflake[vbcol=seagreen]
>|||and whats appends if you create the same SQL query in the query analyzer,
does the same query (with the same join) return something?
you can catch the query through the log or through the SQL Server profiler.
also, does your tables have some data ?
another way to understand what AS do is to put youself some value in the
dimension count property of your dimension level (put 10 for example)
then process the dimension and you'll see (in the process dialog box) again
the query used by AS to retreive the database values.
"Akber" <Akber@.discussions.microsoft.com> a crit dans le message de news:
92E7058A-4C42-481E-8179-ABACF327271E@.microsoft.com...
> Hi guys,
> I encountering the following error whenever I try to create a new
> dimension
> in the MS Analysis Server.
> "Unable to count the members of the 'Area Code' level.Please check your
> table joins."
> I have check my table joins, all the valid. Also I am using the snowflake
> schema.
> How to resolve the issue'
> --
> Thanks
> Akber.

"Concatenate Null Yields Null"

Thanks to the good graces of members I have found the COALESECE() FUNCTION,
and it should fix my problem but still, I'm confused.
I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
question and according to what I read in the Documents on Line for Sql Server
2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
Sadly it returns a NULL value. What gives'
--
Ed Warren> I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
> question and according to what I read in the Documents on Line for Sql
> Server
> 2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
> Sadly it returns a NULL value. What gives'
Current session settings override default settings specified at the database
or instance level. CONCAT_NULL_YIELDS_NULL ON is turned on by default when
connecting via OLE DB or ODBC. This includes SQL Server Management Studio.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed Warren" <EdWarren@.discussions.microsoft.com> wrote in message
news:3CFDF4E6-0E54-480B-A274-4C7ADDF15425@.microsoft.com...
> Thanks to the good graces of members I have found the COALESECE()
> FUNCTION,
> and it should fix my problem but still, I'm confused.
> I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
> question and according to what I read in the Documents on Line for Sql
> Server
> 2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
> Sadly it returns a NULL value. What gives'
> --
> Ed Warren|||Thanks for the enlightenment. That's what is happening. Now how does one
set the value for an (OLe DB session). From the caller or can this be done
on the server side (by setting options for the user', session). I must
admit I'm new to SQL server 2005 and have overlooked many of the 'gottchas'
Again, thanks.
--
Ed Warren
"Dan Guzman" wrote:
> > I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
> > question and according to what I read in the Documents on Line for Sql
> > Server
> > 2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
> >
> > Sadly it returns a NULL value. What gives'
> Current session settings override default settings specified at the database
> or instance level. CONCAT_NULL_YIELDS_NULL ON is turned on by default when
> connecting via OLE DB or ODBC. This includes SQL Server Management Studio.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ed Warren" <EdWarren@.discussions.microsoft.com> wrote in message
> news:3CFDF4E6-0E54-480B-A274-4C7ADDF15425@.microsoft.com...
> > Thanks to the good graces of members I have found the COALESECE()
> > FUNCTION,
> > and it should fix my problem but still, I'm confused.
> >
> > I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
> > question and according to what I read in the Documents on Line for Sql
> > Server
> > 2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
> >
> > Sadly it returns a NULL value. What gives'
> >
> > --
> > Ed Warren
>|||You'll need to set SET CONCAT_NULL_YIELDS_NULL OFF from the client
application after connecting using ODBC/OLE DB. It can be set on the server
side for the duration of a stored procedure execute by including the SET
statement in the proc. However, as you hinted in your original message, the
proper way to handle NULLs is with COALESCE, et. al.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed Warren" <EdWarren@.discussions.microsoft.com> wrote in message
news:B59289CA-EA9A-413A-9290-9D12D2C772D0@.microsoft.com...
> Thanks for the enlightenment. That's what is happening. Now how does one
> set the value for an (OLe DB session). From the caller or can this be
> done
> on the server side (by setting options for the user', session). I must
> admit I'm new to SQL server 2005 and have overlooked many of the
> 'gottchas'
> Again, thanks.
> --
> Ed Warren
>
> "Dan Guzman" wrote:
>> > I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
>> > question and according to what I read in the Documents on Line for Sql
>> > Server
>> > 2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
>> >
>> > Sadly it returns a NULL value. What gives'
>> Current session settings override default settings specified at the
>> database
>> or instance level. CONCAT_NULL_YIELDS_NULL ON is turned on by default
>> when
>> connecting via OLE DB or ODBC. This includes SQL Server Management
>> Studio.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Ed Warren" <EdWarren@.discussions.microsoft.com> wrote in message
>> news:3CFDF4E6-0E54-480B-A274-4C7ADDF15425@.microsoft.com...
>> > Thanks to the good graces of members I have found the COALESECE()
>> > FUNCTION,
>> > and it should fix my problem but still, I'm confused.
>> >
>> > I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
>> > question and according to what I read in the Documents on Line for Sql
>> > Server
>> > 2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
>> >
>> > Sadly it returns a NULL value. What gives'
>> >
>> > --
>> > Ed Warren