SSAS Model viewer – mining attributes/features results queries

After you have designed and processed/trained a mining model using data from the underlying mining structure, the mining model is complete and contains mining model content.

You can use this content to make predictions or analyze your data.

To retrieve mining model content, you can create a query against the data mining model.

The easiest way to create a content query is to execute the following DMX statement in SQL Server Management Studio:

SELECT * FROM [<mining model name>].CONTENT

Some of the algorithm specific useful SQL queries are as follows:

Decision_Trees:

Declare @GetNODE_UNIQUE_NAME varchar(max);

Declare @GetNODE_UNIQUE_NAME varchar(max);

Declare @NODE_UNIQUE_NAME varchar(max);

CREATE TABLE #TableNODE_UNIQUE_NAME (NODE_UNIQUE_NAME varchar(max));

set @GetNODE_UNIQUE_NAME = N’SELECT * FROM OPENQUERY(SSASlinkedservername,”SELECT NODE_UNIQUE_NAME FROM [‘ + @ModelName + N’].CONTENT WHERE NODE_TYPE = 2”)’

–Print (@GetNODE_UNIQUE_NAME);

insert into #TableNODE_UNIQUE_NAME

EXEC (@GetNODE_UNIQUE_NAME);
select @NODE_UNIQUE_NAME=NODE_UNIQUE_NAME from TableNODE_UNIQUE_NAME –Print (@NODE_UNIQUE_NAME);
drop table #TableNODE_UNIQUE_NAME

Declare @GetModelResults varchar(max);

Declare @GetModelResults varchar(max);

Declare @GetModelResultsForRootNode varchar(max);

set @GetModelResults = N’SELECT *,getdate(),’+@ModelConfigurationKey+N’ FROM OPENQUERY(SSASlinkedservername,”SELECT FLATTENED [MODEL_NAME], [NODE_CAPTION],[NODE_TYPE], (SELECT ATTRIBUTE_NAME, ATTRIBUTE_VALUE, [SUPPORT], [PROBABILITY] FROM NODE_DISTRIBUTION WHERE VALUETYPE <> 1 ) AS t  from [‘ + @Model + N’].content WHERE IsDescendant(””””,””’+@NODE_UNIQUE_NAME+N””’)”)’

set @GetModelResultsForRootNode = N’SELECT *,getdate(),’+@ModelConfigurationKey+N’ FROM OPENQUERY(SSASlinkedservername,”SELECT FLATTENED [MODEL_NAME], [NODE_CAPTION],[NODE_TYPE], (SELECT ATTRIBUTE_NAME, ATTRIBUTE_VALUE, [SUPPORT], [PROBABILITY] FROM NODE_DISTRIBUTION WHERE VALUETYPE <> 1 ) AS t  FROM [‘ + @Model + N’].CONTENT WHERE NODE_TYPE = 2”)’

Advertisements

SSAS model viewer – Feature or Attribute Characteristics vs Discrimination for Naive Bayes Model

When you open a Naïve Bayes model using Browse, the model is displayed in an interactive viewer with four different panes. Use the viewer to explore correlations, and get information about the model and the underlying data.

The purpose of the viewer is to help you explore the interaction between input and output attributes (inputs and dependent variables) that were discovered by the Microsoft Naive Bayes model.

Dependency Network

The first window you’ll use is the Dependency Network. It shows you at a glance which inputs are closely correlated to the selected outcome.

dependency network in Naive Bayes viewer

Explore the dependency network

  1. First, click the target outcome, Yearly Income, which is represented as a node in the graph.The highlighted nodes surrounding the target variable are those that are statistically correlated with this outcome. Use the legend at the bottom of the viewer to understand the nature of the relationship.
  2. Click the slider at the left of the viewer and drag it downward.This control filters the independent variables, based on the strengths of the dependencies. When you drag the slider down, only the strongest links remain in the graph.
  3. After you have filtered the graph, click the button, Copy Graph View. Then select a worksheet in Excel, and press Ctrl+V.This option copies the view that you have selected, including filters and highlighting.

Attribute Profiles

The Attribute Profiles windows gives you a visual indication of how all other variables are related to the individual outcomes.

