DMX with .Net-Part 1

pad-black-and-white

Predictions always matter; it is always nice to find a pattern in existing data. It will help to have a more accurate decision-making. These days, I am busy with designing and implementing a prototype for tourist recommendation system. I need to use variety of data mining algorithm such as “clustering” “Decision Tree”, “Regression”, “Neural Network”. All of these algorithms accept inputs with different data types. These inputs are the main factors that will affect the predictions.

In tourist recommendation system, I want to find out the needed budget for travel to London based on the previous traveler experience. However, it is not just about the Budget. I also want to have a prediction on Length of travel, mode, cities, and activities.

For example for prediction the budget of travel, many factors will affect such as such as length of travel, accommodation etc. for each prediction I need to have many inputs and prediction items.

I can use SSAS “Microsoft Intelligence” software to do that. However, I need to have that to run the algorithm via my C# .net Code.

I have search and Found the

 Microsoft.AnalysisServices.AdomdClient “

I added this library to my project in reference part.


I am using this namespace on my project


The connection to analysis services is similar to SQL connection,

I have to create an Adomd connection first.

AdomdConnection connection = new
AdomdConnection(“Data Source*******;Catalog=*******”);

Then, I wrote the command string. First, I want to delete all previous created models with the name that I am going to create. Drop Sentence should be like ” Drop MINING STRUCTURE…..name of the structure.

string commandText = “Drop MINING STRUCTURE “ + Modeltype + “Travel_” + ITEM + “_Structure;”;

I put the name of the structure as a variable, because it helps me to be more flexible.

AdomdDataAdapter DMXCmd = new
AdomdDataAdapter();

DMXCmd = new
AdomdDataAdapter();

DMXCmd.SelectCommand = new
AdomdCommand();

DMXCmd.SelectCommand.Connection = connection;

DMXCmd.SelectCommand.CommandText = commandText;

So first, I dropped the mining structure that has been created before. Then I started to create a mining model with a unique name. Creating model is so similar to create a table in SQL. All related attributes should be placed in bracket. They should have data type (for example text for string, long, Boolean,
Double and Date). Each data type supports many content types. Content type is Cyclical, Discrete, Discretized, Key Sequence, Ordered, and Sequence.

Besides that, we can identify that which column is predictable.

commandText = ” CREATE MINING MODEL [“ + Modeltype + “Travel_” + ITEM + “] “ +

“( “ +

“traveller_ID long KEY, “ +

“Year TEXT DISCRETE, “ +

“Quarter TEXT DISCRETE “ + predict_SeasonofTravel + “, “ +

“mode TEXT DISCRETE, “ +

“country TEXT DISCRETE, “ +

“purpose TEXT DISCRETE, “ +

“Age TEXT DISCRETE, “ +

“Sex TEXT DISCRETE, “ +

“Duration TEXT DISCRETE “ + Predict_LenghtofTravel + “, “ +

“Spend long DISCRETE “ + Predict_BudgetofTravel + “, “ +

“Activity Text DISCRETE “ + predict_Activity + “, “ +

“Destination TEXT DISCRETE “ + predict_Destination +

” ) “ +

“USING MICROSOFT_” + Modeltype;

in above example, because I want to have more flexible code, I use the variable to set the predict value to them.

and finally in USING MICROSOFT.. I will put the name of algorithm that I am going to use. Such as clustering, decision tree, ….

DMXCmd = new
AdomdDataAdapter();

DMXCmd.SelectCommand = new
AdomdCommand();

DMXCmd.SelectCommand.Connection = connection;

DMXCmd.SelectCommand.CommandText = commandText;

Finally it’s time to query the minding model that have been created. In the next post I will talk about how to get query from the created model.

 

 

 


Leila Etaati on LinkedinLeila Etaati on TwitterLeila Etaati on Youtube
Leila Etaati
Trainer, Consultant, Mentor
Leila is the first Microsoft AI MVP in New Zealand and Australia, She has Ph.D. in Information System from the University Of Auckland. She is the Co-director and data scientist in RADACAD Company with more than 100 clients in around the world. She is the co-organizer of Microsoft Business Intelligence and Power BI Use group (meetup) in Auckland with more than 1200 members, She is the co-organizer of three main conferences in Auckland: SQL Saturday Auckland (2015 till now) with more than 400 registrations, Difinity (2017 till now) with more than 200 registrations and Global AI Bootcamp 2018. She is a Data Scientist, BI Consultant, Trainer, and Speaker. She is a well-known International Speakers to many conferences such as Microsoft ignite, SQL pass, Data Platform Summit, SQL Saturday, Power BI world Tour and so forth in Europe, USA, Asia, Australia, and New Zealand. She has over ten years’ experience working with databases and software systems. She was involved in many large-scale projects for big-sized companies. She also AI and Data Platform Microsoft MVP. Leila is an active Technical Microsoft AI blogger for RADACAD.

Leave a Reply