Power BI And Excel 2016 BI News

Originally posted on Chris Webb's BI Blog:

There have been quite a few Power BI and Office BI-related announcements over the last few weeks, and while I’ve tweeted about them (I’m @Technitrain if you’re not following me already) I though it would be a good idea to summarise them all in one post.

Power BI Announcements at Convergence and SQLBits

You’ve probably already seen the announcement today on the Power BI blog that Power BI is FINALLY available to those of us outside the USA:

http://blogs.msdn.com/b/powerbi/archive/2015/03/16/power-bi-preview-now-available-worldwide.aspx

At last! I’m sure MS had very good reasons why they couldn’t make the Power BI Preview available worldwide back in December, but this decision caused a lot of frustration in the MS BI community and I hope it’s not something that happens again. I can also confirm that the Power BI iPhone app is now available in the UK as well. The new data sources for Power BI that are…

View original 376 more words

Cortana

Getting Cortana if it is not visible or blocked for your region on Windows Phone 8.1

wp_ss_20141220_0008You can still get Cortana, even if your carrier has block/not rolled it out, provided you can do the manual updates.

Worth noting that some carriers will intentionally block the update for a while with phones already out there based on your regional settings, this is so they can sell the new ones with it enabled as a selling point. Hence the 630 got it before you could update the 920… but as I mention you can still get it, provided you can upgrade your phone to 8.1 and relatively recent updates, newer the updates the newer the version you will get and the more “languages and accents will be supported”.

Also worth knowing – you might already have it if you are the latest update, it might be that it is just not switched on, default is “off”… On most older phones, including 920, it is still classed as a “Beta” application which replaces the existing speech support built into those devices since 8.0…

Finding it if you already have it: -

wp_ss_20141220_0001wp_ss_20141220_0002open “Settings”

  • you will be on the default tab “system”, swipe to the side so you are on “applications”, if it is available with your current configuration, it will be listed. 
  • Click “Cortana”.
  • Turn it on.
  • Restart the phone if required.
  • Hold down search button to start it.

If blocked or not visible – getting it if you do not have it: -

This varies somewhat depending on your device and update availability… easiest thing to do is change everything to “English (United States)” turn on Cortana and then try adjusting things back to your region one by one… when Cortana stops working change whatever you last changed back to “English (United States)”

The three areas to change are “Region”, “Language” and “Speech”. Then you need to turn it on.

Cortana will only work if “Language” and “Speech” match and Cortana is available for that combination.

System Settings   System Settings

Here I have it working with both English UK and US note that “Language” matches “Speech”

open “Settings” 

  1. Region SettingsUnder the default tab “system” scroll down and go into “Region”.
  2. Change “country/region” to “United States” (if Cortana not available to you).
  3. Change “Regional format” to <your desired format> (for me “English (United Kingdom)”).  
    • This preserves your currency and date format.
  4. Click on “Restart Phone”.
  5. Check for any updates, and install them, restart as required.
    • This will get Cortana and other updates if not already installed.
       
  6. Language SettingsUnder the default tab “system” scroll down and go into “Language”.
  7. Change the language to match the region setting (“English (United States)”).
  8. Click on “Restart Phone”.
  9. Next bit differs depending on whether Cortana is available in your local language version… including the variations in English accent and pronunciation.
    • If it is, available in your language/accent.
      1. from within “Settings”.
      2. System Applicationsif on “system” tab, swipe to the side so you are on “applications”
      3. if available you will see Cortana at or near the top.
    • If it is not visible within “applications” tab (within “Settings”)
      1. Go back to the “system” tab.
      2. Scroll down to to “speech”.
      3. Go into “speech” and change the “Speech language” to “English (United States)” … or another one that is currently supported by cortana…

        System SettingsSystem SettingsNote: Depending on your current phone setup, you may need to download, then go back into this and reselect it to get it to install, follow the prompts, reboot as required and then check for any phone updates, which will get the updated speech package which includes cortana.

      4. Make sure “speech” matches “language”.
      5. System Application SettingsCortana SettingsOnce done with all the updates/install of language packs, as above, go into settings, swipe to side to get “applications” tab… and you will see Cortana listed… open it and switch it on.
      6. Restart phone if required, hold down search button an say hello to Cortana.

Finding the columns and values from AggregateResult (or QueryResult/sObject) in Salesforce.com APEX

I have been doing some quite intensive Saleforce.com (Visualforce.com) custom site development recently. This has involved full front to back-end development on multiple levels along with integration into various jQuery and charting frameworks.