Explore the profiles

  1. To hide some values so that you can more easily compare outcomes, click the column heading and drag it under another column.attribute profiles in Naive Bayes viewer
  2. Click in any cell to view the distribution of values in the Mining Legend.  Because the attributes associated with different outcomes are displayed visually, it is easy to spot interesting correlations, such as how incomes are distributed by region.

Attribute Characteristics

The Attribute Characteristics view is useful for in-depth examination of a particular outcome variable and the contributing factors.

attribute characteristics in Naive Bayes viewer

Explore the attribute characteristics

  1. Click Value and select an item from the Value.As you select a target outcome, the graph updates to show the factors that are most strongly associated with the outcome, sorted by importance.Note that if you create a model using the Analyze Key Influencers (Table Analysis Tools for Excel) option, you can create models that have more than one predictable attribute. However, all other wizards in the Data Mining add-ins limit you to one predictable attribute.
  2. Click Copy to Excel to create a table, in a new worksheet, listing the scores for all attributes that are related to the selected target outcome.

Attribute Discrimination

The Attribute Discrimination view helps compare two outcomes, or one outcome vs. all other outcomes.

attribute discrimination in Naive Bayes viewer

Explore attribute discrimination

  1. Use the controls, Value 1 and Value 2, to select the outcomes that you want to compare.For example, in this model there were some interesting attributes in the low income group, so we chose the lowest income group in the first dropdown list, and chose All other states in the second dropdown list.The attributes are sorted by order of importance (calculated based on the training data). Therefore, occupation is the factor most closely correlated with income (for this target group, at least),

    To see the exact figures, click the colored bar and view the Mining Legend.

MSDN Reference

SQL Server linked server to CSV file gives error The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’, schema ‘sys’. (Microsoft SQL Server, Error: 229) due to lack of EXECUTE permissions ON SYS.XP_PROP_OLEDB_PROVIDER

We had created the linked server on SQL server to consume CSV files.

You can get details in this post.

The user could consume the files using query:

