Why Do I Need An SSAS Cube?

A viewer of my Pluralsight courses wrote to me asking what I thought was an excellent question. “What is the advantage of having an SSAS Cube?” or at it’s root, why do we need cubes at all? What do cubes provide that we can’t get directly from the database?

It’s an excellent question because understanding why is the foundation for all other learning about cubes. If you know why you are doing something, you’ll better understand what you are doing as you do it. I think the answer can be best illustrated by an example.

Let’s say you are an analyst for a huge  chain of international retail stores. The big box kind that sell everything: groceries, household items, clothes, and the like. You’ve been put in charge of toilet paper sales. When starting your analysis, the first thing you want to know is “How much toilet paper have we sold over the last five years?”

This may seem easy at first. A simple SELECT SUM(Amount) FROM SALES WHERE Product=’Toilet Paper’ would yield the result. But wait, is the query really that simple?

Think about it terms of scale. On any given day you likely sell at least a million rolls of toilet paper. Amplify that by years, and now you are looking at billions of individual rows the database has to crawl over to get the results, if not more! Even a high performance database could take hours to process such a query.

Of course you wouldn’t stop there. You’d then want to break down sales for each year. Then for each month in the year. For each month by state/province. Then by sales territories. Then by individual stores.

But wait! You might decide to analyze by state/province first, then by year/month. As you can see, your queries would soon be taking hours, if not days to run.

In addition, your queries would be brining the database to its knees, your coworkers would be ready to spread nasty rumors about you on social media, and the DBA Police would be bursting into your cube, disconnecting your computer from the network and putting you in handcuffs.

By now you’ve probably guessed the answer to this is the cube, but it circles back to the original question, why a cube?

While the focus of the question was SSAS (SQL Server Analysis Services) the answer applies to pretty much any cube based software. Analytic cubes work by aggregating values ahead of time. Whenever the cube is processed, the cube engine (SQL Server) contacts the source database, reads in all the records, and sums them together. The totals are what is stored in the cube.

During processing the cube engine will analyze the data, and determine the best way to organize the data into small pieces that can quickly be added up to answer most questions. Of course, during the design of the cube the developer can provide hints to the cube engine in various ways, such as creating hierarchies.

A hierarchy provides guidance to both the end user as well as the cube engine. For example, you may have one for Year / Month / Day. There may be another for Country / State / County / Store, and perhaps another for Sales Region / Sales Territory. Building these hierarchies gives the cube hints on how to store the data aggregations so they can quickly be combined at query time.

Note I said aggregations, as a cube can store many different types of totals. Of course there is the natural one of sum, but a cube can also contain averages, maximum and minimum values, and more. In addition, it can also create calculated values to hold things like year over year growth.

You might be thinking “well wait, wouldn’t it take forever to process the cube everyday?” True, if you processed the entire cube daily. However, most cubes (and SSAS for sure) provide the ability to do incremental processing. This means the engine only has to read in new records in the source database, records that have appeared since the last time the cube was processed, and add those values to the stored totals.

All of this together helps us to answer our question, “Why do I need a cube?”

Speed

Being able to analyze data fast is the primary benefit of cubes. Being able to slice and dice data in an almost infinite combination is why cubes are so valuable to data analysts across the world.

Reducing the workload on the source systems (whether a relational database or a data warehouse) is an additional benefit. The aggregations are created once and stored in the cube, as opposed to having to query the data warehouse each and every time we need information.

Some may argue they can achieve similar goals with tools like PowerBI, and you could. But you need to understand that under the hood, PowerBI is using an SSAS (Tabular) cube to hold the data. So on the back end you are still using a form of SSAS for your analysis. In addition, using a cube provides a centralized data source for many reporting tools, including PowerBI.

By now you can see what benefits a cube provides, and why you need one!

Advertisements

Downloading Files with PowerShell and Invoke-WebRequest

Last weekend I was at the Atlanta Code Camp, giving a presentation on PowerShell for Developers. One of the attendees emailed me, asking for a good example of being able to download multiple files from a website.

To do so, we’ll use the Invoke-WebRequest cmdlet. But first, we’ll setup a directory to hold our output. For all my demos I have a root folder, C:\PowerShell. For this article I’ve created a subfolder called Invoke-WebRequest-Demo. The first thing my code does is sets a variable to point to this folder, then changes the current location to it.

$dir = 'C:\PowerShell\Invoke-WebRequest-Demo'
Set-Location $dir

Next, we’ll need a variable to point to the root URL of the website we wish to download from. This will be everything except for the actual file name.  For this demo I will use files from the No Agenda Show podcast. Not only is it a cool podcast, but they have an unrestricted license for their material meaning I can freely reuse it for demo purposes. Each episode of the podcast has it’s own image, so we’ll download images from a few recent episodes.

