Data Mining (Fundamentals & Time Series Algorithms)in SSAS

This short article is completely Q & A based on Data Mining in SSAS(SQL Server Analysis Services)where we will discuss fundamentals and Time Series Algorithms. So below are the questions and its proposed answer which will help reader to improve fundamentals of Data Mining in SSAS.

What is Data Mining?

  • Data Mining is nothing but some kind of logic or algorithm running on genuine historical data to bring out some kind of prediction.
  • So Data Mining means mining or hunting from the genuine historical data using some algorithms to do prediction, forecasting, etc. in an intelligent way.

Explain the term “training an algorithm”?

  • The term “training an algorithm” means that whatever algorithm we have with us is dead or not useful. Now to make that algorithm alive or useful that algorithm has to be trained.
  • Training an algorithm means that algorithm is running on a genuine historical data and as soon as it runs on the genuine data the algorithm starts working and puts some mind into it. By running on the data it will try to figure out what will be the future prediction of the data.
  • In short we say that to train an algorithm we have to make that algorithm to run on genuine data and this is how the algorithm gets trained.
  • Now trained means we are artificially putting some intelligence on a dead algorithm so that it can think in terms of that data to do forecasting, predictions, etc.

In what scenarios would we use Time Series Algorithm?

  • The Time Series Algorithm is used in scenarios where we want to do forecasting like about the annual sales, annual sales profit and various other forecasting.

What type of project will we select for Data Mining?

  • For Data Mining we will select our regular Multidimensional project as we used to select the project when we were doing SSAS.

For doing Data Mining Cube is compulsory?

  • Now usually it is not compulsory to have a Cube or create a Cube for doing Data Mining. But professionally in the industry for doing Data Mining Cube is necessary.
  • Because data coming from the Cube will be faster, so as a best industry practice Cube is necessary for Data Mining but it is not compulsory because Data Mining can also be done without Cube.

Explain Sequence Clustering Algorithm?

  • Sequence Clustering Algorithm is a combination of sequence analysis and clustering.
  • It identifies clusters of similarly ordered events in a sequence and these clusters can be used to predict events based on their characteristics.
  • For example there is a shop and some people will call first, then inquire and then go to the shop to purchase, some people see the advertisements and then go to the shop for purchasing & some people go directly to the shop for purchasing. So this algorithm will see which event of purchasing from the shop is most used and do analysis.

Explain Continuous & Key Time Data Types?

  • When we are configuring our mining structure there are two data types named Continuous & Key Time.
  • So Continuous means if we have Sales Amount so continuous data type will be like 100, 100.10, 100.20, 101, 101.5 etc.
  • And Key Time Type means suppose if we have Sales Year then the Key time data type will be like Saturday, Sunday ,Monday, Tuesday, etc. It is kind of a discreet data type.

What is Model in Data Mining?

  • Model is nothing but it is a thought process which will make us understand that how that thing will look like in real world or when it executes. For example if we want to build a car first we will build a model of a car.
  • Now in SSAS we have an algorithm and this algorithm is dead by itself so when this logic runs through the historical data it becomes alive or we can say get trained and have the thought process. And this thought process it uses to predict.
  • So Model means our Algorithm + our Historical Data. So basically it tells us how our algorithm has been trained and does the prediction in an intelligent way.
  • So Model = Algorithm getting trained using Historical data.

What is the query language for Data Mining?

  • The query language for Data Mining is called as DMX query language.

Explain Deviation in Time Series?

  • Deviation in Time Series means at what point the algorithm starting changing the trend or it started making movements.

Our algorithm is not showing the proper trend what can be the reason?

  • The reason for algorithm not showing the proper trend is due to the bad data or we can say not so genuine historical data.

Where does data mining model gets deployed?

  • The data mining model gets deployed in our analysis services in our SQL Server Management Studio.

Can we deploy data mining in tabular?

  • No, we cannot deploy our data mining in tabular.

Explain Predict Function in DMX?

  • Predict function in DMX is nothing but a kind of function used to do prediction on particular column or table using DMX queries.
  • Also this predict function says that what column reference to give and what are the number of items we want on which we want to do prediction. We can also specify the start and the end value so that the prediction can happen in between them.

If you are now feeling bored with huge dose of theory below is the practical SSIS project video which will refresh you and go energetic: –

Posted in MSBI Interview Question, Uncategorized | Tagged , , , , , , , | Leave a comment

MSBI Interview Question’s:- Part 1

We have come up with few real-time questions asked to one of the fresher candidate during MSBI(SSIS, SSAS and SSRS) interview questions if you are one of those who is seeking for the same then below questions and answers will surely help you.

1. Is SQL Server required for MSBI Development?

  • Yes, SQL Server is required for MSBI Development.

2. What components did you use in SSIS package development?

  • We did use many components is SSIS like Conditional Split, Merge, Merge Join, Sort, SCD, CDC, Data Conversion, Execute Task component, Script Task, etc.

3. What is the difference between Merge & Union All?

  • They are both transformations of SSIS. The first difference between them is that Merge can only accept two datasets as input while Union All can accept multiple datasets as input.
  • The second main difference is that Merge requires both datasets to be sorted while Union All does not require sorting of the datasets.

4. What is an MDX query and give its sample query?

  • MDX Query is used to retrieve information stored in an OLAP Cube. It gives us the Multidimensional view of the data. SQL Query can handle only 2 dimensions in a query for processing tabular data whereas MDX Query can process more dimensions in a Query.
  • Example of MDX Query, “Select From [OLAPCubeName];”,

“SelectDimension.Member on Column From [Cube Name];”

5. What do you mean by SCD and explain its types?

  • SCD means Slowly Changing Dimensions. As the name says it is used where our dimensions are not changing that frequently. These dimensions can be our master tables like Country master, Currency master. We know that the countries or currencies do not change frequently so therefore these dimensions are termed as Slowly Changing Dimensions. Now SCD is of three types Type 0, Type 1 & Type 2.
  • Type 0 is known as Fixed Attribute. It means that whenever any changes are detected, it will consider those changes as errors.
  • Type 1 is known as Changing Attribute. It means that whenever any changes are done it will overwrite the new value on the old value.
  • Type 2 is known as Historical Attribute. It means that whenever any changes are done, it will show us both the old value and the new value.

6. How to generate a Cube is SSAS?

  • Cubes are multidimensional database which stores data to do analysis process. A Cube contains a Fact or measure in its center and Dimensions all around it means dimensions act as sides of the cube.
  • So when we create a SSAS project according the project structure first we have to provide the source from where our data or Facts & Dimensions will come. As soon as we do that, in our Data Source Views we see that our SSAS Database diagram is created. Here if we want we can change the relationship also. Remember that our SSAS database and our SQL Database are not linked, it means that any changes we do in our SSAS database it will not affect our SQL Database. For example, we change our relations and our Database diagram of SSAS Database, so it will not change the relations or the database diagram of our SQL Database.
  • After that there is a folder of Cube where on right click comes an option to create cube. Once we select it there will be a wizard opened where we have to configure the cube and follow the wizard step by step and our cube is generated. Inside the wizard it will ask us which Fact and which dimensions we want in our Cube.
  • In this way the Cube will be generated.

7. What are the different project deployment methods is SSIS?

  • There are basically two methods of project deployment in SSIS, they are Project deployment and Package deployment.
  • In project deployment the project is deployed in one go and this technique is preferred way of deploying SSIS project. It was introduced from year 2012. By this method we can deploy our project in SQL Server, MSDB & File System.
  • In package deployment individual package is deployed. This method is used when the whole project is deployed and after that there are some changes to be done on a package or new packages are to be added at that time we use this package deployment method. This method was active till 2008 and with this method we can deploy our project in our MSDB & File System and not in our SQL Server.

8. What do we mean by Primary Key & Foreign Key in SQL Table?

  • Primary & Foreign Key concepts are very important in creating a SQL Table. Primary key means it is a key given to that column which will be unique and non-repeating. Usually Primary key is given to the numbers or Id’s in a table.
  • Foreign Key means it is a key which is related to the primary key. There can be no foreign key without primary key. A table can have many foreign keys but it can have only one primary key.

9. What are the different joins in SQL?

  • There are different types of Joins which are there in SQL when we want to join the records of two or more tables.
  • These joins are Inner Join, Left Outer Join, Right Outer Join, and Full Outer Join.

10. What is the query syntax of all the different joins in SQL?

  • Inner Join:

Select * From Employeetable inner join Departmenttable

ON Employeetable = Departmenttable;

  • Left Outer Join:

Select * From Employeetable left join Departmenttable

ON Employeetable = Departmenttable;

  • Right Outer Join:

Select * From Employeetable right join Departmenttable

ON Employeetable = Departmenttable;

  • Full Outer Join:

Select * From Employeetable full outer join Departmenttable

ON Employeetable = Departmenttable;

