How to check whether file stream is enabled on the SQL Server Instance via T-SQL.

Problem: How to check whether file stream is enabled on the SQL Server Instance via T-SQL.

Solution:

sys.configurations

Under server- wide configuration catalog views, there is a system object view which stores a row for result for server wide configuration option value in system.

This kind of helps when you what state each configuration values or and gives a way to evaluate or change some decisions when a setting is turned or off.

To Know what a configuration option means, please go through all the list of  Server Configuration Options (SQL Server).

Example:

In this example, let’s see how can use configurations to find out the file stream service enabled.

Example 1:

select * from sys.configurations

where name ='filestream access level'

Results:

configuration_id	name	value	minimum	maximum	value_in_use	description	is_dynamic	is_advanced
1580	filestream access level	2	0	2	2	Sets the FILESTREAM access level	1	0

Example 2:

sp_configure 'filestream access level'

Results:

name	minimum	maximum	config_value	run_value
filestream access level	0	2	2	2

Read More

How to force a query to use index hints with in a query to boost the performance.

Problem: How to force a query to use index hints with in a query to boost the performance.

Solution: Table Hint , Starting SQL Server 2008 or higher.

In some cases, you want to override the SQL server query optimizer plans to choose plans.

There are table hints which can force the query optimizer to choose the specific control you want to consider based on you selection. This can sometimes benefit better query plans and show expected behavior instead of the query analyzer decide what indexes to choose for querying the table.

Caution:
Because the SQL Server query optimizer typically selects the best execution plan for a query, we recommend that hints be used only as a last resort by experienced developers and database administrators.

Syntax:

WITH  ( <table_hint> [ [, ]...n ] ) 

<table_hint> ::=  

