SQL Server SSAS (Multidimensional MDX) - an Introduction | Phillip Burton | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

SQL Server SSAS (Multidimensional MDX) - an Introduction

teacher avatar Phillip Burton

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

24 Lessons (2h 31m)
    • 1. Introduction

      2:39
    • 2. Downloading SQL Server back engine - the Developer edition

      7:50
    • 3. Install SQL Server back engine

      13:49
    • 4. Installing SQL Server Front Engine

      12:34
    • 5. Downloading AdventureWorks

      2:46
    • 6. Investigating AdventureWorks in SSMS.

      4:56
    • 7. Creating our first project

      3:19
    • 8. Looking at our working environment, and Facts, Measures, Dimensions and Cubes

      7:51
    • 9. Creating a Data Connection

      5:33
    • 10. Creating a Data Source View

      6:25
    • 11. Creating a Cube

      5:58
    • 12. Creating a role, and playing with the cube

      5:34
    • 13. Looking at the cube using Excel

      2:25
    • 14. Adding an extra table, and Creating a dimension

      4:35
    • 15. Updating the cube, and using Excel again.

      5:28
    • 16. Looking at the cube using SSMS

      7:00
    • 17. Looking at the cube in SSRS

      4:43
    • 18. Practice Activity - Let's do it again!

      7:33
    • 19. Practice Activity - The Solution

      7:05
    • 20. Updating dimensions, and creating translations

      12:48
    • 21. Adding a new table into the Data Source View, and replacing it with a query

      5:59
    • 22. Adding a hierarchy - two levels

      6:08
    • 23. Adding a hierarchy - three levels

      7:14
    • 24. Congratulations

      1:15
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

101

Students

--

Projects

About This Class

Welcome to this course on SQL Server SSAS and MDX Cubes – an Introduction.

You may have become experienced with creating SQL statements in SQL Server Management Studio. Building databases is ideal when you want to quickly add data – that’s why they are called OLTP – Online Transaction Processing – they are designed for speed for adding transactions.

But what if you want to get to get information about? OLTP databases are not based designed for this. What you need instead is a process whereby data is pre-aggregated – in other words, a lot of the calculations you may write have been calculated before you ask for them. It saves a lot of time. It would also be useful if the end user didn’t have to bother with SQL queries, and could use something a bit more hands-on, although retaining something more advanced for advanced users. That’s where cubes come in, full of pre-aggregated data, and SQL Server Analytical Services– or SSAS – (Online Analytical Processing) allows you to make these cubes.

This course is designed for the complete beginner in Multidimensional cubes, or someone who wants to refresh their memory. We’ll create a cube to start with from an ordinary database, and then I’ll ask you to create one from a special database known as a Data Warehouse. We’ll export our cube in SQL Server Management Studio, and into SSRS – and we’ll even have a bit of a look at the more advanced way of querying that is MDX.

Meet Your Teacher

Phillip is a Computing Consultant providing expert services in the development of computer systems and data analysis. He is a Microsoft Certified Technology Specialist. He has also been certified as a Microsoft Certified Solutions Expert for Business Intelligence, Microsoft Office 2010 Master, and as a Microsoft Project 2013 Specialist.

He enjoys investigating data, which allows me to maintain up to date and pro-active systems to help control and monitor day-to-day activities. As part of the above, he also developed and maintained a Correspondence Database in Microsoft Access and SQL Server, for viewing job-related correspondence (110,000 pdfs in one job) by multiple consultants and solicitors.

