Writing for BIDN

Looking to give back to the community or learn through teaching others? Anyone can post blogs by clicking Add Blog Post after contacting us for permission.

«August 2015»
MonTueWedThuFriSatSun
272829

Power BI and Big Data

If you’re worked in the wide and diverse field of information technology for almost any amount of time, it probably hasn’t taken you long to discover that the one thing constant about IT is that the technologies and strategies involved change faster than you can learn them. And if you work in business intelligence like I do, you don’t have to look very far at all to see change. The Microsoft Power BI team rolls out a software update every month! If I want to stay learned up on the technology, I have to really be on top of things.

About ten years ago when Hadoop was first being developed at Yahoo, I don’t think anyone could have anticipated the size of the ripples (more likes cannonball sized splashes) being able to access Big Data could and would have on the IT industry. Hadoop (and other advances in hardware and software technologies) gave us something we never had before: The ability to access and report on data in real time on a scale never previously imagined gives an organization to identify and understand trends and patterns in the data and gain previously unknown insights. The organizations that are able to leverage big data will be the organizations that leave their competition in the dust.

Set Up and Configure the Hortonworks Sandbox in Azure

Not only does Power BI Desktop give us the ability to connect to Hadoop Distributed File System (HDFS) for reporting we can also mash it up with other more traditional and structured data sources with minimal effort required. But that’s not what this blog post is all about. This post is about setting up a virtual machine in Azure running Hadoop and connecting to our Hortonworks Sandbox with Power BI Desktop :).

The first thing you do if you don’t have access to a Hadoop cluster is to set up the Hortonworks Sandbox on Azure. The good news is its free (for the duration of the trial) and its super easy. Just follow the instructions at this link to set up the Hortonworks Sandbox.

Hadoop in Azure

Once that’s set up, you’ll need to add mapping for the IP address and host name to your hosts file. Devin Knight has a blog on this that you’ll find helpful.

Connecting to Hadoop with Power BI Desktop

Once your Hortonworks Sandbox is set up, you’re ready to set up your connection to Hadoop with Power BI Query. Start up the Power BI Desktop and click Get Data. Scroll down and select Hadoop File (HDFS) and click Connect.

Get Data with Power BI

From there you can follow the rest of the wizard to load the data into the semantic model.

Load Data with Power BI

Once the data is loaded, you’ll need to modify the query to navigate to the data you wish to use in your model.

In Power BI Desktop, go to the Home ribbon and click Edit Queries.

Read more

30

Three Best Practices for Power BI

Since the release of Power BI Desktop this past week, I’ve been really spending my extra time digging into the application focusing on learning and experimenting as much as I can. When my wife has been watching Law and Order: SVU reruns at night after the rug rats are in bed, I’ve been right there next to her designing Power BI dashboards like the total data nerd that I am. When my kids have been taking their naps during the weekend, I’ve been writing calculations in the model for my test dashboards. Or when I’ve been riding in the car back and forth to work I’ve been thinking of new things to do with Power BI Desktop.

Since I’ve been spending a decent amount of time with Power BI Desktop, I thought I’d take a moment to share three things to know and remember when designing your Power BI models and dashboards that I think will help you make the most of this tool and be effective at providing the data your business needs to succeed.

1. Optimize your Power BI Semantic Model

It probably hasn’t taken you long to figure this one out if you’ve built Power Pivot/Tabular models or at least it won’t when you do start developing Power BI dashboards. The visualizations in Power BI and Power View are heavily meta-data driven which means that column names, table or query names, formatting and more are surfaced to the user in the dashboard. So if you using a really whacky naming convention in your data warehouse for your tables like “dim_Product_scd2_v2” and the column names aren’t much better, these naming conventions are going to be shown to the users in the report visualizations and field list.

For example, take a look at the following report.

Power BI Dashboard without formatting

Notice anything wonky about it? Check the field names, report titles and number formatting. Not very pretty, is it? Now take a look at this report.

Power BI Dashboard with formatting

See the difference a little cleaned up metadata makes? All I did was spend a few minutes giving the fields user-friendly name and formatting the data types. This obviously makes a huge difference in the way the dashboard appears to the users. By the way, I should get into the movie production business. ;)

My point is that the names of columns, formatting, data types, data categories and relationships are all super important to creating clean, meaningful and user friendly dashboards. The importance of a well-defined semantic model cannot be understated in my opinion. A good rule of thumb is to spend 80% to 90% of your time on the data model (besides, designing the reports is the easy part).

I’d also like the mention the importance of the relationships between the objects in the semantic model. Chance are you will have a small group of power users that will want to design their own dashboards to meet their job’s requirements and that’s one of the beauties of Power BI. But when users began developing reports, they may query your model in unexpected ways that will generate unexpected behaviors and results. I only want to mention this because the relationships between the objects in the model will impact the results your users will see in their reports. Double check your relationships and ensure that they are correct, especially after you add new objects to the model since the

Read more
3112
3456789
10111213141516
17181920212223
24252627282930
31123456

Converting to the SSIS 2012 Project Deployment Model

  • 22 May 2012
  • Author: DevinKnight
  • Number of views: 18787
  • 0 Comments