[ NOEXPAND ] {  

    INDEX  ( index_value [ ,...n ] )  

  | INDEX =  ( index_value )   

Example:

In this example, let’s see how can use with index in the join between two tables based on the indexes you want to use for the selection criteria.

-- Example 1: using with table hints

USE AdventureWorks2014

GO

SELECT p.FirstName+' '+p.LastName as fullname, ph.PhoneNumber

FROM Person.Person p

WITH (INDEX([IX_Person_LastName_FirstName_MiddleName]))

INNER JOIN Person.PersonPhone ph

WITH (INDEX([IX_PersonPhone_PhoneNumber]))

ON p.BusinessEntityID = ph.BusinessEntityID

GO

--Example 2: Using OPTION clause

USE AdventureWorks2014

GO

SELECT p.FirstName+' '+p.LastName as fullname, ph.PhoneNumber

FROM Person.Person p

INNER JOIN Person.PersonPhone ph

ON p.BusinessEntityID = ph.BusinessEntityID

OPTION (TABLE HINT(ph, INDEX ([IX_PersonPhone_PhoneNumber])),

TABLE HINT(p, INDEX ([IX_Person_LastName_FirstName_MiddleName])))

GO

Results:

 

Read More

How to make use of a data mining model.

Problem: How to make use of a data mining model.

Solution: Data Mining Model Viewers

After the data mining structure, has been created, you can explore the model to look for interesting trends. Because the results of mining models are complex and can be difficult to understand in a raw format, visually investigating the data is often the easiest way to understand the rules and relationships that algorithms discover within the data.

Each algorithm that you use to build a model returns a different type of results. Therefore, Analysis Services provides a separate viewer for each algorithm. When you browse a mining model in SQL Server Data Tools (SSDT), the model is displayed on the Mining Model Viewer tab of Data Mining Designer, using the appropriate viewer for the model.

How to Use the Model Viewers

First you select the mining model and then you select a viewer. Each model always has two viewers available: a custom viewer, which can include multiple tabs, and the generic viewer.

Depending on the type of the model that you selected, you will see very different options for exploring the model. The custom viewers associated with each model type are tailored to the algorithm that you used to create the selected data mining model. Each custom viewer has a variety of tools and dialog boxes for helping you explore the statistics and patterns in the model, view charts, or interactively work with probability thresholds or filter out items by name.

Example:

In this example, let’s see how we can use the data mining model that has been created, please go through this article to see how we can create a data mining structure. datamining structure

Step 1: open the solution from visual studio and connect to the mining structure and open the tab of data mining model viewer.

Step 2: toggle the mining model and viewer to change the algorithm for using the data mining to get appropriate view in the results window.

Results:

This is the Microsoft generic content viewer.

This is the Microsoft tree viewer where the probability is calculated based on the tree structure and decision trees.

Read More

How to create a SSAS data mining structure based on an algorithm model.

Problem: How to create a SSAS data mining structure based on an algorithm model.

Solution: Data mining Models

After collecting all the data in a SSAS Cube, based on this data we can create a data mining model based on an algorithm to predict future data. SQL Server Data Mining includes multiple standard algorithms

This is also called machine learning or predictive analytics.

To drive the predictive analysis, you should have datamining structures created on top of the cube dsv.

On the backend, datamining is done by queries based on the patterns and these queries are called DMX.

Example:

In this example, let’s see how to create a data mining model.

Step 0:  Create a dsv for using inside the data mining model.

Step 1: create a new datamining structure by right clicking on the mining structures from the project.

Step 2: now a wizard will be prompted to create a mining structure model and select the mining structure data source.

Step 3: Create mining structure model and from the drop down which technique do you want to use.

Step 4: Select the data source view

Step 5: Select the tables.

Step 6: Select the training data from where you want to input data and predict data and key columns.

Step 7: Specify data types and hit detect and content types.

Step 8: name the mining structure.

Results:

Read More

How to predict and analyze data using SSAS.

Problem: How to predict and analyze data using SSAS.

Solution: Data mining

After collecting all the data in a SSAS Cube, based on this data we can create a data mining model based on an algorithm to predict future data. SQL Server Data Mining includes multiple standard algorithms, including EM and K-means clustering models, neural networks, logistic regression and linear regression, decision trees, and naive bayes classifiers. All models have integrated visualizations to help you develop, refine, and evaluate your models. Integrating data mining into business intelligence solution helps you make intelligent decisions about complex problems.

This is also called machine learning or predictive analytics.

To drive the predictive analysis, you should have data mining structures created on top of the cube dsv.

On the backend, datamining is done by queries based on the patterns and these queries are called DMX.

Key Data Mining Features:

·         Multiple data sources: You can use any tabular data source for data mining, including spreadsheets and text files. You can also easily mine OLAP cubes created in Analysis Services. However, you cannot use data from an in-memory database.

·         Integrated data cleansing, data management, and reporting: Integration Services provides tools for profiling and cleansing data. You can build ETL processes for cleaning data in preparation for modeling, and ssISnoversion also makes it easy to retrain and update models.

·         Multiple customizable algorithms: In addition to providing algorithms such as clustering, neural networks, and decisions trees, SQL Server Data Mining supports development of your own custom plug-in algorithms.

·         Model testing infrastructure: Test your models and data sets using important statistical tools as cross-validation, classification matrices, lift charts, and scatter plots. Easily create and manage testing and training sets.

·         Querying and drillthrough: SQL Server Data Mining provides the DMX language for integrating prediction queries into applications. You can also retrieve detailed statistics and patterns from the models, and drill through to case data.

·         Client tools: In addition to the development and design studios provided by SQL Server, you can use the Data Mining Add-ins for Excel to create, query, and browse models. Or, create custom clients, including Web services.

·         Scripting language support and managed API: All data mining objects are fully programmable. Scripting is possible through MDX, XMLA, or the PowerShell extensions for Analysis Services. Use the Data Mining Extensions (DMX) language for fast querying and scripting.

·         Security and deployment: Provides role-based security through Analysis Services, including separate permissions for drillthrough to model and structure data. Easy deployment of models to other servers, so that users can access the patterns or perform predictions.

Read More

How to access cube data inside Microsoft excel without connecting to server.

Problem: How to access cube data inside Microsoft excel without connecting to server.

Solution:

To browse cube data from excel, please go through this article. browse cube from excel

In some cases, you might want to hop on a flight and do some work offline, like in disconnected mode and create some reports of the data.

Example:

In this example, I would like to show we can browse offline an SSAS Cube.

Step 1: launch Microsoft excel, go to the ribbon from excel and from the analyze tab and select the OLAP Tools and select from the options offline OLAP.

Step 2: In Offline OLAP Settings, from the dialog box hit create offline data.

Step 3: Now a wizard will pop up to select the offline dimensions and facts to operate.

Step 4: select the facts and dimensions to save the data for processing.

Step 5: Select the file to save on a local directory .

Results:

Read More

How to build SSRS reports from SSAS cube data using MDX.

Problem: How to build SSRS reports from SSAS cube data using MDX.

Solution:

After building a cube in SSAS, the next thing to do is create reports of the analysis services cube data.

SSRS is the Microsoft business intelligence reporting tool where reports can be created using t-sql, mdx or dmx queries. To create a report from cube data there are two options either use multi-dimensional language(MDX) or data mining language(DMX).

To learn about how to write MDX queries please go through this article. MDX queries

Also, SSRS provides query designer where drag and drop measures is allowed to build queries on top of a cube.

Options are almost similar to what you get from a T-SQL dataset, creating parameterized datasets, filtered datasets.

Example:

In this example, I would like to show we can create a SSRS report from SSAS Cube.

Step 1: launch visual studio and create an SSRS Project and create new data source to connect with cube.

Step 2: Now create a new report and go through the wizard and select the data source.

Step 3: after selecting the data source, next step is to build the data set by clicking on the query builder.

Step 4: for building the query drag and drop the data fields needed for report.

Step 5: After adding the data set, next add the type of report to show.

Step 6: Select columns rows and details from available fields and click on the drill down features to enable toggling.

Step 7: Next step is to go through designer for formatting all the content in SSRS designer and go to preview results.

Results:

Read More