Archive for January, 2010

PowerPivot for Excel – My First Look Part 2

Friday, January 29th, 2010

In my previous blog, I discussed the PowerPivot workbook. I looked at how to create it by importing data, and I briefly covered the new Data Analysis Expressions (DAX) that are used to create new measures. In this blog, I would like to look at the presentation of PowerPivot data in worksheets.

Worksheets created from the new workbook house PivotTables and PivotCharts. You can have PowerPivot create the workbook with one of several possible layouts that include one or more PivotTables and PivotCharts.

After the layout is determined, you simply select the PivotTable or PivotChart to work on and drag the fields you want into lists in the task pane. Adding a field to the axis, label, filter, and value lists, automatically changes the related table or chart to reflect aggregations of the data including subtotals and grand totals. PowerPivot creates each aggregation based on the relationships between the PowerPivot tables in the workbook. All of the aggregates are SUMs by default. However, it is a simple matter to change the default operation to calculate and average, minimum, maximum, or count.

Fields are also used to create slicers. Slicers add components to the worksheet that contain buttons for selecting data to display in the PivotTable or PivotChart. Each button represents a distinct value from the field being used. If several slicers are added, then you can use any combination of selections to filter the data. The combinations of filters are limited only by your imagination and can range from predetermined business criteria to random combinations. I used the Contoso retail database to create my workbook. I was able to design a PowerPivot PivotTable that showed me all of the sales filtered by product category and subcategory. Then I added demographic slicers that enabled me to change the sales figures based on customer gender, income, marital status, sales source (reseller or individual), and territory. Being able to do all of this within about ten minutes was very cool! I am used to spending hours or days with various report writers trying to perform simpler data analysis reports.

Most of what PowerPivot can do to analyze and present data is automatic and requires very little operator assistance. Naturally, you can spend a little extra setup time and manually create measures using DAX. But, you can also create reports from PowerPivot workbook tables using the new Excel cube functions. This is considered advanced PowerPivot. For an excellent example of using PowerPivot and cube formulas, you can go to http://powerpivotpro.com/2010/01/18/pulling-back-the-curtain-intro-to-cube-formulas and walk through the tutorial.

For now, PowerPivot for Excel is still part of Microsoft Office Excel 2010 Beta. That means if you want to try it out you should be prepared to deal some idiosyncrasies until the final release. Nevertheless, I can guarantee that if you wait for the official release of Excel 2010 and PowerPivot for Excel, you will not be disappointed in the least.

Oh! Did I mention that PowerPivot for Excel is a FREE add-in!

Share

PowerPivot for Excel – My First Look Part 1

Friday, January 29th, 2010

For the past few weeks, I have been working to understand the new Microsoft PowerPivot. PowerPivot is actually two products: PowerPivot for Excel and PowerPivot for SharePoint. In this blog, I will walk you through PowerPivot for Excel. I will discuss PowerPivot for SharePoint in another blog.

PowerPivot of Excel is a new add-in for Microsoft Office Excel 2010. That is right, 2010! PowerPivot is not available for Excel 2007 or earlier. Excel 2010 is still only a beta release, but you can download it to try out the new cool features provided with PowerPivot for Excel installed.

Excel 2010 is cool by itself, but when you add PowerPivot, it becomes something that most business managers have wished for in a spreadsheet for years: the ability to analyze business data from multiple data sources without paying thousands of dollars for a custom data warehousing solution. This is what Microsoft calls Self-Service Business Intelligence.

At first glance, the only thing new in Excel after installing PowerPivot is a new entry in the ribbon called PowerPivot. On the PowerPivot ribbon, we find the ability to create a PowerPivot workbook. After creating a workbook and importing data, you can use PivotTables and PivotCharts to slice and dice the data in ways limited only by your imagination, literally.

But, Excel already has PivotTables and PivotCharts, so what is the big deal? PowerPivot takes these components and enhances them to use multiple tables. This is evident by the addition of a table section, along with vertical and horizontal slicer sections on the field list.

When importing a database into a PowerPivot workbook, all of the relationships between tables are registered and stored in the workbook. PowerPivot then uses the relationships to create measures. For seasoned business intelligence professionals, the workbook is analogous to a data warehouse cube and the measures are similar to dimensions. The difference is that all of the work is done by PowerPivot rather than a business intelligence professional.

After the creating the workbook with an initial data import, you can add any number of additional data sets. The data sets, or data connections, don’t have to be SQL Server or Analysis Services databases. You can retrieve data from nearly any electronic data source including Access, raw text files, ATOM data feeds, SSRS reports, and virtually all popular database systems.