One issue I ran into I could find no immediate recommended solutions in the various forums. This was having the ability to iterate through a result set, determine the column names and values.

While retrieving the data was relatively simple through SOQL, after employing the various workarounds for the lack of ability to do simple joins. Once I had my query I needed to consume it dynamically from multiple sources (different AggregateResult sets).

In order to make the application robust the consumption of the AggregateResult set needed to be dynamic, and be able to adapt to varying data that might be supplied to it i.e. for the chart output we would have some standard fields, but there would be additional fields the number of which would vary depending on the result set.

In most development languages there is some object model to verify that a column exists and if it does get your value out of it. AggregateResult is the same if you are using C#, the result set is XML so you can confirm structure, but within APEX there is currently no ability to do that.

At first I though I could use “<my agg res>.getSObjectType().getDescribe().fields.getMap()”, but I soon found that only returned one field, an internal ID field, unrelated to my data.

After searching for a quick solution, nothing obvious could be found, so had to figures something else out.

I did try to serialize the result set but that failed as well. Apparently sObjects cannot be serialized (within APEX), which I found odd.

While trying to debug and figure out a solution, I stumbled onto the fact that the AggregateResult object would convert to a string… that led me to the solution.

The string (at least for now) follows a predictable format:

string somethingToWorkWith = string.valueOf(myAggregateResult);

Will give me a string which has the format:

AggregateResult:{MyColumn1=Value1, MyColumn2=Value2}

This allows me to pull out all of my data, with their types intact. Below are a couple of functions, one will get me all my columns in a set, the other will get me all my columns and values in a key/value map. Note that in order to preserve the original type (class) of data the later function’s value is a generic object, and it is pulled from the original AggregateResult, rather than from the converted string array.

/**
* Returns set of columns in the supplied AggregateResult.
**/
public static Set<string> AggResColumns(AggregateResult a) {
    if (a == null) return new Set<string>();
        set<string> myColumns = new set<string>();
        string ar = string.valueOf(a).removeStart(‘AggregateResult:’).removeStart(‘{‘).removeEnd(‘}’);
        string[] ars = ar.split(‘, ‘, 0);
        for (string pair :ars) {
            if (pair != null && pair != && pair.indexOf(‘=’) > 0) {
            string[] keyAndValue = pair.split(‘=’, -2); //We are using -2 deliberately.
           if (keyAndValue.size() > 0 && keyAndValue[0] != null && keyAndValue[0] != ) {
                myColumns.add(keyAndValue[0]);
            }
        }
    }

    return myColumns;
}

/**
* Returns map of columns and values in the supplied AggregateResult.
**/
public static map<string, object> AggResColumnsAndValues(AggregateResult a) {
map<string, object> myValues = new map<string, object>();
string ar = string.valueOf(a).removeStart(‘AggregateResult:’).removeStart(‘{‘).removeEnd(‘}’);
   
string[] ars = ar.split(‘, ‘, 0);
   
for (string pair :ars) {
       
if (pair != null && pair != && pair.indexOf(‘=’) > 0) {
           
string[] keyAndValue = pair.split(‘=’, -2); //We are using -2 deliberately.
           
if (keyAndValue.size() > 0 && keyAndValue[0] != null && keyAndValue[0] != ) {
               
myValues.put(keyAndValue[0], a.get(keyAndValue[0]));
           
}
        }
    }

    return myValues;
}

Self Service BI within Manufacturing #SQLSaturdayEdinburgh #SQLPASS Presentation (#SQLSat)

SQL Saturday

Edinburgh First Conference CentreDuring mid April I was approached by Microsoft (UK) and asked if I would do a presentation at the Microsoft “Accelerate Your Insights” one day conference on the 1st of May 2014. Though hesitant and somewhat nervous about the prospect, as I had never spoken in public… I agreed and prepared a presentation.

The presentation was related to the recent Case Study I had the pleasure of being involved in through my employer, Jabil (@JabilCircuitInc). It would focus on how, at Jabil, we have progressed though the various backend SQL Server infrastructures offered by Microsoft over recent years and how we are using new technologies and features to enable BI delivery to our employees via production systems.

As a direct result of the presentation at Microsoft’s UK headquarters (Reading, UK) I was also invited to speak at a SQL Saturday (SQL PASS community event) being held in Edinburgh on 14th June 2014 at Edinburgh University Conference Centre.

Though hitting a bit of a technical snag with my work laptop, with less than 2 minutes to my presentation: –

