Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to handle the MAXIMUM_STATES warning message in the SSAS Neural Network Data Mining Model


By:   |   Read Comments   |   Related Tips: > Analysis Services Development

Problem

When I process my neural network data mining model in SQL Server Analysis Services (SSAS), I get the following messages in my Process Progress window.

Process Progress Message

When I click on one of the informational messages and then click on View Details, I see the message as shown below. The message says "Informational (Data mining): Cardinality reduction has been applied on column, '' of model, 'NeuralNetwork' due to the large number of states in that column. Set MAXIMUM_STATES to increase the number of states considered by the algorithm."

Message Details

The message is not clear as to which columns are causing the issue. How can I figure out which columns are causing the problem?

Solution

Every mining model in SSAS has Algorithm Parameters. To get to these parameters, click on the Mining Models tab and then right-click on the name of the model. In the image below, I have just right-clicked on the term "NeuralNetwork".

How to get to the Algorithm Parameters

In the Algorithm Parameters window, we can see the MAXIMUM_STATES parameter is set to its Default value of 100. When the Value box is blank, then the Default value is used.

Algorithm Parameters window

We have a few options here. First, we can run a T-SQL query to select the count of distinct values for all of the varchar datatype columns. This might take a while, but it will give us the columns that have a count of distinct values greater than MAXIMUM_STATES. We can then raise the MAXIMUM_STATES value to exceed the largest distinct count. In this example, I have five varchar columns where the count of distinct values exceeds the MAXIMUM_STATES value, which happens to correspond to the number of informational messages displayed in the Process Progress window.

Second, we can choose to Ignore a column. The Resource column is actually a foreign key identifier, so we will ignore it.

Ignore the Resource column

Third, we can see if we have stored our data in the wrong datatype. In our example, latitude and longitude are stored as varchar.

Datatypes shown in Object Explorer

However, we can store these values for latitude and longitude as decimals since they are essentially decimals in our data. The neural network handles continuous values extremely well, so we need to use numeric datatypes as much as possible.

Example of latitude and longitude data

We can change our datatype in our source database using the ALTER TABLE command.

   
alter table [dbo].[tblExample] alter column latitude decimal(9,6)
alter table [dbo].[tblExample] alter column longitude decimal(9,6)

We also need to make sure we update our data source view in Visual Studio. Our data source still thinks the latitude and longitude are of datatype varchar(50).

Data source view needs to be updated with new data types

After replacing the table, we can see that our data source view recognizes the latitude and longitude columns as decimals.

Data source view after updating with new data types

Now we need to go to the mining structure and change the Content and Type for the data types.

Mining structure sees old data types

Change the Type to Double and then change the Content to Continuous as shown below.

Mining structure with new data types

After making the above changes, the informational message goes away when processing the neural network mining model.

Data source view after updating with new data types

As we see in this tip, we have options to eliminating these warning messages and improving our neural network accuracy.

Next Steps

Check out these other tips on data mining in SQL Server Analysis Services.



Last Update:






About the author
MSSQLTips author Dallas Snider Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools