So, welcome to lap 10 and in lap 10, we will discuss something called as time series in SSIS. So, what exactly is time series? If you see at this moment in our queue we know we can go and see the total customer amount we can go and see the customer amount as per the country, we can go and see the customer amount as for the product you know and so on. But, if you remember in our fact table, we had a column here called as sales date. So, it would be great if I can also see the customer amount as per the month as per the year as per the date right. So, how do we go about deriving such kind of a cube or creating such kind of a cube So, you know Other words, I would love to see you know one more dimension here called as something like dim time right.

And from the dim time you know I can drag and drop the month I can drag and drop the year and see the report as for the year as for the year and the month and so on. So, if you think logically, the first thing what we definitely need is we need a dimension table we need a dimension table in our data warehouse and this dimension table could have the data as follows. For example, over here you can see that we have the sales date so the sales date would be a foreign key. And I would have some table here created called us let us say dim time or dim date whatever you want to say. And in that I will have the date as the primary key. Then probably I will have month I will have your right and must be quarter of the month and so on right.

So, now, the way the data would be in this table is as follows. So, for example, let's take the first date of this right. So, for this date, I will remove the time for now. So, for this date, I will say okay what is the month of this date? Okay Jan, what is the year 2010 and it is the first quarter right in the same way now, let us take this, this date second one 2014 right. So, over here now, this is March and it is 2014.

So, in other words, I need to create this date table you know wherein in one column I will have the date right. And in the other columns, I will have the respective months of the date, the respective year, quarter or whatever, you know other fields you want as per the report, right. But now, the question is that Do we go and create this table because creating this manually is very tedious and without dimensions you cannot achieve anything in that cube right. So, without dimensions you cannot have a report as per this month and year and so on. And for that you know we have something called time series. This time series is a wonderful wizard in wherein you can go and say that okay go and create all the permutations and combinations from the year let us say in this case, if you see, we have the data from 2010 to 2014.

I'm sorry, we have 2015 as well. So, from the date of 2010 till the date of 2015 right year 2015 go and create all the permutation combinations of 365 days with that respective month with that respective week, and whatever you want as per the report and then after that you Just go and build that cube. Okay, so let us go back to our project view Solution Explorer. So you can view Solution Explorer. So in this now remember that this table is a dimension table right, so you need to go and right click. So you need to go and right click on the dimensions folder.

There it is, so you can see the dimensions folder. So you need to right click on the dimension folder and say add a new dimension so that the dimension wizard has started so I'll do a next year. Now you can see that the the same standard wizard which we have seen previously, but at this moment, I want to say that create a time table in the data source. In other words create a time table in the data warehouse where we will have the option limitations and combinations you know from the year of 2010. Let us again go back and see from the year of 2010 to the year of 2015, right. So, I will do next.

So, I'll say okay take from 2010. So January 1 2010 in December 2015 and I want to create the column for a year I want to create the column for a month I want to create a column for the quarter, let us say that's it, okay. So, what will happen now, he will go and create all the permutations and combinations for the 365 days from 2010 to 2015 with these four columns, month, quarter and year right, and it does do a next Now again, you know, these dates you know, can be customized you know, for example, let us say if it is accounting year, so, like in India the accounting year starts from March to March. So you can again, go and say that is this a regular calendar? Is this a fiscal calendar? Is this a manufacturing calendar?

And accordingly No, you can have the dates from the range of that month to the next year's month, right. But at this moment, I will just say it's a regular calendar. And I will do a next. And I will say generate the schema. Now, this is a very important checkbox. So generate the schema.

Now it does what it goes to your SQL Server and creates a table with all the permutations and combinations. And I'll do a finish. Once I do a finish, he says he starts the schema wizard and says that Okay, so I'm going to go to your database and create this table and I'll say, Yes, please do that. But don't create a new data source, you know, use the existing one what I have and create this time table into that database, right. So I'll do a next I'll say yes create the primary keys create the foreign keys and all land and don't just keep an empty structure but populate it with the range what I've given say next and I'll say finish so once I do a finish you know this job this wizard starts the magic and goes and loads goes and creates a table in your database with all the permutations and combinations for for those four years for the 365 days.

So if you go back to our database here, you know my expectation is I should see a table here created so let me go and refresh your this table. Let's see. are still creating created sticking time you can see that at the back end he has created The dimension structure as well that it is generation completed successfully I'm so happy about it. So, the first thing you would see that there is a time dimension automatically created you can see that there is a time dimension automatically created right. And my next expectation is I should also see a timetable here you can see there's a timetable here created. So, let us go and right click on it and let us see you know the data of this table.

And there you can see the magic you know item This is a magic there you can see that this table has been created. So, that is the date that is the calendar year that is a quarter that is a month name you know, great right. So we have all the necessary fields now created and you can see the range here from 2010 onwards, you know, he must have gone till 2015 2015 right. So if you keep scrolling can see 2000 12 if you keep falling below you'll see 2015 as well. Nice All right. So now let us go back to our cube project here.