After I place the base URL into a variable, I create an array, each item in the array having the name of a file to download.

$baseUrl = 'http://adam.curry.com/enc/'
$files = '1537127586.656_na-1069-art-feed.png',
          '1536868338.385_na-1068-art-feed.png',
          '1536264075.484_na-1066-art-feed.png'

Now that everything is setup, we use a simple foreach loop to iterate over the array and download each file via Invoke-WebRequest.

foreach ($file in $files)
{
   Write-Host "Downloading $file"
   $dlUrl = "$($baseUrl)$file"
   $dlPath = "$($dir)$file"
   Invoke-WebRequest $dlUrl -OutFile $dlPath
}

And that’s all there is to it. If you want to learn more about downloading files via the web, this code was extracted from my Testing PowerShell with Pester course on Pluralsight. In it I test a module which gets the RSS feed, then downloads images and audio files for the No Agenda show. Just go to the About Me link at the top and you’ll find a complete list of all my Pluralsight courses.

Speaking of Pester, you might also appreciate the introduction to Pester series of articles I’m currently authoring for Red Gate’s Simple Talk website. You can find a link via the same About Me page, or just jump directly there by going to http://arcanecode.red to see my articles.

Kusto Query Language (KQL) from Scratch–Live and Free at Pluralsight

My latest course, Kusto Query Language (KQL) from Scratch, is now live at Pluralsight!

https://app.pluralsight.com/library/courses/kusto-query-language-kql-from-scratch/table-of-contents

Even better, this course is totally free! You will need an account at Pluralsight, but those are free to setup and only takes a moment. You do not have to have a paid plan. Once you have your account  you can begin watching.

What? You haven’t heard of Kusto or KQL?

Increasingly, Azure is becoming the infrastructure backbone for many corporations. It is imperative then, that you have the ability to query Azure to gain insights to the Azure services your company is using. The Kusto Query Language, or KQL for short, is the language you use to query these Azure services such as Azure Log Analytics, Azure Security Center, Azure Application Insights, and Windows Defender Advanced Threat Protection.

Kusto is the new database engine that stores data for all of these services. Kusto was designed from scratch to be a “big data” repository for Azure that was easy to query using KQL.

The course starts with the basics of KQL, the Kusto Query Language. In the first module alone you’ll learn 80% of the commands to cover most of the queries you’ll need.

Most, but not all, as KQL has many more commands to handle unique needs. After learning a variety of aggregation operators, you will progress to advanced KQL abilities such as machine learning and time series analysis.

Finally, you will explore how to export the results of your KQL queries to CSV files and PowerBI. When you’re finished with this course, you will have the skills and knowledge of the Kusto Query Language needed to gain valuable insights to your Azure services.

Get Notified When Your Home Router IP Address Changes–With PowerShell!

Readers of my blog over may recall I’m an amateur (ham) radio operator (N4IXT that’s me!). One of my ham radio buddies has a cool radio that can be remotely controlled over the internet. With their software he just enters the IP address of his home (his home router that is, after we setup some port forwarding), and he can connect to and operate his radio remotely, make contacts around the world and more.

The tricky part of all this came in the sentence enters the IP address of his home…. His home router tends to change its address on the whim of his service provider. If the router reboots, it will definitely have a new address when it comes up. You may be thinking “well just get a static IP address”. Unfortunately, his internet service provider can’t give him this, something about him using a bonded pair of 10 mb lines to get 20 mb prevents it.

So how could he get notified when his IP address changes? Well I was fairly certain I could solve his problem with some simple PowerShell scripting!

I’ve now published the solution on my GitHub site:

https://github.com/arcanecode/PowerShell/tree/master/IPAddressMailer

Rather than going more in-depth here, the ReadMe has the full details describing the scripts that were created. In addition the code is heavily commented.

In addition to solving this particular problem, the code could be used as a template for:

  • Getting the IP Address of your home router
  • Sending emails via GMail
  • Setting up tasks in the Windows Task Scheduler

Hopefully other people with similar issues will find this little project as useful as my ham radio buddy has!

Azure PowerShell PlaybooK: Azure SQL–Now on Pluralsight!

My latest course is now available on Pluralsight! It’s the Azure PowerShell Playbook: Azure SQL. If you aren’t familiar with Pluralsight’s Playbook format, they are fast past courses that are almost 100% demo driven. They are meant to be consumed quickly, my course is just one hour and four minutes long. Great lunchtime viewing!

This course shows you how to use PowerShell to manage and migrate your on premises database up to Azure SQL. In brief, you’ll learn how to:

  • Create resource groups
  • Create and work with Storage Accounts
  • Create a SQL Server in Azure SQL
  • Package up your local database into a bacpac file
  • Import your bacpac file into a new Azure SQL database
  • Execute commands against your new Azure SQL database
  • Cleanup! I even show how to remove everything you’ve created, individually or as a whole