Image

I quickly switched to my personal Surface Pro, which by pure chance I had decided to grab as I was leaving in the morning… had only took it so I had something light to play with between session. Just as well I did – quick switch, download of presentation from cloud storage and was good to go minus my demos.

Overall was able to buffer out the presentation, taking about several other aspects and areas we are working with SQL 2014 and BI… had several questions so am taking from that that audience engagement was good. Hopefully all that attended my presentation took away something that they did not know or at least found it useful.

Presentation can be downloaded both the SQL Saturday website and from here: –

Image

Thanks to Jen Stirrup (@JenStirrup) for the invitation to speak and arranging the great free training event; hope to be invited back in the future.

#SQLPASS #SQLSat #SQLSatEdinburgh #SQLSat281

Full case study @ http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?CaseStudyID=710000004223

Microsoft Case Study

Global Firm Takes an Evolutionary Leap in Data Management with Self-Service BI (Case Study)

A case study I was involved in just got published on Microsoft.com.

Over the past few years a lot of the work I have been involved in has been subject to NDAs, including this work with Microsoft (via my employer). Hence been unable to blog about my work or any of the great features of SQL Server 2014 or the Power BI suite of products.

Over the past year as part of the case study we were given advance access to SQL Server 2014 builds, Power BI and enhanced features of SharePoint. We also had assistance and regular contact with the SQL development team and Power BI guys.

As direct result of my participation I was lucky enough to enjoy a few trips to the USA, including to Seattle, Charlotte (for SQL PASS 2013 conference) and Tampa; making 2013 a very enjoyable and educational year for me :-) 

Business intelligence (BI) information is only valuable when the right users can discover, analyze, use and share it with others—and all in a timely manner. Current technologies produce data at overwhelming rates, often faster than business users can analyze it, and the bottleneck is frequently the time that it takes to generate useful and impactful reports. At US-based supply chain management giant Jabil, as in many enterprises, data analysis has long been a time-consuming and intensive collaboration between the business groups and IT, creating customized reports whose information, by the time it’s used, is already growing stale. With its new solution built on Microsoft SQL Server 2014 and SQL Server 2014 Power View, Jabil users can create their own reports in minutes from business critical data sources using Microsoft Excel, with IT providing training and guidance—freeing up time to work on strategic projects.

Full case study @ http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?CaseStudyID=710000004223

Download PDF of Case Study

Week of releases from Microsoft

 

Free Windows 8 Programming book, plus others – Kindle versions…

Programming Windows® 8 Apps with HTML, CSS, and JavaScript [Kindle Edition]

UK Link:
http://www.amazon.co.uk/gp/product/B009WS8FCG/ref=as_li_ss_tl?ie=UTF8&camp=1634&creative=19450&creativeASIN=B009WS8FCG&linkCode=as2&tag=paubunblo-21

US Link:
http://www.amazon.com/gp/product/B009WS8FCG/ref=as_li_ss_tl?ie=UTF8&camp=1634&creative=19450&creativeASIN=B009WS8FCG&linkCode=as2&tag=paubunblo-21

Canadian Link:
http://www.amazon.ca/gp/product/B009WS8FCG/ref=as_li_ss_tl?ie=UTF8&camp=1634&creative=19450&creativeASIN=B009WS8FCG&linkCode=as2&tag=paubunblo-21

Programming Windows 8 Apps with HTML, CSS, and JavaScript [Kindle Edition]


You can get around 10 additional Microsoft Press books (inc Windows Server 2012, Microsoft SQL 2012, Intro to Windows 8, etc) free for Kindle from:
http://www.amazon.co.uk/s/?_encoding=UTF8&camp=1634&creative=19450&keywords=microsoft%20press&linkCode=ur2&qid=1366291391&rh=n%3A341677031%2Ck%3Amicrosoft%20press&sort=price&tag=paubunblo-21

Or for US:
http://www.amazon.com/s/ref=sr_st?keywords=microsoft+press&qid=1366291726&rh=n%3A133140011%2Ck%3Amicrosoft+press&sort=price&tag=paubunblo-21

Visual Studio 2012 Update 2 Released

Tip

To download for team sharing/network deployment, download web installer from the link, then at command prompt use the command: –

“<download path>\VS2012.2.exe” /Layout “<my save path>”

replacing <download path> and <my save path> as required…. This will download the full install (1.8GB).

The same also works for SSDT (SQL Server Data Tools) installers.