The new project deployment model in SSIS 2012 is the new standard for how packages are created, configured and deployed.  Any new packages that are created in SSIS will default to using the new deployment model. 

The most basic way of describing this change is now instead of deploying packages we now deploy projects.  Projects get deployed to the new Integration Services Catalog, which I’ll cover in a future post.  Also, you get the benefit of project parameters. which allow you to pass values very easy across multiple packages in a project.  Jamie Thomson has a great series on this topic as well I encourage you to read what he’s written here.

After upgrading to a project deployment model you also lose some capabilities you previously had.  For example, configurations that were a staple for how to configure you SSIS packages across multiple environments in previous versions of SSIS are no longer available.  The previously mentioned project parameters now replace configurations along with Environments, which live on the Integration Serves Catalog. 

Obviously these are a lot of new concepts and totally changes the way you think about deploying and configuring SSIS so I plan on explaining each of these in separate posts.  In a previous post I walked you through upgrading your SSIS packages to SSIS 2012.  As I described in that post, the upgrade wizard does not change the deployment model for you.  So after completing a package upgrade you will be running the legacy package deployment model as labeled below.  This means everything developed in the original packages is still available including configurations.  You can still run you packages using the package deployment model but I would recommend upgrading for the benefits detailed earlier and just like anything else new at Microsoft the old way of programming will likely be deprecated over time.

image

Once you are ready to convert from the package deployment model to the project deployment model right-click on the project name and select Convert to Project Deployment Model as shown below.

image

As soon as you select that you may get prompted with a warning telling you that Data Sources you have in the Solution Explorer will be removed with the project deployment model.  These are no longer supported with the project deployment model but are could be replaced with share connection managers.  Unfortunately, it does not automatically replace your data sources with shared connection managers.  Click OK on the warning.

image

The start of the wizard details the 7 step conversion process, which includes converting configurations to project parameters and changing execute package tasks.  Click Next when ready.

image

The first step is to select the packages you want to convert and provide and passwords that you may have used to encrypt them.  After making your selection click Next and the wizard will load the packages for making the conversion.

image

You will then be asked to assign a name to the project if different from the original name.  New in the new deployment model you can also assign a protection level to the project.  Once you’re done here click Next.

image

The wizard will also upgrade any Execute Package Tasks that it detects.  The task had a bit of an overhaul and now when using the project deployment model you now reference packages that are part of the project instead of a file or server that contains the package.  The old way of running packages is still available but it’s now referred to as an external reference as opposed to the new project reference.  The defaults here are generally acceptable but you should review them then click Next.

image

Next you will be asked to select configurations to be converted.  Remember configurations will be replaced with project parameters.  If a configuration is not found for some reason (maybe the file connection couldn’t be made) you can also add it manually here.  When ready click Next.

image

To fully replace the configurations the wizard will create parameters that will store the same information the the configuration did.  Ideally if these configured values are the same across multiple packages you would choose to scope these as project parameter instead of package parameters, which is the default.  Parameters scoped at the package level are only available within that package.  Leaving theses all as project parameters will result in only 2 parameters created even though it appears from my screenshot that I will have 10 parameters because several of these are duplicated.  Click Next.

image

The parameters  that you just created can now be configured to have new values if you choose.  In my case I have connection strings in my value and I should verify that the provider used is appropriate for the version of SQL Server these packages will run on.  Click Next.

image

You can review the steps that are about to occur if you would like and click Convert when ready.

image

Once the conversion is complete you should see a screen similar to below.  You will notice it also prompt you to save as soon as you close the wizard.

image

A couple things to note about the conversion.  You Solution Explorer will appear different.  Notice Data Sources are no longer here, but you do have a Connection managers folder.  You’ll find Connection Managers to be a lot more useful and I will detail those in a later blog.  You will also see a section called Project.params.  These are your project parameters and can be defined by double-clicking on the object in the Solution Explorer.

image

The parameters are basically variables that can be defined across the entire project.  Again are what the wizard used to replace any configurations you used previously.  Below you will see what the definition of these project parameter store and then to assign them to configure an object in the package you will use configurations.  Luckily the wizard automatically did that for any configurations used previously.

image

So when you look at any connections or other objects that used configurations you will now find an expression that references the project parameter.  Notice below in SSIS 2012 it’s much easier to find connections that have expressions on them because they’re labeled with a little fx icon next to them.

image

When looking at the expressions on these connection you will see it does indeed reference the project parameter.

image

The last thing to point out that the wizard did for us is a change to Execute Package Tasks that I had.  The change is simple enough but it’s a new concept to 2012.  Basically instead of referring to a package that lives in the files system or SQL Server you can now reference package within the project.  This is called a Project Reference and the change is done automatically for you using the wizard.

image

I hope you found this post helpful as you walk through through converting your SSIS packages to use the new project deployment wizard.  In my next post I’ll walk you through deploying these package to the Integration Services Catalog.

Print
Tags:
Rate this article:
No rating
DevinKnight

DevinKnightDevinKnight

Other posts by DevinKnight

Please login or register to post comments.