And all of this with PowerShell!

Additionally, I’ve included functions for just about everything listed, so (assuming your subscription gives you access to the samples) you’ll have a great starting point for your own library of cmdlets. (All the code for the functions appears on screen, so if you have to you could always pause and type it in.)

You can find my new course at:

https://www.pluralsight.com/courses/azure-powershell-sql-playbook

I also wrote an article for RedGate’s SimpleTalk website that aligns well with this course. I dive deeper into the restartability aspect of the way the functions were coded, something I couldn’t get deep into with the video course due to time constraints.

https://www.red-gate.com/simple-talk/sysadmin/powershell/powershell-functions-reusability-restartability-azure/

What’s that? Yes you in the back row, you say you don’t have a Pluralsight subscription? Well no worries, just email me, free@arcanetc.com and I’ll be glad to send you a code that will be good for 30 days at Pluralsight. During that time you can watch my courses, indeed you can watch any course at Pluralsight.

Mobile Report Publisher–Dashboards Everywhere

My latest article on RedGate’s SimpleTalk site has just come out: Mobile Report Publisher – Dashboards Everywhere.

In it, I give a quick overview of using SQL Server 2016’s Mobile Report Publisher to create a simple but useful dashboard. Mobile Report dashboards can be assembled easily, and quickly, from SSRS (SQL Server Reporting Services) Shared Datasets.

Reports generated with Mobile Report Publisher are great, as they can be used on, as the name suggests, mobile devices such as phones and tablets, but on websites as well. They fill the need between a traditional report and those analytic reports created by self service tools such PowerBI.

Using the instructions in my SimpleTalk article, you should be able to create your first, simple report. If that wets your appetite for more, I can offer two additional learning paths.

First, there’s my book SQL Server 2016 Report Services Cookbook. In it I have an entire chapter on the Mobile Report Publisher. Being an entire chapter I had more space to go deeper, and provide instructions on pulling data from multiple sources. I also go into the use of other components in the dashboard. You can find the book on both Amazon and my publisher, PactPub’s website.

The second resource is my Pluralsight course What’s New in SQL Server 2016 Reporting Services. The report gives a great overview of all the new features of SSRS 2016 (and still applicable to 2017). The bulk of the course focuses on the Mobile Report Publisher, but I also cover other new features such as the new Report Portal, and the ability to host KPIs right on the portal, among other features.

Don’t have a Pluralsight subscription? No worries, just email me, free@arcanetc.com and I can send you a code good for 30 days during which you can watch my courses, or any of the great courses at Pluralsight!

Opening Port 80 in Windows Firewall to Support Calling SSRS From Another Computer

Recently I was working on another article for RedGate’s SimpleTalk site. As part of it, I had SSRS installed on a Windows 10 computer, and needed to connect to it from another computer. I was having a lot of issues connecting, until I remembered SSRS connects using Port 80, and by default Windows 10 (and previous versions) block Port 80 for incoming traffic.

The solution was to, obviously, open Port 80 on the Windows 10 computer. Doing so was not difficult, but did require quite a few steps, and of course administrator rights on the computer.

First, open the Windows 10 Settings. Then, click on Network & Internet.

image

On the Status window, click on Windows Firewall.

image

From here, click on Advanced settings.

image

If prompted confirm you do wish to make changes. When the Windows Defender Firewall dialog appears, click on Inbound Rules.

SNAGHTMLef395d1

Now click on New Rule

image

In the New Inbound Rule Wizard window, change the type of rule to be Port. Then click next.

image

On the next window, leave the rule applying to the default of TCP. For the port, assuming you are using the default setup, enter 80 for the port number. If you setup SSRS on a different port then obviously use that port number instead.

image

On the action page we tell Windows what we want to do if it finds incoming traffic on this port. For this development environment we will take the default of Allow the connection. If you had setup https service on your report server, then you could take the second option of allow if secure.

image

Next, we need to specify what network type the rule should apply to. For the scenario, I am on a small network, such as you might have at home, and that network was setup as private. Thus I am leaving Private checked on, and unchecking Domain and Public.

Unchecking public is especially important if you plan to take your laptop out to a coffee shop, you don’t want someone trying to hack into your machine via port 80. When done just click next.

image

On the last screen we’ll give the firewall rule a name, and a description. When done, click finish.

image

As you can see, the new rule now appears in our Inbound Rules area.

SNAGHTMLf01e748

Once you have completed working with your SSRS server, I’d suggest you return here, right click on the rule, and either disable it, or if you know it will no longer be needed, delete it.

And with that you should now be able to connect to the computer running SSRS from another computer on your network.