"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.

"order by" is slowing page load

hi guys,
the following test script works fine and displays a list of cars from the fairly small database, but if I specify the sort order in the querystring, the page takes ages to display and usually times out. Can someone look over it please and tell me where I can fine-tune it for performance or redundant code?
thanks
M

<%@.LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
Dim oRS,oConn,myOrder,strSQL

Set oRS = Server.CreateObject("ADODB.Recordset")
Set oConn = Server.CreateObject("ADODB.Connection")
'next, a couple of test lines to prevent timeout (seems to have no effect)
oConn.CommandTimeout = 0
Server.ScriptTimeout = 0

Set strOrder = Request.QueryString("Order")

oConn.ConnectionString = "Provider=MSDASQL;DRIVER=SQL Server;SERVER=address;UID=userID;PWD=password;DATA BASE=name"
oConn.Open
%>

</head>

<body>
<%
strSQL = "Select make,model,price from vehicles where cat = 'car' AND active = 'yes'"
if strOrder <> "" then
strSQL = strSQL & " ORDER BY " & strOrder
end if

oRS.Open strSQL, oConn, 2, 3

oRS.moveFirst
Do while not oRS.eof

make = oRS("make")
model = oRS("model")
price = oRS("price")


%>
<%=make%> <%=model%> <%=price%><BR>
<%
oRS.MoveNext
loop

oRS.close
set oRS= nothing
oConn.close
set oConn=nothing
%>
</body>
</html>well you could use a bubble sort to handle the sort in the app code (where presentation issues should be handled) or you could index your order by column.|||thanks, urm...what's a bubble sort and how do I do it?
and how do I create an index?
the columns to sort by are text and updated regularly by the CMS.

I have Googled indexing SQL Server and cursor types, but all the stuff I find assumes an already high level of understanding which is really annoying (the opposite of a Microsoft help file which states the obvious - "this a whim whom slurping valve, use it to slurp whim whoms...") sorry, I digress.|||Yeah? Go read the IBM or Oracle Manuals....

In any case, are we talking about SQL Server in the first place?

Do you have a dba or are you "it"

And why aren't you using stored procedures?|||This nifty asp you've written is wide open to sql injection. Do you know what that is?|||Oh God, Oracle manuals are the worst...|||I think the bubble sort thing was a little joke by Sean that went over your head. :)

bubble sort is a sorting algorithm, and one of the least efficient ones. it's a CS 101 kind of algorithm. search for it on google, but don't use it. In fact most pages you find that describe it should tell you not to use it.|||Can you elaborate on the sql injection problem please?:o
I have had some scum bags trying to put their smutt into my database before.

Brett, what do you mean by "Do you have a dba or are you "it""
Feel free to direct me in the right direction|||Who administers your database?

Like, who creates tables for eaxmple?|||Can you elaborate on the sql injection problem please?:o
I have had some scum bags trying to put their smutt into my database before.http://www.rockyh.net/AssemblyHijacking/AssemblyHijacking.html

Gist is - if you concatenate strings from user input and submit to the database for execution and you are wide open to SQL Injection. Parameterise your data accces (don't try cleaning your strings).|||Parameterise your data accces (don't try cleaning your strings).

Why can't we clean up strings?
I've always used simple replace commands to stop SQL injection in my Access projects...|||Why can't we clean up strings?
I've always used simple replace commands to stop SQL injection in my Access projects...

Oh yeah...like what?|||replacing apostrophes, equals signs, asterix' and percentages...

Eg. replace(<fieldname>,"'","")|||Great..parse this

DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM Orders GO DROP DATABASE dbname'
EXEC(@.sql)|||Sounds like a problem around tempdb. Query the waittype value in the sysprocesses table, to see what this is hanging up on. Your tempdb is not set to autoshrink, is it?|||DECLARE @.sql varchar(8000)
SELECT @.sql = 'DECLARE @.x varchar(8000) '
SELECT @.sql = @.sql + 'SET @.x =
CHAR(68)
+CHAR(82)
+CHAR(79)
+CHAR(80)
+CHAR(32)
+CHAR(68)
+CHAR(65)
+CHAR(84)
+CHAR(65)
+CHAR(66)
+CHAR(65)
+CHAR(83)
+CHAR(69)
+CHAR(32)
+CHAR(78)
+CHAR(111)
+CHAR(114)
+CHAR(116)
+CHAR(104)
+CHAR(119)
+CHAR(105)
+CHAR(110)
+CHAR(100)
SELECT @.x'

EXEC(@.sql)|||suffice it to say that scrubbing strings will address only the most elementary injection attacks.

the real fix is: NEVER execute sql that was pasted together based on user input. if you do this, you are vulnerable no matter how tricky your parsing code is.|||Great..parse this

DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM Orders GO DROP DATABASE dbname'
EXEC(@.sql)
Ahh clever... I did say I was using access but I never considered this|||Great..parse this

DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM Orders GO DROP DATABASE dbname'
EXEC(@.sql)

actually, that statement would probably fail because of the GO. :)

remove the GO and you are in business.|||I think the bubble sort thing was a little joke by Sean that went over your head. :)

you guys ruin all of my fun. i am too busy these days to lead the posters around by the nose for several posts anyways.|||Haha I remember learning (not to) use bubble sorts 2 years ago... Man those were long lessons!

Imagine your old, grey, chunky, droning computing teacher mumbling on for an hour and a half just to turn round and go "..and that's why you won't ever use this" >:(|||bubble sort is actually ok if you only have a few items to sort. in practice even the most hairbrained algorithm will be fast if there are only 10 items.

as soon as you have 100k to sort though, your code will fall over if you use it.|||Thanks jezemine
is it likely that my small database of 30 or 40 cars with descriptions and specs will slow to the point of timeouts when I ask it to order by 'price' for instance?
my DB seems to have suddenly done this, could it be thatthe amount of data has suddenly broken some threshold? or as I suspect, could 1and1 have changed my DB at a low level and cripled its sort capabilty. It was fine last week.
NB: please keep it simple, all this archane terminology is just making matters worse for my little brain!|||I don't think so...

http://www.autotrader.com/fyc/searchresults.jsp?num_records=&search_type=both&distance=10&address=07052&style_flag=1&make=ASTON&model=&make2=&start_year=1981&end_year=2008&min_price=&max_price=&transmission=&engine=&drive=&doors=&fuel=&max_mileage=&color=&keywords_display=&sort_type=priceDESC&body_code=0&certified=&advanced=&default_sort=priceDESC&keywordsrep=&keywordsfyc=|||actually, that statement would probably fail because of the GO. :)

remove the GO and you are in business.

Ummm, no

DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM Orders GO SELECT * FROM Employees'
EXEC(@.sql)|||I think you can always substitute a semicolon for the GO, anyway.|||Guys...what are you talking about?
what does this do?

DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM Orders GO SELECT * FROM Employees'
EXEC(@.sql)

- is this part of the thread or are you off on a tangent?|||Ummm, no

DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM Orders GO SELECT * FROM Employees'
EXEC(@.sql)

I stand corrected!

I was under the impression that "GO" was only understood by the client tools like osql/sqlcmd/QA as a batch separator. apparently it's also understood by EXEC and sp_executesql.|||Guys...what are you talking about?
what does this do?

- is this part of the thread or are you off on a tangent?
They are off on a semi-tangent, regarding whether the code you are using is safe from SQL Injection attacks.|||Update: it was 1and1 Internet, (again) their DB server was down.
Note to self: ditch them.
Thanks for you help folks, although now, I am totally paranoid that i'm going to be injected.
I guess I should be...|||Update: it was 1and1 Internet, (again) their DB server was down.
Note to self: ditch them.
Thanks for you help folks, although now, I am totally paranoid that i'm going to be injected.
I guess I should be...

Ummmm...you...will be...you will...be...

In any case there are ways...but you need control, you must learn control

Use stored procs access only and you'll be fine|||a poorly written proc is just as vulnerable to sql injection. consider this proc:

create proc ExecSql (@.sql varchar(max)) as exec(@.sql)

"Count" formula giving me a fit!

You guys solved my problem last time so I'm counting on you again! :D

I want to count the number of times the word "CAN" appears on my report. So I wrote a formula that is simply this:

Count (["CAN"])

However, it continually returns the value "1" no matter how many times the word is shown on the report. Like it's a boolean or something.

Don't know if this matters, but the field it is counting up is also a formula. Here is that forumla: If {fieldname}=<-0 then "CAN"

I've tried everything, can you please help??Count (["CAN"]) won't work because it will count the number of array elements, but "CAN" is the only element.

Try creating a new formula
if {fieldname}=<0 then 1

and then creating subtotal for this formula with SUM operation.|||That worked great!! Except my formula couldn't have the = sign directly after the field name.

So my formula is:

if {fieldname}<=0 then 1

Thank you greatly!

"complex" SQL and bulk mailing

Well, you guys probably don't think this is very complex SQL :) , but it's taken me ages and it's STILL not working right. I submit the following:

CREATE PROCEDURE forum_readmail AS
Declare @.hMessage varchar(255),@.msg_id varchar(255),@.MessageText varchar(8000),@.message varchar(8000),@.MessageSubject varchar(8000),@.subject varchar(8000),@.Origin varchar (8000),@.originator_address varchar(8000)
EXEC master.dbo.xp_findnextmsg @.unread_only='true',@.msg_id=@.hMessage OUT

WHILE @.hMessage IS NOT NULL
BEGIN
exec master.dbo.xp_readmail @.peek='true',@.msg_id=@.hMessage,@.message=@.MessageTe xt OUT,@.subject=@.MessageSubject OUT,@.originator_address=@.Origin OUT
IF ((SELECT COUNT(*) FROM forum_users WHERE email=@.Origin)=1)
BEGIN
IF (CHARINDEX('(ThreadID=', @.MessageSubject)>0) -- IF REPLYING TO EXISTING THREAD
BEGIN
DECLARE @.existingID int, @.em1 varchar(100), @.bdy1 varchar(8000), @.sbj1 varchar(500), @.usr1 int, @.mySubject varchar(500)
SELECT @.existingID=CAST(SUBSTRING(@.MessageSubject, (CHARINDEX('=', @.MessageSubject)+1), (CHARINDEX(')', @.MessageSubject)-(CHARINDEX('=', @.MessageSubject)+1))) AS int)
SELECT @.mySubject=subject FROM forum_threads WHERE id=@.existingID
-- GET USERS ID FROM EMAIL
declare user_cursor1 cursor for
SELECT id from forum_users WHERE email=@.Origin
open user_cursor1
fetch next from user_cursor1
into @.usr1
while @.@.FETCH_STATUS=0
begin -- INSERT INTO FORUM_POSTS
INSERT INTO forum_posts (body, thread_id, user_id) VALUES (REPLACE(@.MessageText, CHAR(13)+CHAR(10), '<br>'), @.existingID, @.usr1)
fetch next from user_cursor1
into @.usr1
end
close user_cursor1
deallocate user_cursor1

--GET EMAIL LIST
declare em_cursor1 cursor for
SELECT email FROM forum_users WHERE email_option='yes'
open em_cursor1
fetch next from em_cursor1
into @.em1
while @.@.FETCH_STATUS=0
begin --CREATE EMAIL
select @.sbj1=@.Origin+': '+@.mySubject+' (ThreadID='+cast(@.existingID as varchar)+')'
select @.bdy1='User '+@.Origin+' has replied to a thread on the test forum. The post reads:'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+@.Messa geText
exec master.dbo.xp_sendmail
@.recipients=@.em1,
@.message=@.bdy1,
@.subject=@.sbj1
fetch next from em_cursor1
into @.em1
end
close em_cursor1
deallocate em_cursor1
exec master.dbo.xp_deletemail @.msg_id=@.hMessage
END
ELSE -- IF NEW THREAD
BEGIN
DECLARE @.newID int, @.em2 varchar(100), @.bdy2 varchar(8000), @.sbj2 varchar(500), @.usr2 int

-- INSERT INTO FORUM_THREADS
INSERT INTO forum_threads (subject) VALUES (@.MessageSubject)
SELECT @.newID=@.@.IDENTITY

-- GET USERS ID FROM EMAIL
declare user_cursor2 cursor for
SELECT id from forum_users WHERE email=@.Origin
open user_cursor2
fetch next from user_cursor2
into @.usr2
while @.@.FETCH_STATUS=0
begin -- INSERT INTO FORUM_POSTS
INSERT INTO forum_posts (body, thread_id, user_id) VALUES (REPLACE(@.MessageText, CHAR(13)+CHAR(10), '<br>'), @.newID, @.usr2)
fetch next from user_cursor2
into @.usr2
end
close user_cursor2
deallocate user_cursor2
--GET EMAIL LIST
declare em_cursor2 cursor for
SELECT email FROM forum_users WHERE email_option='yes'
open em_cursor2
fetch next from em_cursor2
into @.em2
while @.@.FETCH_STATUS=0
begin
select @.sbj2=@.Origin+': '+@.MessageSubject+' (ThreadID='+cast(@.newID as varchar)+')'
select @.bdy2='User '+@.Origin+' has started a new thread on the test forum. The post reads:'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+@.Messa geText
exec master.dbo.xp_sendmail
@.recipients=@.em2,
@.message=@.bdy2,
@.subject=@.sbj2
fetch next from em_cursor2
into @.em2
end
close em_cursor2
deallocate em_cursor2
exec master.dbo.xp_deletemail @.msg_id=@.hMessage
END
END
SET @.hMessage = NULL
EXEC master.dbo.xp_findnextmsg @.unread_only='true',@.msg_id=@.hMessage OUT
END
GO

This runs as a scheduled job every 10 minutes. There are about 600 users in the 'forum_users' table - which our experiments indicate it should take about 3 minutes to email.

It seems to be having problems, though - it sits continually in an "Executing job" state, even sometimes when there aren't any emails in the mailbox to read. And sometimes just fails completely - won't read anything.

What could be causing this? Where should I start? Is the code as clean and swift as it can be? Should I be especially vigilant of invalid email addresses? Might we be having network problems? Should I not even be thinking of using SQLMail to deal with large (ie: absolute maximum of about 1500 users) volumes of email both to read and send out?

Help. My fingers are bleeding and my boss is going to beat me.Right, we've done some more testing. It seems that setting it to read all emails (not just the unread ones) and not to "peek" at them (ie: leave them as unread) stops it stitting there in an "executing" state. But there are still problems.

Firstly, it fails every other time. It's like this:
I delete every message in the mailbox
I send it a single email
I start the job - it immediately switches to "Failed".
I start the job again - it spends a few seconds on "Executing" (there are about 100 email addresses to send emails to) and finally switches to "Successful".

It only seems to do this when there's only new messages in the mailbox. I don't understand why.

Secondly - and this is the thing we're hoping to actually test - I have concerns about the volumes that SQLmail can actually cope with. It seems to be able to generate email at about 1000 per minute, but if I've got 1000 users in the database and there's 10 new forum posts for it to process, it's gotta send out 10,000 emails.

Is this a ridiculous thing to expect SQL mail to be able to do?|||Oh, there's one more thing. We've also got a problem with Exchange returning delivery failure notification emails to the mailbox that SQL mail is reading - which can't be helping. Now, we've tried setting Exchange to stop sending the blasted things, but to no avail. So I changed the SP above to include (immediately after the "exec master.dbo.xp_readmail" line), the following:

IF (CHARINDEX('Undeliverable', @.MessageSubject)>0)
BEGIN
exec master.dbo.xp_deletemail @.msg_id=@.hMessage
END

but it very blatantly isn't deleting anything with "Undeliverable" in the subject. Can anyone think why?|||OK, update:

I've got round the error message it was generating, but it's still ignoring this 'if' statement, and I've NO idea why. I've tried various checks, including:
IF (@.MessageSubject LIKE '%Undeliverable%')
and
IF (@.Origin LIKE '%System%')
(where @.Origin is the sender of the email; 'System Administrator')

It's just not deleting the email, and it SHOULD. Does anyone know why?