Above given questions and answers will serve as last minute revision on MSBI topic or will help as manual to prepare for interview. If you want more learning on MSBI topic will
practical full hands-on MSBI training in Mumbai so if you are from Mumbai city then visit us it will surely help to shape your MSBI career.

If you are not from Mumbai then learn MSBI through self-study learning MSBI project videos series especially for distance learners which has turn-out be good substitute of offline learning. Below is first video which starts with SSIS from that project series: –

Posted in MSBI, MSBI Interview Question, Uncategorized | Tagged , , , , , | Leave a comment

How to perform Script Task and send email using Script Task?

Script Tasks are used to send an email or do any web related tasks where customization is required. In Script Task for doing the customization we have to write C# code. It would be helpful for the MSBI training candidate if they have earlier worked with Visual Studio as writing C# code would require this tool to perform this lab easily.

So first of all we have to create a new package and inside that package we have to drag and drop the Script Task component from SSIS Toolbox.

1

After doing that we have to just double click on the Script Task component to configure it.

2

Now to write the C# code we have to click on option Edit Script and then a new Visual Studio window will open where we will write our C# code.

3

Now we will write the C# code.

4

This is how our Visual Studio window will look like where we have to write the code.

5

publicvoid Main()
		{
// TODO: Add your code here

// Step 1 :  Configure SMTP..
SmtpClient client = newSmtpClient();
client.Host = "smtp.gmail.com";
client.Port = 587;
client.EnableSsl = true;

//Step 2 : Provide Credentials..
string Username = "xyz123@gmail.com";
string Password = "xyz123";

NetworkCredential credential = newNetworkCredential(Username,Password);
client.Credentials = credential;

//Step 3: Configure from address..
MailAddressFromAddress = newMailAddress(Username, "John Doe");

//Step 4 : Create message to be sent..
MailMessage message = newMailMessage();
message.From = FromAddress;
message.To.Add("abc@gmail.com");
message.Subject = "SSIS Script Task";
message.IsBodyHtml = true;
message.Body = "Hello World from MSBI";

//Step 5: Send Email..
client.Send(message);

			Dts.TaskResult = (int)ScriptResults.Success;
		}

This is the C# code written step by step. No worries if candidate of MSBI traininghas never worked with C#,faculty helps to learn, understand and execute this lab.

After writing the code, we will close the window and click OK on our Script Task configuration dialog box.

6

After this done save the Script Task component and then execute the package.

In this way we can send email using Script Task component.

Also get to see following video on MSBI project series which start from the basic to start SSIS : –

Posted in MSBI, Uncategorized | Tagged , , , , , , | Leave a comment

3 things to avoid during Job Interviews By Questpond.

First and foremost avoid the below three things:-

Avoid saying negative about your previous organization because that would send wrong message to the interviewer. Do not create a pessimistic image before the interviewer.On the contrary if you start with positive things like :- “The current company has no issues and I am extremely happy with the current environment but it’s more of a personal problem etc etc…” that would create a very strong and positive image of yours.

Avoid complaining about your current work profile like “It’s a maintenance project, it’s in 1.1 version and I am not learning anything new etc.”. It’s very much possible that the interviewer has a maintenance job. So in such kind of situation’s I have seen the interviewer rejecting people irrespective even though if they have performed well in technical round.

Avoid saying you are jumping only because of salary. Some of the interviewer’s do not like people who just look at monetary benefits. When you are asked about salary expectation these things could be better discussed during that conversation. Do not make it explicit during the interview. Bet me nobody likes greedy professionals:).

Below are some of the good reasons for which the interviewer does not cross question back:-

  • For better prospects and growth.
  • Office is far away I spend lot of time travelling and would like minimize my travelling time.
  • They are telling me to relocate but in my current situation it’s not viable.
  • I am developer but due to project requirement I am in a testing role and so want to switch.
  • Project is getting closed and the company does not have projects.
  • Company is not paying salary on time.
Posted in Uncategorized | Tagged , , , , , , , , | Leave a comment

QuestPond’s Interview Questions & Answers on (SSIS, SSAS and SSRS)MSBI

What are partitions in SSAS?

SSAS let us create cube. Cube is a multi-dimensional database. Data will be stored inside cube as dimensions and Fact Tables.
By default in order to store Fact tables (measure group tables) partitions will be created inside cube.
Be default for one fact table one partition will be created. Partition is simply a physical storage unit inside cube.

Note: End user is nothing to do with partition. Her/she never say, I want data from this or that partition.
For him/her it is always going to be single partition.

Can we span single fact table across multiple partition?

Yes, we can. We can create more than one partition for a single fact table.