Download now from http://www.microsoft.com/visualstudio/eng/downloads#d-visual-studio-2012-update or http://go.microsoft.com/fwlink/?LinkId=273878

Very nice code mapping features during debug and error tracing, watch the video below (or at here – about 25 minutes into video) for details.


Most of the following content has been extracted from http://www.microsoft.com/visualstudio/eng/visual-studio-update#story-update-2

46 minutes, 36 seconds

New Features include

Agile planning

Visual Studio 2012 introduced a new set of capabilities to support agile teams—on their terms. Update 2 adds new capabilities for your team to customize and get more out of its agile tooling. You can now add customizable columns to your Kanban boards so that they more accurately reflect your organization’s structure. Work item tagging helps teams to manage their work items by adding tags to get more out of their data.

Quality enablement

Maintaining quality throughout the development cycle is one of the key focus areas for Visual Studio 2012. In Update 2, you can continue to drive quality wherever you are through web access for Test Case Management. You can author and execute test cases remotely, making it easier for all members of your team to participate in test case reviews. You can also profile your unit tests to create better code by tracking the end to end flow of your code, including the unit test itself.

Line-of-business (LOB) application development

With Update 2 we continue to invest in making it easier for you to develop LOB applications. You can quickly create SharePoint apps and HTML5 client apps using LightSwitch. Just design your app and let the LightSwitch templates provide the fit and finish so you can get your LOB applications running quickly. With the addition of WPF, Silverlight and SketchFlow to Blend for Visual Studio, now you have everything you need for designing and coding your desktop applications in Visual Studio.

Developer experience

When you spend a lot of time developing software, you want tools that will provide an enjoyable developer experience. Update 2 includes enhancements and updates to improve the developer experience for Visual Studio 2012. Code map debugger integration gives you a visual representation of your code while debugging so you can identify issues faster. You can also create great apps for Windows Store using profiling enhancements to find issues earlier that could impact your users.

Plus more… Full description of Visual Studio 2012 Update 2 can be viewed at http://support.microsoft.com/kb/2797912

Additional downloads for Visual Studio 2012 Update 2 (Visual Studio 2012.2) are also available for:

Windows 8 Rocks!

Windows 8 Pro

I have been using Windows 8 full time now for just over a month, a fairly short period (I did have a VM running for a couple of months to check it out as well, though to be honest did not have much time to play with it).

I did not think I would be a fan, I tended to heavily customize and favour my start menu structure, but I can honestly say I don’t miss the start menu; hitting the start button and just typing one or two letters of what I want is so smooth and fast that I am kinda glad to be rid of my OCD organization of my start menu into organized sub folders and groupings.

The start screen is also growing on me, quite like the live tiles, even though I spend almost all my time in desktop mode and I have no touch screen, I still use it. It is easy to use and a nice change from small static icons.

Most significantly, as a rather heavy user of VMs and high memory development software, plus 30-50 windows open at any one time across 3 to 5 screens; is that the whole OS is lot faster and slicker, even with two or three VMs running in the background, all on a laptop, not a high spec desktop.

Hyper-V was the main reason I took the plunge, it’s integration is very nice; but having used the OS for everyday work, developing and maintaining everything from WPF, .NET and COM+ to old VB6 code, I actually regret not having migrated earlier…

Hyper-V On Windows 8 - Why?

Hyper-V On Windows 8 – Why?

Admittedly there are several hoops to jump through to get VB6 to install correctly, and our antivirus solution (McAfee 8.7i) took a bit of work to install (see https://developtheweb.wordpress.com/2013/02/13/trying-to-install-mcafee-8-7i-onto-a-windows-8-box/), but got them both working and do not have any apps I currently use that I have not been able to install (for Windows 7 SDK on Windows 8 box see https://developtheweb.wordpress.com/2013/03/04/windows-sdk-for-windows-7-and-net-framework-4-on-a-windows-8-pro-dev-box/ – same issue affects latest SP of Windows 7 as well).

All in all I’d happily recommend Windows 8 to anyone, whether for business, development or personal use.

Update: Forgot to mention another neat one… I use a lot of ISO disk images, Windows 8 supports them out of the box, no need for 3rd party tools and drivers, just right click and Mount; and you can mount many at once!

P.S. for VB 6 run setup in compatibility mode, deselect the Data Access components and Source Safe bits, install will say it fails at end but it hasn’t, it can be found in your program files directory. Then install VB6 SP6, which should pass. If you run into trouble with SP6 and it will not install, then make sure VB6 GUI is in the program files directory, (x86) on 64bit, and apply the following to your registry via a reg file: –

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MS Setup (ACME)]

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MS Setup (ACME)\Table Files]
“Visual Basic 6.0 Enterprise Edition@v6.0.0.0.0626 (1033)”=”C:\\Program Files (x86)\\Microsoft Visual Studio\\VB98\\Setup\\1033\\setup.stf”

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\Visual Basic 6.0 Enterprise Edition]
“DisplayName”=”Microsoft Visual Basic 6.0 Enterprise Edition”
“UninstallString”=”\”C:\\Program Files (x86)\\Microsoft Visual Studio\\VB98\\Setup\\1033\\Setup.exe\””