This is another place that it gets interesting. Have you ever wanted to combine data from multiple data sources that are only remotely related? For example: have you ever wondered how sales trends for a specific product relate to the movement of the stock markets in the same time period? Or would you like to know how total purchases in an area relate to the Fair Market Rent estimates, US Census Bureau data, the Consumer Price Index, or Local Area Employment Statistics? Maybe you have some specialized compliance issues and need to check your customers against the Denied Persons List with Denied US Export Privileges provided by the US Bureau of Industry and Security.

I think you get the point. PowerPivot enables you, the average user, to deep dive into your data and find facts and trends that bring an entirely new dimension to your business decision-making process.

Even with all of the data available these days, there is often something you just can’t find. That one comparison or aggregate that will make everything come together for you. The new Data Analysis Expressions (DAX) in PowerPivot are provided to fulfill that need. Using DAX, you can create completely new measuress. DAX is a set of data analysis functions that use syntax similar to Excel functions, making it much easier to learn for the Excel user. DAX includes simple operators such as +, -, *, and / which all have the same purpose as in Excel formulas. The difference lies in the complex multi-table analysis and calculations that you can use to create your own measures. DAX is also well equipped to help you perform time-based aggregations and analysis.

There is so much that DAX provides that it is worthy of a separate blog, or series of blogs. Perhaps I can address the specifics of using DAX in a later blog, after I have had a chance to dig a little deeper into it. In the mean time you can go to http://powerpivot-info.com/post/52-list-of-powerpivot-dax-functions-with-description to find a listing of the DAX functions currently available in PowerPivot.

In my next blog, I will look at creating PowerPivot worksheets to analyze the imported data.

Oh, if you really do want to look at some of the data sets mentioned earlier, you will find links to all of them at http://powerpivot-info.com/post/50-list-suggested-datasets-to-test-powerpivot.

Share

LINQ to SQL

Monday, January 25th, 2010

If you’re still writing SQL queries in code, you are really missing out.  Writing queries by hand is always error-prone and can lead to long debugging sessions.  As a data persistence model, LINQ to SQL is easy to use and really makes a difference in the quality of code that you write.

After using the LINQ Designer, you have a model of your tables in a standard looking Entity Relationship Diagram.  From there though, your code can query your database by simply working with objects.  If you like LINQ to Objects for working with in-memory collections, then you are most of the way there.

The cool thing about it is how the query generator works behind the scenes.  When you create a query using object calls, a T-SQL statement takes form with all of the necessary pieces in place.  It parameterizes, adds sub-queries , inserts ROW_NUMBER syntax , and does all of this without you needing to think about how it fits together on the database side.

What if policy prevents you from sending ad-hoc queries to the database though?  No problem!  LINQ to SQL lets you specify stored procedures to call for all CRUD operations, then stored procedures to call for business-specific needs.  You lose the query generation coolness at that point, but you retain the ability to work with database data as local objects without tedious work to create objects from result sets.

The objects returned from your LINQ operations provide fully-typed access to your data along with nearly effortless updates and inserts to the database.  You can focus on what to do with the objects, such as binding to user interface elements, rather than spending time integrating data access code.  Let your database team create the queries or stored procedures, then assign them to the objects with ease!

In my next post, I’ll talk more about the mechanics of specifying database hints to the LINQ to SQL designer, and how to make effective use of it all from code.

Share

Using Windows PowerShell as an IT Pro – Part 4

Monday, January 18th, 2010

In my last post I talked about the Get-help cmdlet and how it gives you detailed information on how the cmdlets are used. Now I will talk about snap-ins.

A Windows PowerShell snap-in is a Microsoft .NET Framework assembly that contains Windows PowerShell providers and/or cmdlets. Windows PowerShell includes a set of basic snap-ins, but you can extend the power and value of Windows PowerShell by adding snap-ins that contain providers and cmdlets that you create or get from others.

When you add a snap-in, the cmdlets and providers that it contains are immediately available for use in the current session, but the change affects only the current session.

To add the snap-in to all future sessions, save it in your Windows PowerShell profile. You can also use the Export-Console cmdlet to save the snap-in names to a console file and then use it in future sessions. You can even save multiple console files, each with a different set of snap-ins.

Windows PowerShell includes a set of Windows PowerShell snap-ins that contain the built-in providers and cmdlets. You can get a list of snapins in the current Windows PowerShell session by using the Get-PSSnapin cmdlet.

Get-PSSnapin

Get-PSSnapin01

If you wanted to get a list of the cmdlets in a snap-in you would use the Get-Command cmdlet with the PSSnapin parameter.

Get-Command -PSSnapin Microsoft.PowerShell.Security

Get-PSSnapin02

The built-in snap-ins are registered in the system and added to the default session when you start Windows PowerShell. You can also add other registered snap-ins to the current Windows PowerShell session by using the Add-PSSnapin cmdlet.

After the command is completed, the providers and cmdlets in the snap-in are available in the session. However, they are available only in the current session unless you save them. To use a snap-in in future Windows PowerShell sessions, add the Add-PSSnapin command to your Windows PowerShell profile, or export the snap-in names to a console file.