Is there anything we have to keep in mind while creating multiple partitions for single fact table?

Yes, we have to make sure that each partition contain unique records.

Advantages of having partition:

  • Each partition can be stored inside a separate physical drive bringing parallel data access into picture.
  • Each partition can have its own aggregation logic.
  • Different storage setting can be defined for each partition. Example – one partition supports MOLAP whereas one supports ROLAP
  • Each partition can be processed independently
  • By keeping historical data and a new data of a particular method in separate partitions processing speed can be improved.

Does partitions store only method group data?

No, in case of aggregating logic is defined, it also stores aggregated data.

Also refer our article on 8 important SQL Server (SSIS, SSAS and SSRS)MSBI Interview questions

For technical trainings on various topics like WCF (Windows Communication Foundation), MVC (Model View Controller), Business Intelligence, Design Patterns, WPF, TFS and Basic fundamentals feel free to contact SukeshMarla@Gmail.com or visit www.sukesh-marla.com

For more stuff like this, click here. Subscribe to article updates or follow at twitter @SukeshMarla

See practical video on explaining SSIS, SSAS and SSRS (part 1) with sample demo: –

Posted in Uncategorized | Tagged , , , , , , , , | Leave a comment

QuestPond’s Interview Questions & Answers on ASP.NET MVC (Model View Controller)

Why MVC (Model View Controller) is loosely coupled?

In order to answer this question, first let’s understand what’s the difference between loosely coupling and tightly coupling is.

Look at the following pic.

p1

When we talk about human body, body parts are tightly coupled to each other. We cannot simply replace hand with some other hand or a leg with some other leg. This is called tightly coupling.
When we talk about computer most of the parts are loosely coupled. Take the RAM out put a new one, same with display, keypad etc. This is called loosely coupling.

In Web Forms User interaction logic is handled inside CodeBehind where as in Asp.net MVC it’s handled inside Controller. Now if we compare Web form Codebehind with MVC Controller biggest difference is, CodeBehind is tightly coupled with view where as controller wont tightly connected to any view.
Single controller can work with more than one view and similarly one view may contain references for more than one controller in form or anchor tag.

p2

For technical trainings on various topics like WCF (Windows Communication Foundation), MVC (Model View Controller), Business Intelligence, Design Patterns, WPF, TFS and Basic fundamentals feel free to contact SukeshMarla@Gmail.com or visit www.sukesh-marla.com

For more stuff like this, click here. Subscribe to article updates or follow at twitter @SukeshMarla

See the following MVC (Model View Controller) video on viewdata, viewbag, tempdata & session:-

 

Click and see here for more step by step training in ASP.NET MVC

 

 

 

 

Posted in Uncategorized | Tagged , , , , , , , | Leave a comment

QuestPond’s Interview Questions & Answers on ADO.NET

What is the use of Command objects?

Command object helps to execute SQL statements. Following are the methods provided by command object:-

  • ExecuteNonQuery: – Executes insert, update and delete SQL commands. Returns an Integer indicating the number of rows affected by the query.
  • ExecuteReader: – Executes select SQL statements which can either be in your .NET code or in a stored procedure. Returns a “Datareader” object.
  • ExecuteScalar: – Executes SQL command and returns only a single value like count,sum , first record etc.

How can we fine-tune the command object when we are expecting a single row?

Again, CommandBehaviour enumeration provides two values Single Result and Single Row. If you are expecting a single value then pass “CommandBehaviour.SingleResult” and the query is optimized accordingly, if you are expecting single row then pass “CommandBehaviour.SingleRow” and query is optimized according to single row.

What are Dataset objects?

Dataset is an in memory object with data tables, rows and columns. You can visualize it as in-memory RDBMS.Dataset has the following features:-

  • The in memory RDBMS works in a disconnected manner. In other words even if the connection is closed the dataset is still there in memory.
  • You can do modification in the in-memory database object and send the final changes to the database.

Below is a simple code snippet which shows how to access a column value. You can see how the full dataset object hierarchy is accessed to get the column value.

objDataset.Tables[0].Rows[0]["CustCode"]

dataset

How can we force the connection object to close after my data reader is closed?

Command method Execute reader takes a parameter called as Command Behavior wherein we can specify saying close connection automatically after the Data reader is close.

PobjDataReader = pobjCommand.ExecuteReader (CommandBehavior.Close
Connection)

See the following video on ADO.NET connection pooling: –

 

Click and see here for more training on ADO.NET

Posted in Uncategorized | Tagged , , , , , , , , | Leave a comment