select * from [TEST-CSV]…[FileName#csv]

Though in object explorer of SQl management studio when try try to browse they use to get error:

The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’, schema ‘sys’. (Microsoft SQL Server, Error: 229)

The solution was to grant EXECUTE permissions ON SYS.XP_PROP_OLEDB_PROVIDER using:

GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO [DomainName\UserName];

or

GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO [LocalUserName];

On SQL Server to create Linked server to CSV file and query to use

For creating linked server to CSV file on a sql server use following syntax:

EXEC master.dbo.sp_addlinkedserver @server = N’TEST-CSV’, @srvproduct=N’CSVFLATFILE’, @provider=N’Microsoft.ACE.OLEDB.12.0′, @datasrc=N’\\FileServer\UNCPath\’, @provstr=N’Text;HDR=Yes’

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’TEST-CSV’,@useself=N’False’,@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’collation compatible’, @optvalue=N’false’
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’data access’, @optvalue=N’true’
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’dist’, @optvalue=N’false’
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’pub’, @optvalue=N’false’
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’rpc’, @optvalue=N’false’
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’rpc out’, @optvalue=N’false’
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’sub’, @optvalue=N’false’
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’connect timeout’, @optvalue=N’0′
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’collation name’, @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’lazy schema validation’, @optvalue=N’false’
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’query timeout’, @optvalue=N’0′
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’use remote collation’, @optvalue=N’true’
GO

EXEC master.dbo.sp_serveroption @server=N’TEST-CSV’, @optname=N’remote proc transaction promotion’, @optvalue=N’true’
GO

And to query use 3 periods/dots as follows:

select * from [TEST-CSV]…[FileName#csv]

SQL Script to get list of linked server on SQL Server

Reference: This is awesome script to get list of linked server on SQL server.

SELECT ss.server_id 
          ,ss.name 
          ,'Server ' = Case ss.Server_id 
                            when 0 then 'Current Server' 
                            else 'Remote Server' 
                            end 
          ,ss.product 
          ,ss.provider 
          ,ss.catalog 
          ,'Local Login ' = case sl.uses_self_credential 
                            when 1 then 'Uses Self Credentials' 
                            else ssp.name 
                            end 
           ,'Remote Login Name' = sl.remote_name 
           ,'RPC Out Enabled'    = case ss.is_rpc_out_enabled 
                                   when 1 then 'True' 
                                   else 'False' 
                                   end 
           ,'Data Access Enabled' = case ss.is_data_access_enabled 
                                    when 1 then 'True' 
                                    else 'False' 
                                    end 
           ,ss.modify_date 
      FROM sys.Servers ss 
 LEFT JOIN sys.linked_logins sl 
        ON ss.server_id = sl.server_id 
 LEFT JOIN sys.server_principals ssp 
        ON ssp.principal_id = sl.local_principal_id

SSAS Mining Structure Data Types

Microsoft Reference

When we create a mining model or a mining structure in Microsoft SQL Server Analysis Services, we must define the data types for each of the columns in the mining structure. The data type tells the analysis engine whether the data in the data source is numerical or text, and how the data should be processed. For example, if our source data contains numerical data, we can specify whether the numbers be treated as integers or by using decimal places.

Analysis Services supports the following data types for mining structure columns:

Data Type Supported Content Types
Text Cyclical, Discrete, Discretized, Key Sequence, Ordered, Sequence
Long Continuous, Cyclical, Discrete, Discretized, Key, Key Sequence, Key Time, Ordered, Sequence, Time

Classified

Boolean Cyclical, Discrete, Ordered
Double Continuous, Cyclical, Discrete, Discretized, Key, Key Sequence, Key Time, Ordered, Sequence, Time

Classified

Date Continuous, Cyclical, Discrete, Discretized, Key, Key Sequence, Key Time, Ordered

You can find details for discrete vs discretized here.

Power BI Report Server on premise version build release info

Microsoft Power BI Reference

  • Power BI Report Server
    • Version 1.1.6582.41691 (Build 14.0.600.442), Released: January 10, 2018
      • Security Updates
      • Bug Fixes
        • Fix for Model.GetParameters returning 400
        • Fix for setting shared data set to existing Paginated Reports (RDL)
        • Fix for ExecutionNotFoundException when exporting report with different parameter values to PDF
    • Version 1.1.6551.5155 (Build 14.0.600.438), Released: December 11, 2017
      • Bug Fixes
        • Failure to save data after refreshing for certain Power BI Desktop reports.
    • Version 1.1.6530.30789 (Build 14.0.600.437), Released: November 17, 2017
      • Bug Fixes
        • Fix for Basic Authentication Scenarios
        • Fix for weekdays were not selectable on schedule page for Subscriptions, Cache Refresh Plans and History Snapshots on Portal
        • For Paginated Reports (RDL), fix for having expressions in Textbox with CanGrow property set to false is resulting in values not showing colors and fonts not being proper
        • For Power BI Reports (PBIX), fix for adding Legends to line chart renders an empty visual
    • Version 1.1.6514.9163 (Build 14.0.600.434), Released: November 1, 2017
      • Bug Fixes
        • Fix for upload reliability problems for PBIX reports over 500MB
        • Fix for data loading issue for PBIX reports over 1GB
    • Version 1.1.6513.3500 (Build 14.0.600.433), Released: October 31, 2017
      • Features
        • Embedded Data Model Support
        • Excel Workbook Viewing (with Office Online Server integration enabled)
        • Scheduled Data Refresh (PBIX)
        • Direct Query Support
        • Large File Support (up to 2 GB)
        • Public REST API
        • Shared Dataset support in Power BI Desktop (via oData)
        • URL Parameter Support for PBIX files
        • Accessibility improvements
  • Power BI Desktop (optimized for Power BI Report Server)
    • Version: 2.51.4885.2501 (October 2017), Released: January 10, 2018
      • Security Updates
    • Version: 2.51.4885.1423 (October 2017), Released: November 17, 2017
      • Bug Fixes
        • Fix for 32-bit Power BI Desktop failing to run on x86 OS
        • For Power BI Reports (PBIX), fix to show x-axis gridlines
        • Other minor bug fixes
    • Version: 2.51.4885.1041 (October 2017), Released: October 31, 2017
      • Features
        • Contains changes required for connection with Power BI Report Server (October 2017)