another problem using permissions in analysis services

Hello!
I have a problem getting information about accuracy (percentage of the right predictions) of the model using DMX. Is it possible to get information about accuracy of the model using DMX? I didn't find any useful function... My second idea was to build and process the model. And then compare states of the predictable columns of the test data to states that the model predicts on the same data. And count them. That would be the way to get percantage of the right predictions... The problem is that usage of the function COUNT is not allowed? I tried:SELECT COUNT(*) FROM [My Model Name].Cases and it didn't work like in standard SQL...
Is it possible to count rows in DMX? Any idea how to get accuracy (percentage) of the model? I would need this information in my application...
Thanx for any idea,
Ziga

Hello

The simplest way to get the percentage of right predictions is to use the Mining Accuracy Chart. Both the lift chart and the classification matrix can give you good information about the overall accuracy.

You can also use SQL Profiler to record the queries sent to the server when the charts are computed, so that you can reuse the queries and get the accuracy information programmatically.

COUNT(*) is not supported in DMX. However, given that you are trying to get the number of rows in the training set (SELECT COUNT(*) FROM MODEL.CASES), this can be replaced with

CALL SystemOpenQuery( [DS], "SELECT COUNT(*) FROM SourceTable")

Where DS is the data source you used in creating the mining structure/model and source table is the main table or view used in creating the data source. SystemOpenQuery executes your query directly against the relational data source behind the mining model

Hope this helps,

thanks

bogdan

|||

Bogdan!

Thanx a lot for your advice! I found a way to count rows in a mining model using AdoDataAdapter which is used to pull the rows from the mining model and put them into DataSet where it is possible to count them in my application. But in a way it is a waste of memory space to pull all the data out of the table.

So it is possible to see which DMX statements or stored procedures Analysis services use while processing mining models and counting accuracy charts using SQL Profiler?

Bye,

Ziga

|||

>But in a way it is a waste of memory space to pull all the data out of the table

you are right, this is potentially consuming lots of memory. Have you tried my suggestion, executing

CALL SystemOpenQuery([Data Source], 'SELECT Count(*) FROM SourceTable' ?

This should work.

An even simpler solution: execute a SELECT * FROM YourModel.CONTENT. The first content node, usually labeled 'All' should contain statistics about the attributes and their states. You can fiure out the number of rows in the training set either by inspecting the NODE_SUPPORT column of the first node or by summing the support for all states of either attribute in the NODE_DISTRIBUTION column.

> So it is possible to see which DMX statements or stored procedures Analysis services use while processing mining models and counting accuracy charts using SQL Profiler?

Yes, just start the Profiler, connect to your server and then play around with the viewers, particularly with the Accuracy Chart tool. In the profiler, you will capture all the statements sent to the server

Hope this helps

thanks,

Bogdan Crivat SQL Server Data Mining|||

Hello!

Thank you for taking your time...

I tried:

CALL SystemOpenQuery([Data Source], 'SELECT Count(*) FROM SourceTable' )

Actually it didn't work. It looks like the COUNT function is not supported here.

The worning was: The server sent an unrecognizable response

But this statemen without count worked:

CALL SystemOpenQuery([Data Source], 'SELECT * FROM SourceTable'

Aniway, I found the information about nodes using: SELECT * FROM YourModel.CONTENT

Thanx a lot,

Ziga

|||

Hi again!
I have another problem. Permission problems in Analysis services using DMX statement.
In Analysis services I have two databases, DB1 and DB2. Both have Imersonation default. And each database has one data source, first database has data source DS1 and second has DS2. Both of two datasources also have Impersonation default.
When I try to see the content of data using datasources in DMX statement:
1. CALL SYSTEMOPENQUERY([DS1], 'select * FROM table')

2. CALL SYSTEMOPENQUERY([DS2], 'select * FROM table')

The first Query works well and show all data, but the second query does not work.
The error is:
Either the 'DOMA\ziga' user does not have permission to access the 'DS2' object, or the object does not exist.

Object (DS2) surely exist! But I dont know what s wrong with permissions. They' re set just like in the first case which works (default).
Interesting is that both data sources point to the same relational database!

Thanx for any good idea...
Ziga

|||

Hi, Nekdo

Your problem is caused by the permission issue.

To resolve it, first add a role to the Roles directory of targeted Analysis Service Database and check the "read definition" option below the dialog box.

Then, from the membership list item (at top-right corner), choose the account you like.

Finally, click the the data source menu item and make the access of specified data sources to be Read.

Cheers,

Ricky.