(change directories/reg key as appropriate, this one is for 64bit)


A nice vid on Windows 8, what’s changed and where to find your stuff…

(25 min)

Alternatively, from the same guy, for the tech savvy or those folks short of time – same content in just 4 minutes.

.NET Development :: Accessing Special Folders Location Across Different Windows Versions & Tightened Security Within Windows Environment

I am writing this post as I know quite a few developers only now migrating apps from Windows XP environments, most of whom are spending a lot of time fighting with the new tighter security world that started to come in with Windows Vista.

As most know; the directory structure for user files and temporary documents has changed over the life of Windows (“C:\Documents and Settings\…” is now “C:\Users\…”, Program Files location changes for 64 bit, etc).

In addition to this many of the folders and registry keys, that as a developer, you used to be able to write files and values to are no-longer accessible. In addition to this areas of the event log are also locked down, and writing to it can crash your application if not handled correctly.

So now on Windows Vista, Windows 7 and Windows 8, as a developer you cannot and should not be writing to Program Files, the root of any of your drives, Windows folder, etc, etc. This can also include the traditional temp folder (C:\Windows\Temp or C:\Temp).

The only places you can write to with any certainty are the user specific Temp folder, the ProgramData folder, or the users document store.

If you are having to deal with a legacy app you might have to manually (or programatically) override the security settings granting permissions to write files to the locations you need, though you should do so with caution, as Windows updates and security patches can reverse your changes.

All of these locations vary depending on the system setup and operating system, so what is the best way to handle it?

Well in any .NET App you can easily access any of these folders locations using the Environment namespace (System.Environment) and the SpecialFolder enumeration.

For example: –

Console.WriteLine(“Folder Path: {0}”,
System.Environment.GetFolderPath(System.Environment.SpecialFolder.ApplicationData));

A full list of the special folders can be found at http://msdn.microsoft.com/en-gb/library/system.environment.specialfolder.aspx

This is not a list of those accessible for writing to, it is a complete list.

In addition to these you can also access the traditional list of environment variables using: –

System.Environment.GetEnvironmentVariable(string);

Though for this you need to know what is available as it will cause an exception if you call for a variable that does not exist.

You can get a full list of what is on your current system with: –

var s = System.Environment.GetEnvironmentVariables();
foreach (System.Collections.DictionaryEntry item in s)
{
Console.WriteLine(“{0} = {1}”, item.Key, item.Value);
}

But you need to avoid app specific ones, and watch out for some that may have changed names over the years.

You can access the documentation (.NET 4.5) for the System.Environment class at http://msdn.microsoft.com/en-us/library/z8te35sa.aspx or via your Visual Studio help.


With regards to the Event Log, you need to make sure you create your event source during your app install (or have an admin add it into the appropriate event log). This may not be caught during development as most developers run as admin and may even turn off all the UAC protection.

If you don’t then the kind of error you might see in Visual Studio would look something like: –

Security Exception when trying to write to Event Log

Security Exception when trying to write to Event Log.
“The source was not found, but some or all event logs could not be searched. To create the source, you need permission to read all event logs to make sure that the new source name is unique. Inaccessible logs: Security.”

But all your users will see is something like: –

App crash when trying to write to Event Log

App crash when trying to write to Event Log

If you need to use the event log (which is good practice), then make sure you have created your source during your install and not during your exception handling.


As for the registry, unless your app is running in elevated mode, the only hive you now have access to is HKEY_CURRENT_USER, some of which itself may have been locked down, by specific apps to prevent changes. So if you need to read from HKEY_LOCAL_MACHINE then you should not be using CreateSubKey; but creating your keys/valuse during or app elevation and using OpenSubKey for reading… remember you exception capturing though, similar to Environment Variables, you’ll get an exception if you don’t have access or the key does not exist.