He has also developed expertise and programmes to catalogue and process and con... See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Introduction: and welcome to this coast off SQL Server S S S and M DX cubes. I am Philip Burton off Alcott Stockholders UK. Now you may have become experienced with creating SQL statements in S S M s SQL server Management studio. Boating databases is ideal when you want to quickly add data. That's why they're called oil TP online transaction processing. They're designed for speed for adding transactions, new data. But what if you want to get information out or are two key databases are designed for this ? What you need instead is a process whereby data is pre aggregated. In other words, a lot of the calculations some count average that you may write have been calculated before you even ask for them. If you can imagine, it saves a lot of time. It would also be useful if the engines they didn't have to bother with SQL queries with joins and have ings group buys on could use something a bit more hands on, but the returning something a bit more advanced for Well, if you advanced, that's what cubes coming full of pre aggregated data on SQL Server Analytical Services, or S. S. S, which is a type of oil. AP Online Analytical Processing allows you to make these cubes. This course is designed for the complete beginner. Accuse Also one who wants to refresh their memory. We'll create a cue to start with from an orderly database. And then I'll ask you to create one from a special database known as a data warehouse. We'll explore how cube in S s m s into SS rs on into except and we'll even have a bit of a look at the more advanced way of squaring That is MDX. Now, this is probably not for you. If you haven't used S S m s SQL server managements to deal Know nothing about s Curole guilty. SQL though you don't actually need one single bit of SQL. Just be helpful if he knew some of the terminology beforehand. But why not try it out if you don't, Okay, next lecture will have a look of what we're going to do and who start downloading for free the developer edition of SQL Server. And if you haven't evolved yet, why not look at some of the free previews that got available? Either way, I'll see you in a cost good 2. Downloading SQL Server back engine - the Developer edition: right now. Let's install SQL Server now. It used to be on previous versions of this cost that I suggested you downloaded version called SQL Server Express. And the great thing about it was it was free. The less good thing about it was it wasn't fully. They didn't give come of all of the functions. It came with S S M s and he came of SS. All right. I don't know if you don't know what these acronyms mean at the moment, which was good, but it didn't come of S s. I s especially on Azaz. Yes. So it was a cut down version, but now I can give you Well, let's show you how to install the developer edition of SQL Server. Now you see on here It was previously on sale for around $100 but I could get it for you for free. It's great. So let's see how we can do this. So this is a full version. Almost So if you just Google SQL server additions, we have a link to the official Microsoft websites. You can see how the moment it says max off dot com U S cloud platform ask yourself traditions, so it's click on the link. Andi, you can see that there are four types Enterprise Enterprises and the biggie. It's the one that's really used all of these Mission critical data bases. Standard is a never business type one. There was used to be one in the middle, which was cold business intelligence. It seems to have disappeared in the 2016 Position Express. This was the one I used to have. You installed it waas free. Andi, you could play about with it quite a lot. But now we have developed, unless you have a read about developer if three full featured set of the Enterprise edition . So the Enterprise edition was this big additional is the full version. The only thing is basically for your personal use or even for your business use as long as it's not used in anger. Basically a non production environment so you can click. Learn more throughout to find about it. But that's down now. The first thing you need is a Microsoft account on. If you don't have what that you can click sign up now. I won't do that for real, but just show you what That's like you signed first name, last name, user name, password and all the rest of it create account. It was Sandra an email. You need to click on the email to ensure that you're real person and it's a real email address. Hand Douala your Microsoft account so that you can see it's not intrusive, really in any way. It's working out these patterns. And, of course, you need to have a moderately strong passwords, at least opera case in lower case letters. And please don't use password for password. Want to free. It is one of the most commonly used passwords out there, so I'm just signing using my personal credentials, right? I can see that I need to review the information, so just reduce its not quite right. So Microsoft may use your contact information. Yeah, OK, it's giving me something for free about visual studio, which is interesting because I've asked it to download SQL Server and you could see at the top where in the visual studio Spit and SQL Server development. Sorry, I can't find any Donald's to continue. Please join visual studio Death Essentials. Okay, let's do that. Join visual studio, Deaf Essentials hand again. This is three. So you confirm that right now we're here. We click on visual studio Dead Essentials. Which doesn't make sense, does it? We want to download SQL Server. Not visual studio, but not it's what happens. Yeah, we got all these visual studio off things on top, but then go further down on. We have Microsoft SQL Server developer additional. This Timmy makes absolutely no sense, but it's free, so I don't care. So let's click download now, and that gets you to this page. Now there is a very tempting button here to just down or directly, however less of a reader. What else? There it Because the problem is this doesn't download inexcusable file this downloads and I s all files you can see here. But here we've got an installer which we can use which is provides a quick and easy basic install experience downloading installing the SQL Server injured in just three clicks. Well, that sounds like a really good idea. So don't click this big green button. Just click here to utilize the SQL Server. I'm still out there. We go very quick because all it is, it's a shell. Andi Now he's going to download things now. It's going to take quite a while, obviously, to download the entire lot. But what are we going to do while basic will just get you the really, really basic stuff that is basically as S M. S and SS the administration for a secure server? This is on that. If that's all you want. If you are doing your calls like 70-461 which is all about t SQL Or if you just want to learn about SQL Server Management Studio, that's the one to go for anything else, I would go for the custom. So this goes through the more complex SQL Server installation with it. So here we go. So clicking store is going to download it into that location, so make sure that location is good for you acquiring settle files. Now, if you ever seen any previous versions, you'll see that this is quite a different install window, at least for downloading. Get done in previous versions, but it is going to take our nine minutes on my fairly fast broadband to download. You can see all sorts of interesting message down there now if you did have a problem getting to this page because he saw high to go in and out. So while well, if necessary, go in this page. If you go back to this page, you can always download the express edition If all you want to do is the very basic stuff, which is again s SMS, which is SQL Server Management Studio and also the administration, or least a lot of the administration tasks. But it really is worth a while getting the developer edition because that allows you access to everything that the enterprise conduce. Just licensing is slightly different, so it's going to take another few minutes. Download. So we have a version you choose, Donald Donald it now, and I'll see you in the next video. 3. Install SQL Server back engine: right is now old. Downloaded on. We've got this new dollar box come up the SQL Server Installation Center. Previously, we had this big green button and I told you not to press it. Now we don't have any big green button. And where don't you start? Well, let's minimize every single help her page. So we just have this installation center. Andi, do you think you start on this one or this one or this dance was none of these. We'll go to the left hander tub and we click on installation. So feel free to read all of these, but not essential. Click on installation and click on new SQL Server Standalone installation or to add features in existing installation where we don't have an existing installation. So this is a new stand alone installations. So you click this top one right now, we don't need this dialog box, and so I'm going to minimize this. So first of all this new dollar box tells his wit tradition do you want and you see we've got the choice of evaluation, which is 100 80 day Valuation express, which is a small version whole development developer, as you see it does not have an expiration. It has the same features as evaluation, but his license and non production database application development. Army. So that's what we want. I'm not going to use it in anger on a huge databases just for personal tinkering about, so they're also click. Next. I accept all these license terms. I've read, um, completely yet notice next. And now he's just checking to see whether everything's fine. You can see is already found a product update so the update will be installed. When you click next, that is 228 megabytes. That will take about a minute to install. So I'm just going to lessen still, so I'll just pause the video on dial. Replay it Once it's downloaded, it's to downloading. But I just wanted to take this opportunity to let you know on the left side. This shows why we started from on where we're going. So we did the product key license terms. It's checked rules, it seemed, whether there's any updates and it's now installing the settle fouls, so basically we'll start from the top, work away all the way to the bottom. However, there will be additional items being added. So don't think this is just eight. But you can check the progress of where we are by just roughly by looking on this left inside as we go. Well, it's just now finished Extracting Settle fails. So installing the settle fouls now in progress, this should be fairly swift. I took about 20 seconds, so please wait while it finishes an expert. Andi. Now it's checking some more rules, so we start at the top again. So is checking some more rooms. Andrea festival, which is good. It's give me a warning that the father were using Windows foul. But if I compress next, that's fine. So what do you want to instill on Vos? I want to start everything. Yes, I could just say I just want the very basic stuff. But no, I want to Still everything. So everything gets ticked and notice what happens is left inside a lot. What's more, I did so there's click next and you could see how much hard drive space is Too quiet A gig mines. Okay, so Okay, Next, right. And you can see that it's not actually going to allow us. You can see that if we're going to once of gold Polly base, we would need an article something over. So we want their thoughts, go back on and want to get rid of this Polly base because we don't need it for that where we are, what we're going to do also don't want our services and our server Stand alone. Second, look at the rest of it. The rest of it looks fine. So let's get next The All Services and Polly base and are new to the later versions of SQL Server. So we are instance configuration Andi for basically most the rest of these we can I will only be clicking one button and going next. So this is fine. That's the next. Next was the server configuration. Andi, This style of box is all about permissions and setting up accounts and Kalay Shin Next database entry configuration windows authentication more is good, but hey, we have specifying the administrators on I always click adieu, current user, so that has me as an administrator. So there's lots more fear I'm not going to go into them, at least for now. Click Next Analysis Services. This is if you're going to use S S A s. You have to choose at this stage of the using MDX multi dimensional tabular hold power pivot. I'm just going to keep it at the default. Had three years of current user flick. Next reports services install and configure and still on it. That's fine. Knicks at consciousness remember, Just click. This had Kant user. It's available. Click next control name. Next install, right Go to put this on the right Inside, Some people do like to see it Install another two coming still successfully while it's installing. I want to talk to you about the various versions now Body previously have talked about the fact that there are There's the enterprise version, which is Theseus, Purdue per one standard version which still costs a huge amount of Money Express, which is free on the developer, which used to cost about $100. But now you can see Winstone it for free, so those are the main version nowadays, but I don't talk to you about this number 2016 now SQL Server 2016 is the time of recording the very latest version, but you might be going hike on Philip. I want to use the 2012 version. How I want to use the 2014 version. What festival? Why would you send that? Andi The answer, for instance, if you have a look at the exam 70 Dutch 461 you can see it's called acquiring Microsoft SQL Server 2012 not 2014 but not is what else it says here in December 2015 this exam was evaluated for applicability to both SQL Server 2012 Andi SQL Server 2014 and in fact, the modern version off the actual certificate his queer ing Microsoft SQL Server 2012 slash 2014. And the reason for that is that it's virtually the same at least that the standard that we were looking at in any of these courses, you know, really, really, really high tech. They do some very good things to the internals. Uh, but just brought goes anywhere near them. The closest stepped who come to new features are new features that came in the 2012 edition . Onda, uh, that was a little while ago, So let me just talk to you briefly about the other versions. So 2016 only dispersion Before that was 2014 before that 2012. There was a lot of new functionality in terms of commands that you could use in 2012 that made my life so much easier. Now, if I just go back and this one out, you can see that the years started in the year 2000. There were there was 2000 and five and 2000 and eight on. Then there was one every two years. So my question to you is, What is this missing one? And if you said 2010 it should be. But that's in the name of it. 2000 and eight are to release number two now, right to the minute does this time recording the majority off mainstream uses mainstream database the big data bases in the big businesses. They're using a combination of 2000 and 8 2000 and eight to on 2012. Hardly anybody's using 2016. It's only been out for a few months on. Basically, you won't see any big users use the very latest version. It's been out for you for a year on, probably not even then, because it is a big pain to taken existing big due to base and move it a little bait on Dawson money as well. So this is why most off the work that she could get professionally. He's done. Having said that, we're installing 2016 on. There will be virtually no difference between 2012 and 2016 and there will be very little difference between the 2008 versions on 2016 you what you get to the 2000 and 5 2000 versions, he devil. Quite a few changes, but as it's a very I think less than 1% of business issues 2000 at the moment, for instance. So any information that you learn in these courses don't be put off by the fact that you're using SQL Server 2016. And indeed in these calls is that we'll be using your combination 2012 and 2016 because he's hold the same for why we are for the basic for the intermediate user, you won't notice any difference, so I will be using both 2012 and 2016. Just don't sweat about it, but the major thing is, we're getting a school. So 2016 developer for free, right? Well That's all I'm going to say for this video. As I say, some people do like to see that it's installs successfully, so I'm going to switch like falling off on def. You want to move to the next lecture you can and not let's see it installed, right so you can see one or more affected files have operations pending. You must restart your computer after the process set up is completed. So in other words, it's done. Basically, everything is just got a small thing left to do and you restart the computer. But you could see everything succeeded. So now I will restart my computer. Andi in the next letter will actually run SQL Server. 4. Installing SQL Server Front Engine: Well, now we got the back engine installed. We now have to install the front engine and avoids the bit that you're going to see. So if I go down to start and go to all programs off, perhaps you've got it as a lapse and scroll down to Microsoft SQL Server 2016. Andi got to installation Center What we get back to this familiar switchboard. It's called a Standard Boxes a switchboard. So let's click on installation. We did the top one. Now we have to do the 2nd 1 so let's click on the 2nd 1 This will install the front end SQL Server management tools, and this time there is a nice button with Jews do conflict, which is bizarre. So you got to click the big blot, so now you can see that it's downloading Onda. This will just take three minutes on my Internet connection, so let's just see what's being sold. So if I going to collapse Andi Microsoft SQL Server 2016 If you've got 4012 Dewie, those aren't stole by some over programs. They're fine. You should concentrate on 2016. The full version of 2012 nowadays that work we does 10 which is a bit of a shame. So we've got some reporting services. That's S R. S. And this is how you can't come figure it. Andi, you've got some more less important stuff. Perhaps importing and exporting data. You've got a dictator quality server and you've also got he configuration manager. Now until you start getting into the higher parts off SQL Server, we talked for you, will. But for now, you probably won't need any of these. The only thing you would need his SQL Server 2016 Installation center Andi, what's being installed and also what we're going to install in the next video. So what SQL Server Management's tools will allow us to do is S S M s onda administration. So if I could just explain what all of these various acronyms are, I says Ennis, she's SQL Server Management Studio. This allows you to called in sequel or T sequel, which are all of these select staff from my table type of statements and affords that the actual programming elements that which is something which is quite well worth learning It will also allow you to get into administration allow you to become what's called a database administrator. So that's SS Embeth is this bias is SQL Server Integration Services. Onda What? This will allow you to do his automate quite a lot off what happens up here, but you have to really get to grips with this or the very least this before you can get on to that. I would put this first and second half ago report, says Horace, which is as conservative with porting 14. Studio on. So that is getting information out in reporting for Mt. And finally, this have been called SS Yes, Analytical Services, and that is to do with cubes. That's is to do with getting information helped, but in a form that easier to use once it's set up. But to start with it is a bit more complicated. So if you're just starting out of this, concentrate on that and then I've a report db 81 of the two, and then only go down to As I said, This is two years later, but the great news is developer will get you all of those. So nice downloaded just last minimizes What is this? So it's still Onda lording packages. So it's not downloading packages anymore, so this will probably just take a moment. Now you can see some of the packages starting to download, right? All specified components have been stolen successfully. Sounds great. So we can close that and go back to this Tyler box and also want to install SQL server data tools. So we got another link. Here we go. We can download, got game, got a good friend of the bottle. So we download data tools so you can see it says we recommend installing visual Studio 2015 . So, in other words, you got to almost suddenly, um, now the visual street, Your community is free. So I click on that and thank you for downloading it on. And there it is. So we're not in my eyes. Everything goes. Now it's installing. And again, he's just a click and do so We started off with installing some back engine stuff. Then we've installed some front engine stuff in the front engine. Looks quite easier so we can customize this. Let's keep from Costa. Might next. You can see what you want to instil Kraft bags. It's very few. These If any. This is the only one Microsoft SQL server data Tools. So click next and so right. And as you can see, it's now all installed. The computer needs to be re start before starting the product. So click reads. Don't know. I'll see you in the next video. Right now we've got visual studio installed. We can go back. Andi going into Microsoft SQL Server 2016 and go back. And do you notice how long this less is and cutting into the installation center? So we've installed the back end. We've instilled the front end on we've installed visual studio, but now we can install SQL server data Tools, so download SQL server data tools. It isn't stolen. All right, would then go down two point free, set up an administrative install point. It's badly named, but then download the appropriate SS DT set up. So going to do that So English, in my case United States, so you can see that didn't take long. So click Next. Yeah, you have read them next. Andi, it's now downloading the latest SQL Server data tubes. So again, I, like celebrate this a bit. Right? So now it's installed. SQL server data tools. So how do you know my fruits installed or not? Well, if we open up visual studio, this is visual studio 2015 community. You can see the little icon that's there. Onda. We create a new project. Then on the left hand side. Previously, we just have things like visual basic on visual F sharp C sharp, C plus plus, But now we've got these and these the really important in our analysis services, integration services, reporting services. So if you remember from Elia, these are that says yes, This is ice. This is ours. How you get the whole within visual studio. So previously we also installed assess m s. So let's go and find that it's difficult to elapse Microsoft SQL Server. And here we are, right near the top Microsoft SQL Server Management studio. You can also find it by ruling S S M s. So I would go, It's okay. Okay. Meisel, These Yeah, it is now, Lord ***, for the first time, it's lording user settings or, more accurately, its making up some new user settings and giving them to you on there is your Microsoft SQL Server Management Studio. I'm looking. The seven name Don't. Okay. Okay, Then we got wearing vent his process M s, which also allows for database administration on Dhere. We've got SS. Yes, and has his eyes on decesaris. So congratulations is taken a bit of a while, but you have now installed the full SQL Server 2016 Developer edition for free. 5. Downloading AdventureWorks: right now, we've got all of the programs. That means, of course, we now need some data. Andi, I'm going to use the adventure works. So if you Google adventure works one word you see that the first example I've got is the Microsoft DB product samples. So if you go into that, you'll see that, though are in fact, new sample databases for SQL Server 2016. But I'm going to stick to the 2012 version. It doesn't really matter whether you just install 2016 or 2014 or even you've got 2012. All of this will be work with SQL Server 2012 Ongoing cold call for the D W version Data Warehouse. So if you click on that, Onda will find that there were lots off data files and in fact, I'm going to choose both off the 1st 2 Now this eventual works 2012 data file. This is your standard all l tp online transaction processing, which is the sold off SQL server that was used t SQL Force all select name of fields from whatever That's just standard one. I'm also going to download the adventure works dwt data Warehouse 2012. Now what we're going to do is we're going to use this 1st 1 to create our very first cube. That's a model within s s s Andi. Then what? We're done going to have a practice activity so that to see if you can create your very first cube using adventure works DW 2012. So feel free to download this first data file on Follow on with me. The 2nd 1 I will be doing after. But I want you to do it first, and then I'll do it. And do we can see if I had any problems on def so we can correct him because really, you only learn about actually doing the stuff. So, as you can see on my computer is going to take about seven minutes or so. So I'll just wait till it's downloaded, right? So they have now downloaded. So I'm going to move them into a folder so that I know where they are. So I've created a folder called s S s On that they will. We now have the data. So in the next lecture, what I'm going to do is look at our adventure works in S s M s SQL server management studio , and just have a quick look around 6. Investigating AdventureWorks in SSMS.: Okay, so the next thing I'm going to do it's open on process M s. That's SQL Server Management studio Onda then going to attach this database and then can have a quick look at the tables. So you know the sort of thing get that we have. So this is an oil TP and online transaction processing. So here we have all ltp. Onda, as you can see, is typically for data entry. So in other words, this is great at putting data in the essence of S S A S is we want reports out and we want analysis out in the quickest amount of time handed all TP, although it's good for rapidly getting data in is not optimal for getting data out. However, let's have a look at S s M s andi going to attach this database. So I'm going to add a D database on you can see at the top where it's going to attach it. So I need to copy the database into that folder. So here I've got two instances off Windows Explorer. Andi. I'm going to paste this location. Andi copies across. You can see I've worked on the light version previously, so I've copy this across some dragging at the right hand mouse key, so it gives me an option for copying. So now I can go back into the dialog box on the touch. So I click back into SMS Onda writing Click on databases and go Touch. So add a new database on Here's My Adventure Works 26 2012 data click OK, and it quickly as it in. So let's have a look around on. The important thing I think, is the Schemer festival. We have a human resources schemer so we have an employee on then everything about the employees. So as we start off with marital status and national I d number on do we have what department they're in onder the history of the department and that sort of thing. So human resources then we have a person on people obviously have a dressers on. You can see how they will link together using these primary keys and foreign keys. So what address type is it? Andi, what contact type is is on because if you want to see anything about this, any examples, you could just script the top 100 tickets. Instance. See what's enough? They're talking about so human results is on person. We also have production, so lots of things about the products on. We have lots of things about sales on. That's where we're going to start our investigation. So just look at the number of tables I've got about sales. 369 12. Probably about 20. Andi, although relevant in oil tp on the reason that there were so many tables of proliferation of tables in oil TP is that you want to enter it once on. Do you don't want inconsistencies? For instance, if I say that I d one is a credit card, we certainly don't want I D one being a debit card elsewhere. So we are minimising the amount of inconsistencies and we minimizing the amount that we got 20. For instance, we say that what's the payment type of payment type is payment type one. We're not saying that the payment type is credit card, so it minimizes the amount of information we got to do on maximizes the amount off consistency for everything, and that's great for entering the data. But there's a lot of luck of table chicken. We've got 20 tables here also, and that's on in the sales schemer alone. So what we need is a different type of beast for oil. AP Online Analytical Processing. But we'll get into the big difference between these two. A bit letter. The first thing is want to do is just use visual studio Andi SQL So for data, tools and all the programs that we've installed and then she just get her hands dirty by creating up First Cube. But first of all, we have to create a project, and we'll do that in the next video. 7. Creating our first project: right. So let's open up Visual studio, which is where we will be creating our cubes. Now I've installed the Visual studio 2015 Community Edition. You may have been sorts Different edition, so your icon might looks like different. So let's open it up, Andi, here we go. So it may look very complicated. Andi, you may not see exactly what I'm seeing. For instance, you got to box on here. That's to do with S s eyes. But that's nothing to do for some good closet later. But first of all, let's create a new project. So this is a container for everything that we're going to do a good secret data, connections, data, cells, views. We're going to create dimensions and cubes and all that sort of things. But I'll explain each one of those. As we go along, Let's create a new project. So festival. We have a huge amount off things that we can now create, including business, intelligence, sewing, no visual C sharp or basic. We're not using any of these. We're going to create a business intelligence, and specifically we're going to create an and analysis services was up difficulty that word . So s s a pass, and you can see that there were various options we can import one that we made earlier. But really, we want to create our own So a s multi dimensional and my data mining project, we won't actually be getting into data mining in this course. That's far advanced, right? Next, we need a location. So our brows for location, But I'm going to stick with S S s. But this is why you select your unfolded while you wanted to go and it's going to create a directory. A folder for the project Onda, we need to give it a name. So I'm going to call this my first cube. So as long as you've got this ticked, you've got a fatted location on. Personally, I will do it on your hard drive as opposed to over network. Andi, you've got a valid name, that's all. OK, so click OK on. This happened during my tests as well. If Mike Self Visual studio does suddenly stopped working, then we started on just to each other game, so one more time. So don't be too surprised if it does happen. But it worked fine for me second time. So my fescue take okay, Taken, see, second time you works. So we've now created a solution or project. Andi, in the next lecture, we're going to have a look at what we've got here. Because really can get quite complicated. Andi, you may not be seeing exactly what I'm seeing so we can customize what we think. 8. Looking at our working environment, and Facts, Measures, Dimensions and Cubes: Microsoft visual studio is used for a number of things. It's used for C sharp on issues for visual basic and it's used for S S I s That's integration services on S S. R s. That's reporting services and reusing gases are rise later on in this section. A few videos hands. So this means that the settle that we've got isn't specifically for S s A s. So if you have installed this product before visual studio, you may find that is set up for how you were doing it. For instance, if you're using visual, basic program is maybe settle for if it's all basic. So let's find out what we got. Well, firstly, we got a huge blank section in the middle and that's why you're reading a lot of your work . But then we've got tabs and pains on the left and on the right and on the bottom. So festival we have a solution. Explorer on this gives us how main headings how to create a cube. You start in the top, Andi Moorthy, work your way down in fact, will be sticking to these 1st 4 with a bit about roles later on no s s eyes to box. We don't need so any of these that we don't need just closed. Let's say out what I'm going to produce. But what? We can also do it so can drag it up so I could have it floating. Or we can put it to any one of these that will predict full screen, Dr Left Doctor. Right, Doctor, Top bottom. So you can see we've got similar ones over here. So I'm going to talk it to the bottom and you notice that's not where we had it previously because this pain used to go all the way down. So docking to this bottom, you can see that it's difference. Now we can have a pin these or can also hide them. So if you also hide them, they temporarily disappear that we can always get him back by just clicking. Now, one thing that's not here, that probably is. There in your version is the properties and properties are very important. Now all of these are contained in the view point since regard to box that's different to DSS. Eyes to box, confusing enough. But we got properties and this is so important. You can see all of these keyboard shortcuts well, Properties Window has F four on that is one cable choker that is worth remembering. So there is my properties window, and as you can see, it's pretty plain that moment. It's just showing me properties about what I'm clicking, and it's probably a tub. It is probably Tab in your solution Explorer, so it's probably and one off thes. There we go solution Explorer and properties so you can tab these like this, so or you could have them separate. Well, you could have, like half and half, so we can see that it probably depends on your screen layout. I'm recording these at 7 20 p, which is screen resolution off 12 80 by 7 20 But you will probably using a much wider screen 1920 by 1200 or 1920 by 10 80. So me and you may even be using to monitors. So really, what you need to do is put it how you would like to use it on. But there's really no limit. Several properties on the left inside. That's fine. So I'm going to put it here for the time being until Unless I decide to change it Now, I just want to define this word dimensions. However, to define dimensions, I have to define a couple of awards. Fuck's Andi handle also was in the World Cube. Now, a measure is literally something that you match up. Okay, I'm not meant to use the same word in the definition. Would a metric any better or business symmetric or something like that? So I love words. If I say this ruler is 30 centimeters, 30 centimeters is the measure. A fact is something that uniquely identifies what we are measuring. So a fact will consist off a measure for quite often, several measures. But you will also consist off basically primary keys that link to descriptions, descriptions. Uh so dimensions are descriptions. There are describing words. They are sort of like adjectives. So, for instance, I might have a ruler that he's 30 centimeters long. It was purchased in a particular order On the dimensions would be the older. It would also be describing the rule itself. You know what color it Waas What supply it? Waas what was glass or plastic? So there are dimensions which linked to facts and facts contain measures. Another thing that separates dimensions from facts is that then generally, dimensions are small tables, fax, big tables present. How many adjectives do you actually need to describe rulers? I mean, all the rulers in the world. I know that many. Once you got past 100 for the color and 10 for the different manufacturing processes, you're not going to get into six figures. But how many rulers are there in the world? OK, you're going to get into millions billions, so that would be a fact table. A description off all of the rulers in the world is going to be a dimension. Always gonna be several dimensions. Andi. It's going to be Each of those is going to be small because this is a how many different colored tap different distinct rulers are no money. Fact tables would include measures. So measures are generally things that you can aggregate. You can some you can count, you can average you can perform manipulations with, and all of these are contained in a cube. So we'll be going into a bit more detail about dimensions of fax and measures and also will be going into detail as to why the data sources for S s a s should be in a different layout to the debtor sources for S S M s, the T SQL over variety. Oh, more quickly. Why the oil AP gets souls should be a different format. Still containing the same date of a different format to or l A teepee different set up a different structure. 9. Creating a Data Connection: right? First thing we need to do is create Toral data sauce connections. So data sources aren't literally your data sources themselves. For instance, if you had a huge Oracle database is not a copy off the database, it's a connection to it. So letters first of all create a new connection to your data sauce. It was missing the worst connection. But if you just remember data cells represents a connection to your data, then you will know the difference. Stream this and your data cells few, so we can either create a data cells based on an existing connection, while I haven't existing connection based on one of my previous courses as a science. But what's actually want to do is not created a source based on a new option a new object. But I want to create a data cells based on a new connection. So I click this new button just here, and this is why I tell it what salt half connection I've got. So it could be that I'm connecting to Microsoft Excel or I'm connecting to Microsoft Access , but I'm going to stick wave connecting to SQL Server. So here's my SQL server native client. 11. So going to connect to my local server, which is docked now, If you had the sole support local, that's him. If you had just need to move this to get the drop down, attached the database in the one of the previous videos, then you could connect to the database that way, So we've all the lorded it into SS PMS, we could just connect here. The alternative is that we can attach a database file. So we've previously downloaded the adventure, works 2012 so don't actually need to attack it in s SMS. I can't touch it right here, and I'll give it a logical name and nature works. So either way, you cannot do that. Now, if you get this error message that says access is denied, it's no really telling you the full truth. So here's our folder, and you can see access is perfectly valid. But what we need to do is to allow you full control so that you can modify what's here. So just to show you how I got to this this is Windows Explorer writing. Click on the folder, got properties, got security, click on edit for users on then allow full control foresight for users. So we need to take on uses again for control. Click. OK, click. OK, then we go back into here, test a collection again. Testing connection. Really good on that. You can see the test connection succeeded. So that's what happens if you get that access is denied. You have to go into Windows Explorer. Andi, allow four collectivity full control which includes this modify which wasn't there to your debt mess or if you've already loaded it up, you can just like to the adventure works either way. Tested connection. Make sure you've got a connection on. Then we can continue. Click OK on and click next. Now, how do you want to connect to that particular file? Or how do you want to connect to SQL Server? Now, you can either use a specific windows user name and password. Andi, if you're in a corporate environment, you will probably have very specific reasons for doing this. And you already know what those reasons are. So if you've got that, then user name password. If not, you might as well use yourself. You can't user. So could next. Andi. There you go, we'll call this my data cells. Uh, click finish, Andi. We can add multiple data sources. So if I had wanted to have a data source being get the file as well, I could have kicked down on this and click open. But you can see that the database is actually being used by me. So this is basically saying that data sources can come from more than one place. And this is one of the really good things about SQL server analysis. We don't have to stick to things that we can get easy access to using T SQL. We can use databases. We can use spreadsheets. You can use access as long as we can connect to it using the death sauce, we can use it. So that is our first day sauce. 10. Creating a Data Source View: Now we got to data cells. We have to create a few. Now, what is a view? Imagine your painter. You've got your paint. So that's how souls material as well as I can tell us. Let's concentrate on the paint. We now put it onto your artist's palette. So all of your pains that your boss, you're not going to necessarily use all of them. But you have selected ones that you might use a particular black particular orca, a particular maroon. So whatever it is, we're not using every single paint on a palette. We give ourselves the choice off just a few, and that's what a view is. I'm not going to use every single table that's in my data source. I mean, that would be overwhelming. We saw that there were about 20 in just one scheme all along, so instead we use a subset off just what we want to use. So let's create a new data source view, and you can see you can use data Celtics from tables and views in a relationship relational database. So let's click next. So what is our data source? Well, we have to find a data cells previously as my data source. So this is where you could define a new debt assaults if you want to, and they'll go through exactly the same wizard we saw in the last video. So select my day soul school to next on. This is where we can select what tables we want. By the way, have a look at the bottom right hand corner. If there are thesixties dots in a triangle in a right angle triangle, that means we can resize the window. Even if it doesn't say you have to maximize anything, we can just resize it. So here are all of our windows in schema format scheme older. So we have the DP old that did his homers, then human resources in person. But let's concentrate. We got views right at the bottom. Let's concentrate on the sale schemer on Let's have a look at this sales border details. So going to get that across now down here, we've got out roulette table. So what? Tables are related to the sales? Oldest details. So that's click out and you can see we get another couple. So all of these tables are tables which have phone keys or primary keys. So, however, there's a funky in detail on the primary keen header all they've around. We cannot related tables yet again, so you can see sales. Older Header has an awful lot off tables connected to it, and you might be, I don't want to see the shipping method or I don't want to see some. So it's entirely your choice so we could just keep adding in Attica. Nothing. But you don't want to keep hunting stuff that you don't need. So let's click next. So this is going to be our data self. You, you can see, is going to contain all of these tables, probably bit over killed. So let's kick finish and see what we get on. Hey, we have our view. So we have a diagram and you can see everything connected as per the primary. So you see the primary keys there on all of the phone keys. It could be now, having looked at this particular data source that we're going at this table I don't want so I can select it and we can never replace the table or, well, that's it. So it's gets substituted with a different table all weaken. Delete the table from the DSV, the data source view. And that's what I'm going to do. So that's object is no longer at our disposal. When we get down to cubes and dimensions, you could see also the tables here, and you can also see all the primary keys. So what do you actually want for this first Keep? Well, I've decided that I'm going to concentrate on the sales called a header on. If I expand this, you can see that we have an awful lot of things. Including, for instance subtitle. So that's something that we can aggregate in some. We can count it, weaken average it. So that is a measure. And this is sort of a fact table. It's the closest we're going to get in this particular example. So I'm going to get rid of all of us. So click on the 1st 1 and I can just press delete. Now, down here, we've got this little four headed arrow, and that gives me a quick representation of all off the tables. So good twos, delete them in a variety ways. And, of course, I can always resize them and Wasit And of course, if I decided that I've gone too far, like, for instance, Oh, no, I've accidentally deleted my credit card table. That's okay. I can always writing, click and add stroke from your tables That gets me back to the style of box, and I'm going to find using a filter my credit card table. I'm going to add that as well. You don't see can also trail system objects atop recommend you take that unless you particularly What you doing? So we don't have very complicated daters self you, but you can see how complicated it is going to get with our O. L T p type of table strewn layout on This is one primary reason why our oil AP is a lot less complicated. We don't want to be working with a huge number of joints we want. Basically, it's easier life as possible. But as I say, Well, look at or l a piece instructions later. So for now, we've got how data source for you 11. Creating a Cube: So let's just have a quick review. First of all, we got the data source, the connection to the data. Then we've got a data source view. So there's trims down all of the tables and just gives us the tables. The fields, measures, dimensions, primary keys, foreign keys. What have you that way we're interested in. And at the moment I'm just interested in one table and we're going to create this que which is the next step quite simply going to create a very simple cube because I want to make sure that it works in your system. I don't want to develop a complicated cube on, then find that we've got a connection problem. So writing click New Cube so of writing clicked on cubes. New coupe will get this dialog box. Use this wizard to create a new cube. First you select the data source for you on tables for the Cube. They set its properties that then you can You can also help to create a Cuba values and get into cells. I don't really recommend that more advanced users, so let's clear next. So how would you like to create the cube? Do you want to use existing tables, create an empty cube or create tables in the data cells. And it was, You don't have a data source view. You just have a data source. Well, because we got stencils under debt is ours. View will use this top on. So basically use existing tables where you have a data source. In the details view, Use this bottom one if you just have data cells and use this middle one if you've got Niva , So let's use existing tables. So which are my measures? So measures are things that you can aggregate you consume. You can count. You can average well, there are some measures in here. There are some things I wouldn't like to measure. Like, for instance, boot to address. I D don't need to measure that, but there are other things that you can measure. Like, for instance, the subtitle. The Taxed. Afraid the total Do so, and as you can see, you need to include at least one measure group. So here's our measure group table, and then it's saying, or what are the measures on who it's identifying all things that are theoretically measurable, including all of these I ds. And obviously I don't want all of these ideas. I've got to include at least one. So I'm going to include the total Jew. I'm going to include the tax and the subtitle and also going to do this one, which is a count of the number of items. So sales older header. That's the name of the table count. So going to next now dimensions. Are there any dimensions? And there are some dimensions in here, so just as a reminder, measures are things you can aggregate. Dimensions are things that describe the things now. It's not the most brilliant table, but I can use, for instance, customer I. D and sales person idea from used dolls, the dimensions. So I'm just going to select that this table. As I said, This is going to be a very simple cube, so I'm going to call it my first Cube instead. They, of course, is the name of the profile. I don't like finish, so we have now a very complicated I think. Obviously we won't be using all of this in our introduction. So avoid cubes of a single measure, absolutely a single dimension. Yes, So what I'm going to do now is test to see if it works. I'm going to know all of these at least for the time being and go into the browser and this cube cannot be browsed. Verify that the coop is being deployed. Onda processed. Now, over here we have the process. So let's click the process. But until we're in browser and those this the thing that looks like a recycle symbol, he's got free arrows in a circle. Let's click on process. So the super content appears to be out of date. Well, no kidding. We're not actually processed it yet. Do you want to bode on Deploy? I the project first. So remember, verify that the cube is being deployed. Andi processed. So yes, we do want to deploy. So say yes. So you may get this. My first que database has been changed. You may not, if you do. Yes, right. So you could see very quickly that there is a deployment completed successfully. Very critical mine Now we got lots of options here, but really the only thing I want to do is wrong. So processing the full version The Cube. Let's go Run! Andi you can see the problem. The data cells contains an impersonation world that is not supported for processing operations. So you remember earlier about two lectures ago, I said, There's a message on screen single. Go back to the impersonation mold on. We will do that and set up a role on finished processing the Cube in our next lecture. So this is a Sfar as we're going to go in this lecture, you can see that you may on your version just go through this. No problem. But you may get stopped, and if you get stopped, that's what the next lecture we're going to correct. 12. Creating a role, and playing with the cube: so we needed to do an impersonation more. That is support of processing operations. So let's close and that's close. Let's close our first cube thes air. Just windows. Of course they don't actually do anything. Brooke, you just open them again here. So we open up my data source score to Impersonating information on I want to use a specific windows user name and password, so I'll type in. It is not possible. So let's redeploy. Our cube will try to going to browser the process yet want to boot and apply. So deployment, processing, operation, progressing oppression we run. So you forgetting if over this time I started reading data that skirt onda process completed so close, close success. So let's try and hold in the browser again on Dhere. It works now. It could be that on your system. It still didn't work. Okay, so let me show you the way around that. So we've done a date ourselves. We don't a date ourselves view. What we might also need to do is a role. Roles are important in allowing specific users. Perhaps you're so perhaps people the people than yourself to access specific parts of the Cube. After all, if you've got, say, sales information or even worse salary information, you wouldn't want everybody to have access to every part of the Cube, would you? You might just want some people to have access to some of agents, some to have access to all of it. So we're going to create a role. I don't need it or mine, but you might need to 10 years. So click new role. So on this, I'm going to give full control. Okay, so now we need to assign membership. So I'll add membership. Andi, this is when you can have my machine based on your pre existing users or groups. Your windows, users and groups are allowed me and I am he member off this administrator, Will, you can see it's where type he turns into this description. And there's lots of other types here. Membership? Yeah, So close. Save so that all of these over taps that you can't let click on. But at the moment, full control is full control. I don't need anything more. So if you do have problems wave browsing this cube after you have deployed it, then this is all you need to do, create a role, had yourself to the role on, then make sure that the role that you've created is this impersonation information suffering since if you currently role for just blog's, make sure you got job logs here. So we created a data source. Were credited view we may have created a role on We created a Cube, which in turn has created Dad damage. Now this is a browser. So we are looking at our Cube and we have got measures. These are our four measures that we selected earlier. We've got things called KP Eyes, which is empty, will come to keep key performance indicators later, and then we've got dimensions. Onda. I'm not going to do too much at this stage about dimensions. I just wanted to make sure that he was working Cube. So what I'm going to do is I'm going to drag Total Cube into this area here, and you can see the Total Jew. Wow, that's a lot of money 123 million, but also do tax amount. There's a tax amount. 10 million, I'm guessing. And he's a sub total 109 million. Yep. Andi, how many rows. Who got? I can also filter on this cube on that's technically known as slicing. So, for instance, this is entire cube, the entirety of the data that we've got. I might want to just look at a specific sales older, and I want to sidetrack it up to here. This is a filter. While the equipment of a filter. Andi, I want to filter on just this one particular order on notice as soon as I do so all of the tussles get recalculated. So we've done a very basic cube. We've got it to work. So the next video going to actually play with it outside all visual studio? 13. Looking at the cube using Excel: So we've got our Cube, but it's not really sufficient to just to be able to play inside of here. We want to be able to slice and dice it outside of visual studio, and you may not. It is rather intriguing. Excel Button here. What happens where we click on this Excel button? Well, Excel opens. I'll enable the potential security concern, which is my cube. Andi. Here we have a pivot table automatically opened in Excel, so I don't have to do anything complicated on notice what we've got. We've got all of our measures now notice. Excel represents Agra Gates with E Sigma, the Greek Letter Sigma, which is this one here. And then we have measures. Now you may be used to pivot tables from Excel data. So if you are, let's just have a look and see what exactly is different. Well, the first thing that's different is that the measures are separated out from the in this case, this table, so so far, that's all that's different. So what I can do now is I can add these measures and I can also hot the sales older I d. I could have added here as a rule. So they go or can I did it as a filter and not is there appears to be some sort of hierarchy here. All of these pluses might come back to that letter. So this is how you can get from Visual studio to excel and be able to play with it very quickly. All you have to do is click on the exorbitant in the browser in the Cube. So this is how very quickly had set up a simple cube you contested else in an environment outside visual studio. 14. Adding an extra table, and Creating a dimension: now cars. Creating a cube is not just a one shot deal. You might want to know if I quite often have to go back into a cube on add extra details in . Quite often, you just need to reprocess as you've got newer data. But also we might need to just get some more tables or refined the dimensions and that sort of thing, and that's perfectly OK. You could do that. That's odd going to our data source for you. So let's have a new table to this view. So remember, the souls remains the same. I'm just adding some more colors to my palette, so I'm going to add and remove tables of writing clicks. Andi, let's see what we let tables. You've got all of these andare looking at that and going the only one I want to retain is this shipping method. So let's get rid of all the evidence. So I press shift to her like those free on DSO click click Shift that gets rid of those okay, And there we have in new relationship that is an existing relationship in the data source, but is being exposed in the desert sauce view Andi ignores this relationship because we have this primary key of ship effort. I d. Aunt. Here's our foreign key. So let's close that. So now we need to add it into the Cube. Now. We could destroy the Cube on that anyone, But let's not do that. Let's instead add a new dimension. So news, I mentioned. So we're creating a new dimension. First of all, selected data source view and tables for the dimension. So enough about the measure. We're looking at dimensions now. I'm using existing table on the main table for the dimension. He's the ship method. Now the key come. The primary key is a ship method. I D. No, you may have a user friendly name for it, and then we do. It's the name column, so to take an example. Suppose we had dates and suppose we had a table which had months of the year that the key column would be the number off the months. 12345 The name column would be January, February, March, April, May I call when we said that, and the difference is that the computer will salt on the key column and not the name column . So if you had the January February March itself off in the key column, then he was sort it in alphabetical Achter. So April come first and then August because the body begin with a whereas if the key column , he is your number. So basically, key column is how you wanted to be sorted. I'm not necessarily always the case, but quite a good rule of thumb key come how you wanted to be sorted as well as something that uniquely identifies the dimension. Next can be replacement name in whatever your main languages. So my case English. If, by the way you are doing a table, a cube in multiple languages, double we've got that covered as well later on. So let's click next. So specified the attributes. So never words what you actually want to see. And so let's say I want to see the ship method I d. I could take, which have I want. It's a lot more here, but let's just keep going for them. So here is our finished page of going to create the ship Method I D. Which is going to have as its name the name now that sounds actually shown in this, but that's what we said here. So let's finish. So there is our damage, Andi. It's fairly straightforward. So that's closet. This table itself is not necessarily straightforward, but will come back to it later. 15. Updating the cube, and using Excel again.: So let's go back to our cube. Andi, look at that dimension usage, so you can see at the moment the only dimension that's there is our pre existing one. So we need to add a new dimension. So it's one of these buttons here. See if you can work out which one it might be. It's the one that has a little plus symbol in green on that looks like a dimension. These are dimensions, so you can see the free axes X axis waxes and said, Access gold, easy access. So we had a new dimension, its form ship method. There's a dimension. On day we go, and for that that's all we need to do. You can see that Measure group is related to out shape measures method because we have this in the intersection. Old to put it another way. In Tedesco, where you connect two tables together, you've got to use joins, letter enjoins, writing joints and self off in cubes. This dimension usage does, he told for you. It joins why there is something to immediately well, on the far left and on the far top on something in the middle. So here we can see that the ship measure So Ship method is joined to the sales order. Head a table through the use off the ship. Method I d all. To put it more precisely, the Dimension Ship method is joined to the measure Group Cold Sales Order header through the ship Method I. D. So this is your joints and don't wave. It's a bit confusing at the moment. We will be coming back to this later. So now let's re process the brow. The cube. If I was just to look at it now, you can see that's how a new dimension isn't there. So let's we process it. We don't know the full process. Lots of very useful data in all of this, by the way, and now we refresh the browser. So now we have our new dimension. So if you've added something recently and you don't see it, track clicking. Refresh. If that doesn't work, try re processing. So there is our ship method and you can see that there are five different methods, and so that's going to our measures on and we'll add so total tax and Jew, and we'll also add the ship method i d. So you can also see there are five possible ship method ideas, but only two are used. And you can also see that we have dragged ship method I D, which is going back to the table. It's really a number, but because we said use the name in substitution, it's now using the name. So just a reminder we went back to If you went back to Sheik Mufeed I d. Andi, you can have a look at the dimensions If I click on a ship method I d. That's cruel to the bottom you can see in Key Column The key column is the Ship Method I D . Which is an integer, but the name is should method name. So that's the name of the table. That's the name of the column, which is a string w char. So they use different technology and SS. So that's a strick. So let's open it in Excel one more time. So now you can see we have measures one dimension, never dimension. So let's odd thes into a cube on then ship method I D. Oh, we could put on door. We could put the sales order idea appear Andi into the report page on Just focus on one particular cells older and everything else reacts. So in this lecture on in the previous lecture, we have added a new dimension. And then we have added it into the Cube, using dimension usage and their reprocessed, refreshed Andi got it else into eggs. Ellicott, we're well on our way to learning how to do cute. 16. Looking at the cube using SSMS: Now, of course, Excel is not the only way in which you can interact with cubes in this video. On the next, I'd like to show you two more. So first of all, I'd like to going to s SMS SQL Server Management studio. Now, you're probably very familiar about this wave using t SQL. But we can also connect to analysis services will by default. We're looking at database engine. You can also look at analysis services. So attacking your server name. In my case, it's the local one. So amusing Docked, click, connect Until we have our SMS version of our cube. Andi, you can see things instantly. A different we've got assemblies and management as well as databases weren't really going to the assemblies on management. That's I'm looking to databases. Okay, this looks familiar. My first cube have been that we've got a very familiar it's just compound contrast. Data sources. We've got my data source. No, it's it's different icons, day sizzles, views, cubes, dimensions, mining structures which we're not going to get into in this course and rose now, assemblies were also not going to going to so out of these five major ones we've already touched on all of them in this course. Right? So let's create a new query. So God, through our database and click new query or writing Click on God Knew Query. No, no. Says it does give you some options. We're going to use MDX in this course. That is for me, the main language off cubes. So you might be. Now we're going. Okay, This lots of it different. But this looks also a bit similar to what we're looking at in the browser. So you remember the browser that was here? We had all of these, so this looks quite familiar. Okay, So what I'm going to do is I'm going to drag one of these measures across on presumably will have. The same has last. Oh, no, it's not quite the same, is not as graphical. Okay, so let's right, It's late, Curry. So select stone from, uh, drug in. My first. Cute. And we'll see what we get. Okay, Play. Where's the play button? Well, if you can't see the play button, it might just be playing hard to get. If you are looking at this on a relatively small resolution, is all the way over here Execute. You might need to readjust these tools. And to do that we go across to thes hashed lines you can see just above the cursor on Drag it down. Oh, Andi, There we go. So we execute. I prefer, heard they execute to be underneath Prefer to be there So we execute Oh, um I felt we don't have permission to access the reference mining model. All the object does not exist on this is when you're going. Okay, This the most basic Q b can get. What on earth is happening? And the answer is we're not in t SQL anymore. We're in empty X. Things are different. How different? Well, I just delete this star. So this is a perfectly functioning query now select from my first cube. So let's execute on does the results. Okay, you tried to work out. What exactly is that result or that result is the default measure off the entire cube. So if I go back to the browser Andi, get rid off ship method I d. You'll see that? That is the subtitle. The computer has identified that as being the default measure. So why is it the default measure. Well, if you go back to cube structure so we've got all of these on. Basically, we added So total First. That's why it is the default measure. Now we can change that if I click on the Cuban Gold properties. And if you're thinking you're gonna be spending most of your life here properties as it quite right, we can see right near the top. We have default measure. So let's see what that says specified the measure used to resolve a MDX expression if the measure is not explicitly reference. If no default measure is express ified, an arbitrary measure is used as a default ledger. That does not sound good, So let's put in the total do as being the default measure. So re process. Go back into SMS re execute. So now we have a new default measure being the grand total. No en D. X is completely different to t SQL. It does you select does use from, and it does use well, but the rest of it we'll have to come back to it. It is a huge section itself, but walking sees that select from name of cube, perfectly acceptable syntax in MDX on DSO. This is now another way that we can connect to our cube in a different program. 17. Looking at the cube in SSRS: so so far we've had excel. Then we've had and says M s which you may prove this is just shoes for tsk but can also be used for the language of MDX. Enough of, um, this visual studio Behold who just being in visual studio while this is a different part of visual studio, this is the reporting services. So I'm going to do a with it that don't have to do with it, but just for ease. So I'm going to connect to our cube using SS or s. So, first of all, we selected data source on the data source is a new desk cells, and it comes from which one of these Microsoft SQL Server and Analytical Services some good call this our cute my fescue. The First Cube. Mrs. Solis, we need a connection string. So let's click Edit Typing your servant in Don't. In my case, Andi, there is the data best now. Testing connection works. So there's my connection strength. We could make it a share data source if you want to, but not for needed for this click next. Then we need a query. So click on quickly. Botha. This should start to look surprisingly similar to you. So let's get the total amount Jew and maybe some over stuff on. Let's get our dimension off shit method. OK, so that's our query. Now that lot is an MDX query on Did it looks horrendous. Don't worry about it. But we will be looking at non empty Andi looking about all of these brackets mean there's are from on we got the select to the beginning, so that's goes next. So select our type of reporting going to use a tabular report. Onda Let's put in all of these into details. Apart from the ship method idea what not to be in group stepped or blocked. All use step tonal include Subtitles Naval drew down So this is my first cube with hopes. Let's finish on. The Wizard will do the rest. So there is my first report. I'm just going to just just this slightly so it's a you can't really read all of a heading . Here we go. There we go. So now how will maximize it? I'll preview this say run though you report and it's not going to look two brilliant, to be honest, there we go and we can drill down. There is nothing to drove down into. But this is how you can create reports. So we now have three different ways off access in cubes, outside of visuals, to deals, SS Yes, that is, we can use Excel that we can use SMS and you We can use the visual studio's s s. R s on a call so over ways to do SS ours as well. If you do want to learn more about SRS, I do have a video on that. That's now cubes can be approached many different ways. So what we have done that we have created our first simple cube. We've gone from dentists ALS, debt assaults view. We may have created a role. We created a cube, we processed it, and then we have viewed it in various different programs. So I hope you remember the old of all this because next video I want you to do all this again with a completely different debt 18. Practice Activity - Let's do it again!: right. This is my s s a s fault. Now, if you remember way back in one of the early videos we downloaded to debt bases, this adventure works. 2012 is a database we'll be looking at on. It is not ideal to be a cube, and we look at the differences between this and something that's better basis to become a cube later. This, however, did is the basis that I really want to concentrate on cubes. It is a D W. A data warehouse. So what's different? Stream these two? Well, let's connect to how, uh, adventure works. Andi, Let's just remind you off the huge number of tables thought to money. Well, not really, but it is enough when you are entering data, but for reporting, it's no really that useful. You have got so many complicated joints do, but very, very important that you are able to enter data such rapidity. So instead, let's Lord or that's their touch are over database. So we opened up this folder, this folder, maybe slightly different from your machine, but it's whatever for the studies, and what I want to do is to copy the database the D W adventure works deep W 2016 2012 database from its current location into the data location. So distracted using the right mouse button. So we're fresh. There's our DW, So we touch it. You don't need to do. All of this is just for me to show you what there is inside this data. Best so fresh. Hey, come. So you see that there are much fewer data basis tables. There are lots of them starting dim. These are your dimensions and the one starting fact. So these are your fact tables, Andi, if we go up to the database diagrams, we can see that the number off diagrams over the existing so finance, for instance, you can see that there is a fact table at the heart on. Then there are various dimension tables going off it, and you can also see that this account there is a self referential connection. Now, this has a technical name which you might be able to guess if you have a look at this shape that you can see around the cursor is called a star. Schema looks a bit like a star house. This is in a as opposed to a snowflake schema, which has tables going off it and tables going out for the tables were going off it. So I'll talk in the future. Lecture about the difference between the star scheme and the snowflakes. Que mint How one is meant to oh, adjust the tables to reflect the difference between schemers. But you can see need to these schemers. There is primarily a fact table. Hey, we got to Factiva about free fact tables, but from each of them we have the same schemers going off it and send dimensions going off it. So what I'd like you to do as part of does the exercise is to create a cube. So we just created a cube. This is our cube. So we created a dentist ALS than the debt of you. Acquitted are Cube. We may have created a role depending on your security, and then we process the cube on that. We opened it up in excel. Now there are lots off database diagrams that you can see here, and some of them are quite frankly, have based on the complicated side. But these are great. If you want to create your own cubes you know, have a structure for how to do it. That the one that that I want you to specifically have a look at is this one. It is DB your database on a dot product in Venturi. So this is a fairly simple schemer. We just have one fact table and off it, we have a dimension relating to products. And that dimension relating to dates on this is the basis off our exercise. In fact, I'm not even going to do the dim date in the sex site. What I'd like you to do, he's create a cube based on these two tables. Fact product in Venturi. In fact, have Zion dim date. So I'm creating a word document off these instructions, which will be a results on the next lecture. So what do you want? We want the following measures on measures, of course, come from the fact table. I won't units in units out on you experience. Those are your measures. Those are the things that you can add and subtract itself off. And then for dimensions can decorate one dimension based on the dimension table on enable browsing for color on for English description. There was a column of cold enable browsing. We have the following activities festival Create a cube creativity creative project Then on a data source on a data source view I had a role, if necessary, create one dimension. Andi enable browsing on two attributes. Add a cube process test If you were tested as well and then finally I want you to export to accept, right? So hopefully you could do all of that from the previous videos. If you can't, if you get stuck in a certain way, the next video is going to be this word document. The video after that is me doing goal of this. So if you do get stuck don't way. I will be doing it in the two lectures time. Good luck. 19. Practice Activity - The Solution: right. How did you find that? Hopefully not too tricky If you did. If there's something that you can't quite do, then let's do it together. So festival. I will close my consolation, create a new project so we'll start off doing this one. So quite a new project Onda. We go back to analysis services on DSO. We'll call this my second cute. So it's creating a direct for your folder for the solution on I'm not importing it from anywhere. And just doing a standard analysis services MDX and Data Mining's project. Okay, Okay. Right next. We're going to had the data sauce. It's a new data cells. We need a new data source. So we don't need to click on another object because we're not created the object yet. So a new decibels. So if you've attached it, fine. If you haven't, then you can attach it. So I've your chance to to him about free lectures ago, in which case it's here. Alternatively, if you haven't, then you can attach it. Go to the relevant folder on bond, click open so you can do it either way. But test a connection, make sure that it wants So I'm going to go to my one that had previously attached what you could do even way. So there's my DW data connection. Specify my user name and password. There you go. So I prefer calling this something to do with the data source. So dw dead cells. Okay, Now we create eight days of you, so choose your data sauce, choose what we're going to go off on. It is a reminder we're using the fact product in Venturi. Then he could add related tables that brings in dim date. We'll have that let in a later lecture. But we just start with these two. That's all that were quiet. So, though, is the data itself you holder? I had a role, if you need to. So add a new administrative role. Andi looking and I'm an administrator. Tick on. And that's all save right Next will add dimension. Andi, we've got two things that were going for the dimension. We're looking at color and English description, but because they're on the same dimension table, we only have to measure create one dimension for it. So, using an existing table, there's a dim product and it wants the key column. In other words, the prime rookie or the following key. That's product key on what I want to do is to enable browsing off the English description on the color, and I don't want to enable browsing off the product key. But you can see that I've got precious little choice. Click Next. So this is my dimension on the product table. Right? So there it is. So just close that you cute, that's our next age. So adding cute. So using existing tables, I want Where is our measure group tables there, in fact. Well, so which particular measures will I want? Unison. Units out in units balance. Why was out that mentions It's dim product. So there's a cube. So to be cute. Finish. So there is our structure. Very simple. There is our dimension set up going from dim product relating to fact product adventurer using the relationship off product key. So next we process. So yeah, so quick process. It deploys. We then process full. There you go. Process succeeded. So now we could refresh. So this is part of the testing. Try a lot in the browser game that's better. So here are measures so we can drag measures in out balance, which presumably is one minus T ever. But you can see isn't Doi will have a look at that in a later lecture for Let's Get Rid of Units. Balance, Andi. Then we have color on English description. So there's color that seems to work so that can get with that on English description. Here we go. So finally we export two Excel. Click the exorbitant table and there we go so you can have in and out does our color if I tell Move color. Two column. That's actually just shoot units in just a bit simpler on then I could have English description going down. Reduce the column way if if I wanted to text There you go. So you can see, for instance, for his pains that we had for this one 2514 Blue on 3111 yellow. Alternatively, I could have it. Woman did the over like that on, so you can see that's our blue and yellow. And there we go. That is the practice activity. So I hope you found it useful. Hope you were able to do it, if not quite first time, then hopefully this video has encouraged you to be able to do cubes fairly quickly. It's only been a few minutes, but we've corrected our cube. 20. Updating dimensions, and creating translations: right, So now we've got our first Q. But based on the data warehouse, let's expand it festival. Let's look start Dimension. How do I mentioned only allows color English description on the product key, so we've got lots of other things that we could look at. We could look at the English product name, for instance. So let's say that as well. And it's a simple as going over to the data source for you and dragging English product name over to the attributes of these attributes or what is being exposed. And we are exposing color in this description in this product name and the product key to delete something like this thing, this product name say you don't want it to be exposed, then simply delete it now that stops it from being shown to all users. If you want it to be on e sean to some users than really that security rather than setting up your cube in setting up the dimension. So not because an additional a tribute to our dimension. So let's save it on. Let's test that it's there. We have to re process. But as you can see with this to be honest, Not much data. It totally takes a matter of seconds. So, no. Where is it? Stop that. What do you think we have to do? Tissue it? We then have to refresh. So there is the product, Lee. So I'll drag English description out. So that is English product name. That's what we just added under competitive and then EPA not supporting the English description as well, so you can see what it is. So you have English name handing this description. But you might say in our table we've got not just got English product name before Scott Spanish. We got fridge. Similarly, we got French. Andi. I was expecting Spanish description to for them to be equal. But don't got French description on our beckoned Hebrew and Japanese. I don't me out those as well. No, it's going to be an awful lot of dimensions on. If you're speaking English, you probably don't care about the French name for something all the Spanish or the Chinese . Where was if you're speaking Spanish, you might be irritated if somebody sends you a spreadsheet with English as the default for the Cube. So what we can do is changes. We have a translation. Taps used this view to build or edit translation of the Cube. So this is what we've got so far. A cube. Andi, this is what it say's into default language. Cube units in, units out, units balance, etcetera. Now let's add in new language scantly. We've got just the default like, but let's had a new translation. So I'm going to translate this into Spanish so that Spanish off mainland Spain, let's say on Dhere all my translations in Spanish and you can see there aren't any. The computer doesn't literally translated for you. You've got to to that, I'm afraid. But why don't we start? So what we have to do is literally type the translation. So there is a cube, Andi. Then I've used Babel fish too. Get these translation you need Addis Agra Kado. You know that this l e nado belongs the status on poor yet Oh, not productive. My apologies if you speak Spanish and I think that my accent is dreadful but let's have a look that's reprocess Onda. We'll see what we got. So we have to refresh Andi, let's get my mentor out on and my dimension. Let's see using this part of name. So that's the default, all in English. But I can change language up here, and I'll change it to Africans. No, it doesn't actually know Africans. So everything stays the same, doesn't say Cuba or doesn't say anything. So now let's change it to the Spanish off Spain, and now you can see productive and you can see you need that. It's like a god or new honey. Daddy's eliminated a and all the rest. So let's execute this creamy. Let's deletes that Put in there. So it's starts of what? It's not completely day yet. We've now changed this measure, but we still actually have the English product name. So what do we have to do now? Well, let's go back into the dimension where we've got color, English description, English product name and product key. Well, if he would across the top, what we have is translations, so we can do the translation of the dimension. So once again we have to add translation, so I'll choose. Punishes Spain. Andi. There we have color and English description and put a name. I think that's nobody, but presumably that's not actually sufficient. I mean, we've not actually said This is a Spanish product name, but not is now we have a daughter dot we would do is actually she'll dot that dances just showing dot In a moment we'll click that and we can see the full of definitions. English product name in the language Spanish Spain is there's a caption number, but what is the actual column where the actual column we want to use instead of English product name is Spanish product name. So now you can see there's a replacement column simile for English description. We have a translated columnar caption now off the script seal Andi. What we have is, well, unfortunately, we don't have a Spanish name. So let's just pretend that the French product, the frank description, is this family's description. So let's click OK, and similarly, if you had colors, we could do the same. So now let's close that on distantly because you can add Aziz many different ah translations as we'd like so we could do French. So I do the French of France Onda. We have description on product name, so there's part of there's the French product name as the French full product name is known . The Pope. Probably. Then we could put that there. Andi, Similarly, for Spanish. We know. You can see we don't site for a description. You can see that we don't actually have to fill in all of these columns. Same with the scripts. All. So now let's go back into a cube. But don't have to go back into this. Translations. We could. We just have to reprocess. They're all done. I don't need to wait for the information's close on. Here we go. So change this to Spanish. Spanish, Spain. So there we have a description and we need to measure. Had it. Units on. There we go. Oh, we could put in the product key. So the name. So we're missing a few translations, It looks like, but there we go. Oh, Hersey. Classical. The Magna Carta. Grand. Then we could change it to French. Andi, you could get rid of this. Given that there's that the product name. You did say it and you can see that the product name is now in French. No, you can guess. Of course. If you go to a translation, we don't have like after cans that we're back to our default English. But I'd be talking about the Spanish for Spain. What if this was a Spanish of the United States? Well, you can see that the computer says that it's close enough that you use the Spanish for Spain. Translation unless I've actually put in a different translation. For instance, mainland Spain. They have the word for juice. The stuff that you drink on that is sumo Zed, you hemo. But in Spanish outside of Spain, they call it Who go J m you geo. So if you are brooding, accurate translations, you need a translation for thes Spain Spanish on a translation for Spain, American and that sort of thing so you can go down to the 10th degree, but don't sweat it too much. Basically, if you want translations, you can then add it and this many translations as would actually be useful. But what we also did in this lectures we alter the product de dimension product so we can add additional attributes. That's what each of these air cold in a dimension so that each one of them gets exposed Now just to finish off this lecture, I have changed the language off my computer from English into Spanish. So now if I going to official street deal, you can see that even though we're on the default language, we actually have Spanish. We've got dis groups, young support to description, For instance on Now, if I go into excel, we have our categories. How measures our dimensions using the translation now why I haven't translated it like product key. It remains in its original language. So, for instance, if I have look a unit that is a great kado and put on the color, you can see we're still in English. But then if I change that to number, though we have can be set a classical Spanish Andi if I change it to descript young, remember, this is French. Of course, we have all of these things in French, so all of that is just available depending on what their computer is set up. You define the cube, you define the translations. The end user gets the end result in the language of their choice. How powerful is that? 21. Adding a new table into the Data Source View, and replacing it with a query: right back to the English language. We've gone into the dead sauce few, and I'm going to add an extra table Now. You may remember that next to the fact table product, adventuring was also the date table. So let's had thus as well. Let's just have a look at this state table so we can see English Name of the week on numbers and stuff off. So what this does is it translates the date key into its already look up table. Days, weeks, names, calendars, fiscal year, semester, quarter. Enough words. The computer doesn't have to calculate anything. It's all pre calculated to give the computer the rapid response. So let's add this dimension and let's add the years and we'll base it on their calendars. No physical. I don't know what semester the ribs. So let's just explore the data. So if I writing click on the table like thinks, blow the data, so this gives us a sample data so you can see that the key date is the date in Japanese format. Year, month, day. So what is this semester? Well, we got a year off 2015 on Gaza semester to starting in June So I semester that while his half a year and you can see we just have ones and twos. Well, is the quarters goal wanted free for 100 years off? You just fronts for via so that's his quite handy. So if you're not sure what Data got just writing click. Andi just explored the data and you can see what we've got. So there's ARD interview, So I want to build a dimension based on dim date. So let's do it. It's a new dimension. Use an existing table I'm going to. So we are doing the dimension on the dim date. So the primary key of dim date is a date key. Andi, That's in alphabetical order because it's numbers. So having that is the name. Is that really what we want? Let's just have a look at the data again. Go back into the view. Look at the explore date. I think probably would want to see this rather than this, I'd imagine. Is there anything else that would look better? Has a date? No eso stick to what we've got. Obviously we could actually aunt a date way could and on a table of based on dim date, but which includes on additional field. Oh, let's do that. So if we replace the table with a new named query, Right, So this is our existing table. So let's just have a look at it. We've got the dim date table and you can see here in SQL what it looks like now I want a new field. So this is why knowledge off ehskyoo come in. Quite handy off the 1st 4 letters numbers the day field followed by slush, followed by the next to followed by a slush and then the last. So that's my oh has yes. Well, as, um, a date. Japanese? Yeah, Japanese. Right. So let's see what the results are So you can see up here. We've got diagram pain. We have the grid pain. We have the SQL 40 sq propane and hey, we have the results pain. So let's execute this and see what the results. Uh, so the sub string is in family because my calculations of electrical date key isn't a string is an integer. Okay, so we just need to add a few more converts up that we converted into a string we know execute. So Now we have the date in Japanese format. And of course, you could change that to the dating English format or American format. Oh, have you are I think I've seen Japanese formatted would have hyphens in the middle. Providence lashes like that. So better. So that's just the show that if the source material is not exactly what you want in the data source for you, you can, um, edit the query about the table to replace it with a new name table. Or you can create a new name quickly or you can create a new inquiry. Scratch Onda The joined to that. 22. Adding a hierarchy - two levels: so anyway, back to creating a new dimension on the reason we went into there was because in this existing table for the dimension date, I'm sorting by date key Burton theme The English. The presentation version. I want deep Japanese Now What do I want to show? Well, I want to show the calendar quarter and year and semester. Andi. I also want the day number off the month, so that gives me She also want month, month of the year As you talk. I drawn quarter. Yeah, why not? So that gives me a lot of flexibility to work with. So that's my dim date. Great. So now let's reprocess the Cube. We need to make sure it's in the damaged usage. No, it isn't so. We need to have this dimension on dim Date, and you can see the primary key on the foreign key is date keys, the primary key of dim dating. It's the phone key off this measure group, so let's re process. So now reading debtor and it's all done so close. Close on, Go, go! To excel on Dhere, we have our dim date so we can have a number of units in Andi Now we can have it by year. Andi. Then we can have it by 1/4. Oh, by months, Oh, by date. Not that I have actually dragged a Kim in terms of number of the month. So my vote no, I had to build up. This is a This is a hierarchy were going from 2005 going then Number two, Number six with. So if I was to get rid off quarter there were looking at the 30 for the 6th 2005 This is a hierarchy that I've boot up. Andi. I can collapse the hierarchy on, then expand to the month. But the point is, I asked, You had to correct this hierarchy for myself. Also, the problem with letting the user defined relationship is that they may get it wrong. If this is the relationship, what's to say they won't get it in a completely opposite order. And blessed in ships such as those using hierarchies don't need to be just in deeds. For instance, you could have country State city what happens if somebody yes, that's in the wrong order. So it's much better if you define a relationship hierarchy yourself. I'm going to delete this existing dimension. Still, it's a fresh so hopefully by now this is getting much more familiar to you. So I want exposed the calendar stuff on the day off a month Have a go now. Hierarchies are formed in this middle bit. So to the right, you got the data source view into the left. The attributes hierarchies can only boat up off attributes that have bean put off this section. So I'm going to do a very simple hierarchy. It goes from the more specific outwards or it goes from the biggest grouping downwards. So the biggest grouping here is year. And then it would be called a semester, quarter, a month, a day and finally the unique value which is the date key. So I generally work from the bottom up, going from the more specific to the least specific. Now attribute relationships allow you to see how they are connected. And here we go from the left to the right. So let's now reprocessed this cube have to go back into dimension usage because I deleted the damn internally to Riyadh it. So let now reprocess it. There we go. Just missed missing out these processing steps now because I don't need to wait. He's for you to wheat weight each time we've done it. Onda Now let's exported to excel so you can see we now have a dim date hierarchy on some additional fields. So if you're looking the additional fields, we have all of the six original fields and did the hierarchy we have calendar year and that the Daiki. So let me put in the units in and they were put in the hierarchy. So it starts off of calendar year on then. Underneath we have the dates key. So we have built our baby first hierarchy. The next one will be, and people difficult will have a know little twist. 23. Adding a hierarchy - three levels: now problem with this is that it's a bit of a jump. We're going from year down to date. And ideally, what we want is a hierarchy that we had here with Yemen Day, for instance. So we need to add an extra level into our high rocky. So let's go back to his dimension structure habia So we drag across month number of year into the hierarchy. Simple as oh no, because a triangle attribute relationships do not exist between one or more levels of this hierarchy. In other words, the computer doesn't know how year can extra month until month connects to date. After all, it's very easy to find out how everything connects to the primary key. But which one assessed the year? The month computer doesn't know. So we're going to the attributes relationships on Dhere. You can see the computer, not Nike. It thinks that month is dependent Date and Dio is dependent on a date. Where was really we wanted to go just like this? Girls from bottom up, we want this to go left to right, so the way to change this or one of the ways to changes is to move that which is should be on the left hand side over that, which should be on the right inside. So you go from the least specific to the more specific so I could go. I could drag date on top of month. But what I really want to do, you drag months on top of year. Let's do that. Police. And here we can see that. Now you got everything going from the bottom up and you got everything going from left to right. This is how we would envisage it. Okay, so let's say that close and save goingto cube going to damage in usage. Make sure it's there always were friendship, making sure that dimensions are there one. But this is your joins, Onda. We go into the browser, we process run on and we get complete success. Oh, no. What's wrong? A duplicate key attributes key has been found in a month of the year for we didn't get a problem before. So what is going on? Well, this is a quick representation of what's happening in the Dimension date. We've got various states between 28 31 of 11 month on. They're going to a month, which is going to a year, and that's fine. The problem exists when we get to 2006 it. The computer is expecting that there is going to be one unique way to get from a date to say April the fourth month bird. As you can see for where we are, we have to. April's. The fact that they are different tables leading into different years is immaterial to the computer. It is seeing that at a particular level because all of this is one big level here at a particular level. We have a Jew pit April. We also, of course, of Jupiter hurt January, February, March itself off. But looking people, we don't have duplicate years because they're all grouped together like this. But we do have to April's and that's what the computers complaining about when it's saying that there are Jew picket attributes, so we need to find a unique attribute four months of the year. So here, instead of saying April here April, we need to simply say April 2005 so that here we can now say you for 2006 and then there won't be duplicates, and this is when they're going into the property's is going to pay off big time. So click on the month number of the year over here. In the attributes on the Senate, you click on the attribute in the Properties window on Press F four. If you can't see the Properties window. So so in view, right at the bottom. So the properties window got all of these properties. I'm right near the bustle. We have a big section code sauce and hey, we have key Comes knows his plural. Currently, we have key calm, just one of them. But we want to change that. So click on the lips of the dot dot and here we have our key columns. Currently, it's just one key column, but what do we want to do? Do you want to include not just the month number, but the year? So we had the year as a second key column, so I'm going to use the calendar year in this case now. Of course, that's not exactly going to be a user friendly thing. The column now KEY COLUMN now has more than one column. It's got a collection that you can see, but If you press the plus key, you can expand the collection and you notice it's quickly underlying. Let's see what it says. The name column should be defined because they're multiple. Key comes so be careful is not this name column. It's click on it. Is there a month name of you? It's not this name column. There we go. Its name column. So, Captain, you don't just define name its name column. So let's define that when we do find that as well. You could define it as the month number of year, but you could have a more user friendly name. Where was it? English month name. Right. So now this quickly line is disappeared. So let's see what happens when you go back into a cube. Aunt Reprocess. Do we get that message? No. Process succeeded. Okay, so let's now explore this into excel and see what we get. So put units in here on the high rocky here. And so we start off of years before then get months. And finally we get days. And so it works. 24. Congratulations: right. Well, congratulations. You made it. SS air. Sounds scary, doesn't it? But how? You know it isn't question. Cubes has its problems, but isn't so difficult, is it? Might imagine. So, what will have we don't what we've installed for free the developer edition on your computer. We've corrected cubes Will have enhanced them. You've had a translations, dimensions, all sorts of things. We've exported it to excel. Is this all this? And we've had to go writing some MDX statements in S S s. So why do you want to go from here? How are you going to use S S A s and your newfound knowledge in your organization? Well, for your let, Nick, what do you want to go from here? Perhaps you want to build a basic cube, Onda, then credit report out and show everybody what you can do and how wonderful this technology would be if everybody would adopt it. Or maybe you've already got some cubes ready to be processed even where Congratulations for getting for its cars and well done