And let us first go and see the data. So CSV, CSV, right? Because in the DSP, I expect that time dimension to be added here you can see that the time dimension has been added. And frankly, in this time dimension, my expectation is that this sales date will have a foreign key and a primary key relationship, right? This should really work. In other words, whatever date are existing, yours should now also exist in this database, right?

So I'll see if this in this table, I'm sorry, right, I will save this. Great. Now let's go back here to our cube. But now we need to do some couple of things more here. Let us go to the cube here. If you go to the cube here and let us go to the cube structure, right the solution is Solution Explorer has become big cube structure here.

If you see in the cube structure, I don't see the dimension time still added here. You can see that I have those previous dim country dim states you know everything, but I don't see my dimension time added here. So, let us right click on this area and add a cube dimension that is this time dimension right I will say okay. Let me save this. Now, till now, I have covered two tabs in SSIS. One is I have covered the cube structure, you know cube structure is a tab where you can go and see your structure of the cube you can see the dimensions you can see the fact and the browser is the tab where you actually go and see the display right.

Let me introduce to you one more tabular which is very important call as a dimension usage. So what is this dimension used? This dimension usage actually connects the fact and the dimension it tells that how the fact is connected with the dimension for example, if you see our dim salesperson is connected with the fact customer by the by the salesperson ID, if you see the state's table is connected with the fact remember this measure means the fact Remember I said the synonym of measure is fact okay measure means numbers. So, here it says the measure is connected with the dimension by using the states ID and so on. In the same way you can see that you're using that okay this dimension is connected with this date and time as an inner with the with the date column right. So, this dimension usage table is useful, you know, when you want to connect the measure with the dimension, okay.

So I came here in to just ensure that this time dimension is connected with the fact customer right and I can see that yes, he has made the proper connection here and that is good in case this connection is not available for example, let us say for some reason, if this connection was not available like this it was empty like this, then what you need to do you need to go here and say yes there is a regular relationship and the relationship is by this date column. So, in the dimension that is Date column and in the measure that is sales date, right. So, in case if you don't see this connection automatically, then you need to go and set this right. So, there it is So, good. Now, let us go and build our cube I think we are almost done. So, we are going to go and process the cube here.

And the content appears out of date. So, yes, I know, I have made a lot of changes there and it is out of date. And I'd say it's asking for my password. So I Given the administrator password here and there it is starting to process the cube. So, you can see that processing the cube, yes. And I hope that we don't get any errors right.

We know you can get errors, you know in case you do not provide the range of the date properly. In other words, for example, you know, if you see at this moment my data is from 2010 to 2015 by mistake if you skip one of the years, you know, then he will give an error here saying that you have defined a primary key and a foreign key relationship and it does not work right. So, fine. So, at this moment, I had done my things properly, so everything's green. Now, let me go back to my browser here. Right.

Now, one of the things I would like to highlight here as you can get this message here, you can see the cube has been reprocessed on the server. And if you want to go and see the The data on this browser you have to reconnect. So this what happens is a lot of times, you know, you have the browser open, and you process the queue when you come back here, you know, and you and the browser says that, oh, you have done some processing on the cube, you have to go and, and reconnect. So that this display, this browser display here is in sync with what is there on the SSIS cube right. So, for that, you have to go and reconnect here. Either you can reconnect from this link, which is given here, or you can reconnect from this link you can see at the left hand side, right, so either from here or either from this link, okay?

So I will do it from your at this moment. So I'll say reconnect. So the time you do a reconnect, you know, he goes and he reloads the browser again with a fresh view. And the first thing you know what is noticeable here is that you can see your dimension time here. Wonderful, right? So now I can go and say yes, go and show me the total customer amount.

And show me your as for the year that it is go and show me your as for the month that it is. And I can go one step further and say yes go and show me as for the month as for the year and as for the country. Wonderful, right? So the time series in SSRS is nothing but it's a very nice wizard which goes and runs and creates your timetable with all the permutations and combinations what you need to create a report when you have a column like sales state. Now, also, I would like to highlight here one thing about this time dimension, this time dimension, what has been created here is very, very special, right? If you go to this time dimension if you go to this demo time here, right and if you see this dim time has a hierarchy if you see your previous dimensions you know it did not have this for example, if you remember your dim states you know it was like this right and the hierarchies here were empty, but if you see this dim time here, this dim time is having a hierarchy.

So, let us understand that what is the importance of this hierarchy at this moment he has created this hierarchy automatically, you know because of that bizarre this hierarchy has been created, but you can see at the top of the hierarchy is the year and then followed by the quarter and then the month and then the date okay. And in case you want to go and see this hierarchy in a more clear manner, you can go and click on this attribute relationships here. And over here you can see now, there is a date, there is a month there is a quarter and then there is a year. So what is the importance of this hierarchy this hierarchy is used for aggregation aggregation means for example, when you go and drag and drop the year on the on the on the report right it totals up to the year right.