Add-PSSnapin Microsoft.Windows.AD

 

In my next Post we will look talk about how to retain a snap-in in Windows PowerShell so that you don’t have to add it each time you need it.

Share

Aw, Snap (Windows Snap)

Monday, January 18th, 2010

A few months ago when I upgraded my comptuters to Windows 7, I basically expected everything to behave as it did in previous versions of Windows, with several cool features added. But when Windows grabbed and rearranged with windows on my screen I fumed and thought why in the world can’t I simply put windows where I want them. Well, this was due to the new functionality called Windows Snap, which is really cool but you need to understand how to work with it before it becomes cool.

Windows Snap controls how you, with certain movements, resize and arrange windows. To arrange windows side by side using Snap, drag the windows that you want on the left to the left part of the screen. When you see an outline pop across the screen, let go of the window and it will automatically size to the left half of the screen. Do the same for the windows you want on the right side, and there you go.
****(Old method: minimize all windows, restore the window you want on the left, restore the window you want on the right, right-click the Windows task bar and then click Arrange All)

The feature of Windows Snap allows you to easily maximize a window by dragging it to the top of the screen. Again when you see a window outline expand across the screen, let the window go and it will maximize. To restore the window, just drag it away from the top of the screen. I’m not sure if this is easier, but it gives you a new way to do something.

The last option in Windows Snap lets you expand a window vertically to the full heigth of the screen. To do this one, line up your mouse on the top edge of a window so that the cursor turn into an up/down arrow, and drag this to the top of the screen. The window will expand to full height without changing the width of the window.

Share

IIS Database Manager and MySQL

Monday, January 18th, 2010

Microsoft has new extensions for Internet Information Services (IIS) 7.0 that can improve the functionality of your website. There is a new version of the IIS Database Manager extension. The new version 2.0 includes the ability to manage SQL Server 2005 and SQL Server 2008 databases inside the IIS 7 console. In addition, you can manage MySQL databases. Setting up the integration with MySQL functions the same as with SQL Server. You need to install MySQL and set up your website that uses a MySQL database. Something that may not be obvious is before you are able to add the MySQL database, you need to restart the IIS Manager console. After you restart the console, you will have the ability to add the MySQL database in the Database Manager section of the IIS Manager.

Share

File Sharing Between VMs and the Host Machine in Hyper-V

Monday, January 18th, 2010

If you are used to the copy/paste functionality between Virtual Machines and the host machine in Virtual PC and Virtual Server, you may wonder how to do this in Hyper-V. Let me detail one way to set up Hyper-V file sharing.

  • To set up sharing between the VM and the host machine, you will want to create an internal network in Hyper-V.
  • On the host machine, create a shared folder.
  • If you want to copy files from the VM to the shared folder, you will need to configure permissions on the shared folder. To do this:
  1. Click on the Sharing tab in the properties dialog box of the shared folder.
  2. Click Advanced Sharing.
  3. Click Permissions.
  4. Allow Everyone Change permissions.
  • On the VM, you need to open Network and Sharing Center. In Advanced Sharing Settings, turn on file and printer sharing.
  • Now, click Start, type \\<the name of your host machine>\<the name of the shared folder>, and then press ENTER.

You can now copy and paste files between the VM and host machine using this shared folder.

Share

Windows 7 Action Center

Monday, January 18th, 2010

Windows 7 Action Center
Sounds exciting, doesn’t it. Without getting into who named this for Windows 7, the Action Center is where you can manage issues that affect the performance and security of you system. When you access the Action Center (Control Panel | System and Security | Action Center) you are first provided with a list of recent events that occurred on the computer. For example, my laptop listed two times when WinRAR had stopped working, an issue with my NVIDIA Graphics Driver, it told me that there are problems that have not yet been reported to Microsoft, and that I still have to configure my backup settings now that I’ve completed the upgrade from Windows Vista to Windows 7. By clicking “Check for Solutions”, that notice goes away and the Operating System starts sending the error reports to Microsoft and checks for available solutions. During the upload process, the interface told me that more information would need to be sent to Microsoft which I allowed and it packaged up the required information.
Now at this point, you would expect it to tell me what it found. Instead the process completes silently in the background and then provides no additional information. This was my main complaint about the similar tool provided with Window Vista. You go through the reporting process, and then it doesn’t provide you with anything up front about what it found, even if it found nothing.
So now I’m not sure where to go. There is the option at the bottom of the window called Troubleshooting to find and fix problems, but this goes to the automated troubleshooting tools which I covered in an earlier post.
So that is the Maintenance section, the other half of the Action Center covers Security information. This gives you a single point of view for all the important security considerations for the system: Network firewall, Windows Update, Virus protection, Spyware and unwanted software protection, Internet security settings, User Account Control, and Network Access Protection. I do like this section because it lets you see the current status of each of these settings and then provides links to the relevant sections of the Control Panel.

Share

Using BitLocker To Go

Monday, January 18th, 2010

With Windows 7 you can now use BitLocker Drive Encryption with your USB storage devices with BitLocker To Go. This enables you to keep your own data, or your company’s data safer in case the USB storage device is lost, stolen, or misused. Now if you misplace your USB drive you don’t have to worry so much about someone examining your data since it will be encrypted.

BitLockerToGo02

You can choose to use a passphrase or even a smartcard to unlock the drive and access its contents. No specific hardware is required to use a passphrase and it allows one to easily roam inside and outside domains and organizations. The passphrase complexity and length requirements are managed by Group Policy. Smart Cards leverage the existing PKI infrastructure and you can roam to any computer running Windows 7 or Windows Server 2008 RC2. Note that this requires specific hardware but uses much stronger keys than the passphrase.

BitLockerToGo03

You can require that BitLocker be used for both removable and unremovable data drives. When the policy is enforced for removable data drives, all removable drives will require BitLocker protection in order to have write access. As soon as a drive is plugged into a machine, a dialog is displayed to the user to either enable BitLocker on the device or they will only have read-only access. The user gets full access only after encryption is completed and  can alternatively enable BitLocker at a later time.

BitLockerToGo01

You can also decrypt if you decide that you don’t want it encrypted anymore or you encountered some kind of compatibility issue. It is a simple process to perform.

BitLockerToGo04

Share

Windows 7 vs Windows Vista Control Panel

Monday, January 18th, 2010

Have you upgraded your Windows Vista computers to Windows 7 yet? If not, go do that and then come back here to read about there the new or improved system tools.

In Windows 7, when you open the Control Panel, you’ll notice that something has changed thought you probably won’t be able to tell what those changes are. I needed the side-by-side comparison to realize that the Windows Vista System And Maintenance and Security groups have been combined to form the new Windows 7 System and Security group.

 However, by going back to the Windows Vista computer, the System and Maintenance group has 10 subgroups and the Security group has 8 subgroups. In Windows 7, the System and Security group has consolidated the items into a total of 8 subgroups. So does this consolidation make it easier or harder to find the tools that you need.

Sidenote: There is a trend to redesign and change things for the sake of redesigning and changing them. This has been notable in the newspaper industry, driving even more customers away as they are no longer familiar and comfortable with the new format and design (i.e. Chicago Tribune). Let’s see how the Windows 7 designs work out.

Here is the breakdown of the groups in the System and Security group:

Action Center – New in Windows 7 – This is a very cool place where you can get a centralized view of the status of your computer from both a security and performance perspective.  The Action Center lists the status of security items such as the Network firewall, Windows Update, Virus Protection, Spyware protection, Internet security settings, and user account control. The Maintenance section will list the most recently events on the computer such as device or program errors.

Window Firewall – Part of the Vista Security group – This group allows you to manage the Windows Firewall on the system. You can reset the settings on the firewall to its default settings or review the settings for both Public and Private networks. The design for this interface is much better than Windows Vista in that it provides you with more information up front.

System – Part of the Vista System and Maintenance group– The System group basically brings you to the System information screen with the Windows edition information, system ratings and components, computer name with domain or workgroup settings, and the status of Windows Activation. You can also access Device Manager, Remote Settings, System Protection, and Advanced System Settings from this window. Nothing has changed here from Windows Vista.

 Windows Update – Part of the Vista Security group – Windows Update is pretty standard, install all the important ones because they are important to system security and install the optional ones so you don’t have to look at them.

Power Options – Part of the Vista System and Maintenance group –  You can manage the power consumption of your system in the Power Options window. Windows 7 still offers the three simple plans of Power Saver, Balanced, and High Performance, but the power options available in Windows 7 has been greatly expanded.

Backup and Restore – Part of the Vista System and Maintenance group –  Not much has changed here as far as the interface. In Windows Vista, something happened to the Windows backup on my computers that prevented them from running. Never resolved this problem, hope that it has been fixed for Windows 7.

BitLocker Drive Encryption  – Part of the Vista Security group – BitLocker encrypts the entire content of your hard drives or removable drives. Visually, nothing has changed here.

Administrative Tools – Part of the Vista System and Maintenance group – From Administrative Tools you can access more advanced tools for configuring and troubleshooting Windows. Many of these tools are familiar to users going back to Windows XP. They include Computer Management, Event Viewer, IIS Manager, System Configuration, and Services.

So the verdict on this redesign is that I like it, but expect to be annoyed when I have to go searching for the tools that I want to use. Comparing each of the items side by side, I generally prefer the Windows 7 look and feel of everything.

Share