OpenCourser
Learn skills that shape your future
Visit OpenCourser.com
Reviews:
"Good Stuff Overall. In my opinion, the instructor did great with the "How-tos" which helped for sure in grasping the whole concept of how to create cubes, set up data source and source views, dimensions, add attributes etc." Lakeside David-Debo
"A fantastic course which gets you rolling very quickly and comfortably, thanks for the short and condensed knowledge delivery. Thanks, Phillip you made SSAS very simple for me." - Anup Kale
Reviews:
"Good Stuff Overall. In my opinion, the instructor did great with the "How-tos" which helped for sure in grasping the whole concept of how to create cubes, set up data source and source views, dimensions, add attributes etc." Lakeside David-Debo
"A fantastic course which gets you rolling very quickly and comfortably, thanks for the short and condensed knowledge delivery. Thanks, Phillip you made SSAS very simple for me." - Anup Kale
"This is really the perfect course for beginners. Easy to learn and very inspirational for further investigations in SSAS. Thank you very much, Phillip. " - Marina Barinova
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 information out quickly? 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.
Hello, and thank you for joining me in this course. I'll be introducing myself, and looking at what we will cover in this course.
It used to be that we had to install a cut-down version of SQL Server. Instead, let's now install a version with the full functionality of the Enterprise edition - for personal use only, though.
Now let's go through the process of installing SQL Server. I'll also go through the various editions of SQL Server (e.g. 2008, 2012).
Now that's the back engine has been installed, we've got to install SSMS (SQL Server Management Studio).
We are going to use two versions of AdventureWorks - the first we will use to create our first cube, and the second will be for a practice activity. Let's download them now.
Let's have a look at AdventureWorks, using SQL Server Management Studios, and have a look at the tables used.
Every cube needs to be creating within a project. Let's create our first project.
Now that we've got a project, let's have a look around at the various panes. Also, just as T-SQL has its own terminology (such as "window functions"), SSAS has its own words. Let's get used to facts, measures, dimensions and cubes.
Now that we've got a project, we need to connect to some data. Let's create a data connection with our AdventureWorks database.
Just like a painter needs colors, a cube designer needs tables. Let's add some tables into a Data Source View.
We've got a data source and a dimension - let's now create a cube, and pull a couple of tables into the cube.
So on my computer, I find that I can't see the cube that I've just created. Let's add a role for my account, and change the impersonation for the data source, and see whether that solves the problem. Then let's take a quick look at the cube.
Now we've got a cube, let's look at it. First of all we'll open Excel from Visual Studio directly. Then we'll go to Excel 2016 and we'll add a database connection, and create a PivotTable, and see how that is different from traditional PivotTables. We'll do the same again in Excel 2013 to see how that it is different.
Let's add an extra table and create a dimension from this table.
Now we've created a dimension, let's re-process the cube, and then view it in Excel again.
Not only can you use T-SQL in SSMS, you can also use MDX, creating SELECT statements from your cube. But be warned - the syntax may look like T-SQL, but it is actually quite different.
What else can we do with cubes? Why don't we create a report as well, using SQL Server Reporting Services.
Now it's your turn. Let's create a second cube, this one using the AdventureWorksDW file. We'll go through what you have got to do - and then, over to you.
As a reminder, here are your instructions.
Let's run through, and create this second cube.
Existing dimensions can be modified, by adding additional attributes. But what if one of the attributes you want to add it merely a translation of an existing one? Instead of addition translation attributes, let's translate an attribute instead.
Now that we've got the cube working, let's add another table into the DSV, and then after trying to create a new dimension, we'll replace this table with a query.
Hierarchies can be very useful in organising data, such as dates into years, months and days, and cities into countries, states and cities. Let's create a 2-level hierarchy from our date dimension.
Now let's change our hierarchy from 2 levels (year - date) to 3 levels (year - month - date). It's now at easy as it sounds. Let's find out what problems we encounter.
Save
Provides a solid base for further study.
Instructor makes learning simple and engaging.
Delivers knowledge efficiently for quick learning.
Provides helpful step-by-step instructions.
Makes complex SSAS topics easy to grasp.
Perfect for those starting with SSAS.
SaveOpenCourser helps millions of learners each year. People visit us to learn workspace skills, ace their exams, and nurture their curiosity.
Our extensive catalog contains over 50,000 courses and twice as many books. Browse by search, by topic, or even by career interests. We'll match you to the right resources quickly.
Find this site helpful? Tell a friend about us.
We're supported by our community of learners. When you purchase or subscribe to courses and programs or purchase books, we may earn a commission from our partners.
Your purchases help us maintain our catalog and keep our servers humming without ads.
Thank you for supporting OpenCourser.