So, it takes up in all the year of 2014 and 15 and totals accordingly, in case you drag and drop the month you know then he will take the totaling up from the month in case you go to the individual dates he will take from the individual dates. So, basically, this hierarchy is created in for aggregation. So, the date amount the per date amount will total up to a month the months total up will be taught will total up to a quarter the quarters total of will total to the year right. So, it has created this hierarchy and because of this hierarchy one Li You know you see something like this for example, if you see here, let me go and clear all grid. So when I say that this is the total amount so you see a single amount here and then later when I go and say okay, tell me as per the year Right.

So for example, when I say year, you can see now that he has taken from the top of the hierarchy and and he has totaled up for 2010 2013 2014. Now the time I drag and drop the month, if I drag and drop the month here, then he totals up to the month so you can see now January's toward individual total is 2024. And then we are again we have a look at the 14 data, March 1000. And, again may 2014 data FEC 1000 plus 1400 totals up to calendar 2014. Right. So this because of this hierarchy, he's able to aggregate you know as for that relationship, so if this relationship was not existing right, then this totaling up is not possible.

Okay. We'll see how to go and create a hierarchical dimensions later on. But at this moment, you know, it has been created automatically. We will have a separate lecture on hierarchical dimensions. So that brings us to the end of the time series lecture. But I would like to quickly talk about some couple of points here around SSIS.

Especially from the point of the end user consumption of the cube. At this moment, you know, whenever we want to see the cube, we are coming to this browser tab here. Okay, so whenever we want to see the report, we come to this browser tab. Now, remember that this browser tab runs under Visual Studio. Visual Studio is a development environment. It is a costly environment.

It is an environment for developers like you. So end users will not come to this browser tab to see the report. Because, you know, think about, you know, end users coming to this Visual Studio and seeing so many menus out here, which will confuse them, right. So the best way for end user connects is by using Excel. So one is you can use SSRS, which we'll talk later on. And the other tool which is the most used tool for viewing for doing calculations is Excel.

So, using Excel, you can connect to SSIS and see your data. Now, very quickly, if you see at this moment, you know, whenever you you are you are going and processing the cube, it was it was actually deploying it into this analysis services here. So, very quickly. Remember our cube name, our cue name was customer data warehouse cube, right? So in this if you go, you will see lots of cubes out there because I'm taking a lot of lectures. Oh, let me check out the name.

So let me go to View Solution Explorer and the name here is SSIS customer, right. So our cube here is this one as the CES customer, the last one right? So, whatever you're doing at this moment is coming inside this cube here. And if you ever want to go and query this cube, remember, you can always go and query using MDX, right. So, I've just clicked on new query MDX. So, so basically the final output is this, the final output is this deployment that should happen, right?

So I can go here and I can see that remember, MDX query is a bit different, but very quickly select from customer data warehouse, and it shows me the total. So basically, the final deployment happens inside this analysis services here, right, the the process and the data is stored here. Now what you can do is you can go and connect from Excel so you can go and open your Excel. So I'm gonna go and open my Excel here. There it is. And in the Excel, you can see that there is a nice tab here called as the Data tab.

So click on this data tab. And you will say that, okay, I want to get data from other sources, right? And from Analysis Services, right, and I will click on this. And you need to go and specify the Analysis Services name here. So I'm going to go and copy this analysis services server from your and I will paste it here. And I will say yes, use Windows Authentication in next.

And he says, Okay, what is the cube name? So my cube name is SSIS customer. And I will say next, and I was a finish. Now, remember that CCS data is multi dimension, right? So if Excel wants to show a multi Dimension Data, the best way it can use is the pivot table. Right?

So in pivot table, you can have a multi dimension view of a data right? So I'll say yes, use the pivot table. And there it is. Now you can see here this Excel is running here very wonderfully. And you can see Okay, show me the customer amount. Right?

Look at the left hand side, okay? Then the country name, product name, right. And also if I want to see by date, I can definitely go and see by date as well. So I'll say okay show me yearly. So, yes, so I can see Okay, take this year and on top here, oh, okay, actually, this is year. So yes year, put it on the column labels that it is.

So you can see that the the end user will not use that Visual Studio browser tab. Okay. He's going to go and use Excel or you know, some other very general software to connect At the end of the day internally SSIS understands MDX. So, you can always use Excel and connect. And the other way to connect to your cube to get the report is by using SSRS So that brings us to the end of this sweet and small 30 minutes video so in this video, we talked about what exactly is time series in SSIS and we also talked about you know, how to go and see your SSIS data into Excel. Now, the next video is on SSIS.

So, as we promised that we will do SSIS we will do SSIS you know, we will put our equal weights into all the three legs of msbi right. So, the next lecture is on SSIS you know, where we will talk about transactions and checkpoints. I hope that you enjoyed this video. Thank you very much. And again request please go to facebook.com slash questpond. Let us know on which lecture you are currently.

What do you feel about this lecture series and do you want to see something extra? Or do you think that you know that can be more effective In this lectures thank you very much