IMPORTING EXCEL POWER VIEW DASHBOARDS INTO POWER BI

If your organization is now a Power BI customer, congratulations. You’re now ready to create some very cool dashboards, integrate disparate and disconnected data sources and take advantage of Power BI’s ability to modify and transform your data, build interactive and dynamic dashboards and then share them with your team and organization. But until you create your dashboards to take advantage of the new visualization types and other improvements, you can easily import any existing Power View sheets in Excel into your Power BI site.

Above you’ll see an example of a Power View dashboard that I will import into my Power BI site.

Importing Power View into Power BI

To import Power View sheets into Power BI, navigate to your team’s Power BI site and click the Get Data button at the bottom left.

Then select where your data exists. In my case, I have my Power View reports in Excel saved in my One Drive folder so I’ll select Files.

You’ll have to log into your One Drive account. Once you’ve done that, just navigate to where your Excel file is and then click Connect.

After the import is complete, you’ll see your new Report and Dataset on the left in your My Workspace explorer. Open the new Report based on your Power View sheet. Now I can browse and interact with my Power View dashboard within my Power BI site.

I can also pin visualization from my Power View report to a Power BI dashboard with other visualizations.

And now that we have our Power View report imported and pinned to a dashboard can use Q&A to quickly generate visualizations using the imported data set.

Pretty cool stuff! It’s nice to know that I can easily jump from my legacy Power View dashboards to the new Power BI.

It’s important to not that in order to import Excel workbooks into your Power BI site, you’ll need to make sure that you have the latest version of Power Query installed before uploading.

Resources

For more information on importing data, take a look at the Power BI support page. There’s lots of good information there:
https://support.powerbi.com/knowledgebase/topics/63369-get-data

Power BI Tip: Use a Scatter Chart to Create a Calendar Report

The Scatter Chart in Power BI and Excel is very useful chart for visualizing three different metrics in tandem. But with a little bit of work you can use a Scatter Chart to create a Calendar chart for visualizing your metrics across the days of an individual month.

To configure a Scatter Chart too mimic a Calendar type report, you need the following:

1) An attribute for the day number of the week (1,2,3,4,5,6,7).
2) An attribute for the day number of the month (1,2,3…29,30,31).
2) An attribute for the week number of the month (1,2,3,4,5,6).
3) An attribute for sorting the week numbers in reverse order.
4) A business metric you wish to represent in the report.

Most of these items you can get from a traditional date dimension. In this example, I’m utilizing the Adventure Works DW database which has a date dimension table.

To set up the Scatter chart correctly, configure the visualization as seen in this screen shot from Power BI Desktop.

I want to point out a couple things here. First, you can optionally add a field to the Legend to differentiate between the weekend and weekdays or to identify holidays, as seen below.

Secondly, in order to display the Calendar in the correct order, we actually need to reverse the order to the Week numbers so that the first week of the month is numerically higher than the last week of the month. To do this I used a TSQL Case statement to populate the new column in the Adventure Works Date dimension table:

[WeekNumberOfMonth]  AS 

    ((datepart(week,[FullDateAlternateKey])

    -datepart(week,dateadd(month,datediff(month,(0),[FullDateAlternateKey]),(0))))+(1)),

[WeekNumberOfMonthReverse]  AS 

    (case (datepart(week,[FullDateAlternateKey])-datepart(week,dateadd(month,datediff(month,(0),[FullDateAlternateKey]),(0))))+(1) 

        when (1) then (6) 

        when (2) then (5) 

        when (3) then (4) 

        when (4) then (3) 

        when (5) then (2) 

        when (6) then (1)  

    end)

In the above code snippet, I also included the TSQL I used to create the WeekNumberOfMonth column.

You may also notice that I included the days of the week names at the bottom of the chart. I kind of cheated here as the Scatter chart doesn’t allow string fields on the X axis. I simply used a text box to rig this up but when the charts resize it shifts out of position, so be aware of this.

The final step is to hide all the axes and the background if you desire. In my case I left the data labels visible to display the day of the month number.

Resources

Interested in other Power BI tricks? Check these out:

Import Excel Power View Reports into Power BI
Clean Your Power Query Code
Create Cool Slicers with Treemaps, Funnels, Column & Bar Charts

Office 2016's Windows release tipped for September 22

Microsoft’s productivity suite is getting a big boost in collaboration features, but few design changes

The Windows version of Microsoft Office 2016 appears to be less than a month away, according to a leaked internal document.

A screenshot from Microsoft’s employee intranet lists a September 22 release date for Office 2016 on Windows, WinFuture.de reports. Microsoftreleased a Mac version for Office 365 subscribers in July, promising a standalone release in June.

It sounds like pricing will be similar to that of Office 2013, with WinFuture.de claiming a cost of 140 Euros for Home & Student, and 240 Euros for Home & Business. (In the United States, Microsoft currently charges $140 for Office 2013 Home & Student, and $220 for Office 2013 Home & Business.) Of course, Microsoft is still heavily pushing Office 365, which provides the latest version of Office for an annual subscription fee. Pricing starts at $70 per year for personal use on a single PC or Mac.

What’s different in Office 2016? In terms of design and layout, not much, though The Verge notes that the colorful menu bars found in Office’s mobile versions will make their way to the desktop. The big emphases in Office 2016 will be on real-time collaboration, with Microsoft finally adding support for real-time simultaneous editing. Desktop users will also get the “Tell Me” feature that appears in Office’s web and mobile apps, providing quick access to hidden settings through a text search box.

For more on what Office 2016 will bring, check out PCWorld’s hands-on preview. You can also try out the public preview for yourself.

Lead People, Manage Processes

Training: one of the many processes you manage daily as a business executive, and one that may not often fall as the most important task on your agenda. What does your company’s training program look like? Some companies have their training on autopilot, taught through a manual or informational video. Others simply throw their untrained employees in the mix, hoping they will pick things up on the job.

It’s time to rethink the traditional training process. By taking the time to lead employees through the training process, it will result in employees not only understand their tasks and processes better, but it’ll also instill a sense of ownership of the process that encourages the trainee to become a leader in what they do.

If you look at training from strictly a managerial lens, you’ll search for the quickest, easiest standardized way to get someone from point A to point B, ignoring the most important aspect of the equation - the employee’s personal learning style. If you've ever been trained in this manner, you know just how long it takes to finally master your tasks efficiently (it could take months).

Instead, look at training through a leadership lens, seeking out a process that will take advantage of your employee’s learning style and explain new tasks in such a way that said employee could in turn teach others the process. When coaching on the topic of training, I refer to John C. Maxwell’s five-step process of training from his book, Developing the Leaders Around You. Through modelling, mentioning, monitoring, motivating, and multiplying, he explains how leadership and guidance is crucial in the process of training:

Model: When you first begin the training process, start by simply doing the tasks while the individual being trained watches. Give the trainee the opportunity to see you go through the whole process. When the individual sees the task performed correctly and to its entirety, it gives them something to work towards duplicating. Modelling also is a great way to help shape perspective by showing the trainee how each small step leads up to the big picture.

Mentor:Continue to perform the task, but this time ask the trainee to come alongside and assist in the process. Take time to not only explain how the process works but also why each step is important to the process. Be approachable and available, during this step is when your trainee will have a lot of questions while they analytically work through how to complete each step.

Monitor:Have the trainee perform the task, and be available to assist and correct. During this step, make a point to be positive and encouraging to the trainee. Mistakes will happen, and it is important not to show frustration while your trainee is working to grasp the hang of things. Work with him or her until they develop consistency. Once they seem to have the process down, ask him or her to explain it to you as a true test of understanding.

Motivate:Take yourself out of the task and let the trainee go! Your role is now to make sure he or she knows what to do without help, and to keep encouraging them to do their best. At this time the trainee may want to make improvements to the process. Encourage them to do it, and at the same time learn from them. You never know what innovation they may have to make the process more efficient.

Multiply:Once the trainee does the job well, it becomes their turn to teach others. After taking the time to lead one person through the training process, your role stays in “motivator” mode and the cycle of training continues without you! As every teacher knows, the best way to learn something is to teach it.

Implement the five-step process of training in your organization and lead your employees from beginners to experts in their roles. You’ll trade off the time and effort you initially put in with the involvement in one trainee’s learning process for confident, knowledgeable leaders that teach each other and innovate the process while doing so.

Is Excel the Next Killer Business Intelligence Application?

Love it or hate it, export to Excel is still the most specified requirement in contemporary analytic tool selections, despite all the advances in business intelligence (BI) technologies. Excel is comfortable, flexible and with the new Microsoft Office 365 Excel Power BI add-ins (Power Query, Power Pivot, and Power Map), it's growing to become exponentially more powerful—pun intended. With the latest Microsoft strategy shift of embedding self-service BI applications right within Excel.

Excel in the BI World

Business users may love Excel, but in the professional BI world, it carries emotional baggage. Countless data warehouses and reporting applications have been built with the intent of removing Excel risks and dreaded "spreadmarts." Excel has been blamed for high profile analytic disasters such as JPMorgan's $6 billion trading loss. Keep in mind, people make mistakes using many tools—not just Excel—yet Excel remains an extremely popular attack target.

Quite a few data discovery and BI vendors advertise anti-Excel messaging in one way, shape, or form. Since Excel is where most analytic tasks are performed today, if Microsoft makes the right investment decisions and quickly executes, popular data discovery applications are most at-risk and they know it. Reviewing an admittedly subjective list of top requested data discovery tool capabilities, Excel Power BI already arguably meets or exceeds more than half of them and rapid releases new features monthly.

Most Common Data Discovery Tool Requirements*

Data discovery requirements typically are business user driven requirements (a.k.a. the masses). If Excel can meet most data discovery requirements, why would you buy another stand-alone tool?    

Microsoft Business Intelligence in Excel 2013, SharePoint 2013 and SQL Server 2012 SP1

Excel enables business users to do self-service Business Intelligence directly in the client, which now becomes a complete and powerful self-service BI tool - users have all they need in one familiar application in order to do data modeling, exploration and visualization of the data.

Also, SharePoint 2013 supports corporate deployments and significantly improves governance and compliance over the self-service BI assets. SQL Server 2012 SP1 is required for server side compatibility.

With these significant investments in Excel and SharePoint 2013 self-service BI becomes a natural part of user’s day-to-day activities. See below for more details!

New capabilities delivered for end users in Excel 2013:

• Ability to analyze data ranging from a few rows to hundreds of millions of rows with extreme analytical performance on your desktop using Excel data models powered by xVelocity in-memory analytics engine
• Opportunity to speed up analysis in Excel by easily cleaning up and shaping your data with Flash Fill; by previewing, applying conditional formatting, creating tables, charts and PivotTables using Quick Analysis; by easily navigating multidimensional and tabular data models and creating trend charts to analyze information over time using Quick Explore.
  Quick Explore enables a user to look at the product categories summarized by Sales Quantity with 1 click:
  
  Quick Analysis suggests a Pivot Table type with a preview of actual data:
  
• Empower users of all levels to access, mash-up and analyze data from virtually any source and rapidly create compelling analytical applications with PowerPivot. Click here to learn more about new features in PowerPivot in Excel 2013
  
  PowerPivot Diagram View shows the data relationship:

• Provide stunning data visualization to discover new insights at the speed of thought with a highly interactive and familiar data exploration, visualization, and presentation experience for users of all levels
  with Power View. Click hereto learn more about new features in Power View in Excel 2013
  
  Interactive visualization with Power View in Excel 2013:
  
  

New capabilities delivered for IT:

At the same time, we are strengthening our IT management and oversight capability by

• Enabling discovery and assessment of user-created spreadsheets with SharePoint 2013
• Enabling comparing spreadsheets, tracking lineage, conducting interactive diagnostics and creating spreadsheet analysis reports with Inquire in Excel

SharePoint 2013 discovery and assessment criteria setup:

Inquire in Excel showing spreadsheet lineage and workbook analysis:

Explore these new features yourself:

We are excited to deliver these new capabilities for our customers. But don’t just take our word for it – download Excel, SharePoint 2013 Preview and SQL Server 2012 SP1 CTP3 and try these features for yourself!

Download Office Professional Plus 2013 Preview here: http://go.microsoft.com/fwlink/p/?LinkId=256624

Download SharePoint 2013 Preview here: http://go.microsoft.com/fwlink/p/?LinkId=256619

Download SQL Server 2012 SP1 CTP3 here: http://go.microsoft.com/fwlink/?LinkID=251592&clcid=0x409

Learn more about Microsoft Business Intelligence here: http://microsoft.com/bi

5 reasons to implement Mobile Business Intelligence

Shifting work environments and new technologies driving enterprise mobility

According to the International Data Corporation, nearly one billion smart connected devices – PCs, media tablets, and smartphones – were shipped in 2011. That figure is predicted to top 1.1 billion in 2012, with shipments expected to double 2011’s tally and reach 1.84 billion units by 2016, as businesses of all shapes and sizes around the world show a nearly insatiable appetite for smart connected devices. Most significantly, IDC predicts that the number of mobile devices, principally smartphones in conjunction with tablets, are set to dwarf the number of PCs – outnumbering them by more than five to one. And, on the back of that growth, global internet users will double between now and 2015, with mobile users accounting for around 80 percent of consumption.

Enterprise mobility driving Mobile BI

1. Faster decision-making
Mobile BI: Actionable Intelligence for the Agile Enterprise – found that companies with Mobile BI could make critical business decisions six times faster than organizations without a mobile platform for reporting and analytics. The capacity to quickly identify trends, patterns and emergent opportunities (or threats) produces competitive advantage.

2. Better decision-making
Equipping mobile decision-makers with up-to-date fact-based information not only improves the speed with which information is transmitted and decisions are made, but the capacity for on-the-go personnel to improve the quality of their decision-making.

a. Collaboration
Mobile BI users can share reports, information and insight relating to BI content – with other authorized users – across devices and platforms to collectively solve problems and decide on the best course of action. Sharing new ideas and fresh perspectives creates a more effective and efficient decision-making process. Users are able to utilize the knowledge gained through reporting and analytics more efficiently and effectively to make better, faster decisions. For example, sales executives affected by a delayed shipment could email the stock level report to the marketing manager. Notifying the marketing team about the delay allows them to adjust the launch of their advertising campaign accordingly. Likewise, the CEO of Some Firm could set the agenda for 9 am AGM by commenting on or annotating a report and distributing it to the rest of the executive team during his morning commute.

3. Enhanced productivity
Mobile BI empowers decision-makers across all industry types and functional roles to utilize time more effectively and reduce inefficiencies: Executives can transform the airport lounge into strategic workshops, sales personnel can convert a prospect’s conference room into the perfect pitching environment, and line managers can match strategy with output on the shop floor.

4. Monitor business performance at a glance 
Mobile dashboards make monitoring KPIs easy, and ensures that executives always have their fingers firmly on the pulse of the business.  

5. Improved customer satisfaction 
Field personnel need to have an accurate understanding of their organization’s customer-base to do their job effectively. Pro-active alerts and access to both real-time and historical data, detailing current customer needs and past behaviors, will lead to increased efficiency and productivity ‘in the field’, and ultimately enhanced customer satisfaction.

New Features in SQL Server 2014

Here are 10 new features in SQL Server 2014.

1. In-Memory OLTP Engine

SQL Server 2014 enables memory optimization of selected tables and stored procedures. The In-Memory OLTP engine is designed for high concurrency and uses a new optimistic concurrency control  mechanism to eliminate locking delays. Microsoft states that customers can expect performance to be up to 20 times better than with SQL Server 2012 when using this new feature. For more information, check out “Rev Up Application Performance with the In-Memory OLTP Engine.”

2. AlwaysOn Enhancements

Microsoft has enhanced AlwaysOn integration by expanding the maximum number of secondary replicas from four to eight. Readable secondary replicas are now also available for read workloads, even when the primary replica is unavailable. In addition, SQL Server 2014 provides the new Add Azure Replica Wizard, which helps you create asynchronous secondary replicas in Windows Azure.

3. Buffer Pool Extension

SQL Server 2014 provides a new solid state disk (SSD) integration capability that lets you use SSDs to expand the SQL Server 2014 Buffer Pool as nonvolatile RAM (NvRAM). With the new Buffer Pool Extensions feature, you can use SSD drives to expand the buffer pool in systems that have maxed out their memory. Buffer Pool Extensions can provide performance gains for read-heavy OLTP workloads.

4. Updateable Columnstore Indexes

When Microsoft introduced the columnstore index in SQL Server 2012, it provided improved performance for data warehousing queries. For some queries, the columnstore indexes provided a tenfold performance improvement. However, to utilize the columnstore index, the underlying table had to be read-only. SQL Server 2014 eliminates this restriction with the new updateable Columnstore Index. The SQL Server 2014 Columnstore Index must use all the columns in the table and can’t be combined with other indexes.

5. Storage I/O control

The Resource Governor lets you limit the amount of CPU and memory that a given workload can consume. SQL Server 2014 extends the reach of the Resource Governor to manage storage I/O usage as well. The SQL Server 2014 Resource Governor can limit the physical I/Os issued for user threads in a given resource pool.

6. Power View for Multidimensional Models

Power View used to be limited to tabular data. However, with SQL Server 2014, Power View can now be used with multidimensional models (OLAP cubes) and can create a variety of data visualizations including tables, matrices, bubble charts, and geographical maps. Power View multidimensional models also support queries using Data Analysis Expressions (DAX).

7. Power BI for Office 365 Integration

Power BI for Office 365 is a cloud-based business intelligence (BI) solution that provides data navigation and visualization capabilities. Power BI for Office 365 includes Power Query (formerly code-named Data Explorer), Power Map (formerly code-named GeoFlow), Power Pivot, and Power View. 

8. SQL Server Data Tools for Business Intelligence

The new SQL Server Data Tools for BI (SSDT-BI) is used to create SQL Server Analysis Services (SSAS) models, SSRS reports, and SSIS packages. The new SSDT-BI supports SSAS and SSRS for SQL Server 2014 and earlier, but SSIS projects are limited to SQL Server 2014. In the pre-release version of SQL Server 2014, SQL Server Setup doesn’t install SSDT-BI.

9. Backup Encryption

One welcome addition to SQL Server 2014 is the ability to encrypt database backups for at-rest data protection. SQL Server 2014 supports several encryption algorithms, including Advanced Encryption Standard (AES) 128, AES 192, AES 256, and Triple DES. You must use a certificate or an asymmetric key to perform encryption for SQL Server 2014 backups.

10. SQL Server Managed Backup to Windows Azure

SQL Server 2014’s native backup supports Windows Azure integration. Although I’m not entirely convinced that I would want to depend on an Internet connection to restore my backups, on-premises SQL Server 2014 and Windows Azure virtual machine (VM) instances support backing up to Windows Azure storage. The Windows Azure backup integration is also fully built into SQL Server Management Studio (SSMS).

I Can Do It Approach

Whenever we set out on something new, there is one thing that frequently pops up, threatening to sabotage our efforts, and that is SELF-DOUBT. When it takes charge, that new thing suddenly seems very difficult and may even start to feel out of our reach.

Self doubt is one of the biggest reasons why people find it difficult to embrace change. It makes us believe we CAN’T rather than we CAN do that new job, new venture, new task or new challenge.

here are 5 practical tips that I’ve found very helpful and a bit easier to execute.

1. SEE LEARNINGS AS ACHIEVEMENTS

When you embark on something new, remember to give yourself the opportunity to learn the ropes and set your performance expectations accordingly. After all you haven’t done it before! Instead of focussing only on performance achievements related to the task, look at your learnings as achievements in their own right.                

I did this recently on a new project I have been working on and discovered I had 53 learnings and 14 achievements – so together that was 67 achievements in a few months. I was thinking my progress was a bit slow, but when I saw it this way, it totally transformed my view of how I had done.

2. IMAGINE BEING YOUR ROLE MODEL

"If you can imagine it, then you can make it happen."

What a fabulous concept this is. I love it! Such a great little tool for managing self-doubt. Here's how it works.

Think of a role model and imagine yourself being like them. Yes, imagine you can do everything they do and imagine yourself doing it confidently.

It’s not about comparing your current REAL SELF to them, but rather to see your IMAGINARY SELF performing just as well (if not better) than them! Make sure it’s believable though and if not, try imaging your role model as they were starting out or choose a different role model, one a bit closer to your current self.

3. THINK "I CAN" MORE THAN "I CAN'T"

Yes this is the mind-set one! But sometimes it is much easier said than done. No-one can be positive 100% of the time – that’s just not going to happen, is it?

But here’s the thing. If you can have yourself think I CAN even a little bit more often than you think I CAN’T, that will do it. Out of 10 thoughts about you and this task or goal, if 6 of them are positive and 4 are negative, that’s enough! It really is! Positive takes priority over negative thinking and the balance will soon tip towards cracking those doubtful, negative thoughts.

4. CREATE A FEEDBACK FILTER

We all love getting positive feedback. However, whilst most people’s intentions are good, it may not always be in your best interests. Sometimes it is full of their own stuff, coloured by the way they see the world or peppered with their own judgements. What you need is a feedback filter!

Be selective about the feedback you act on. Include highlights of your strengths, reminders of your achievements and advice that resonates with you - e.g. how you might improve on things you find difficult - these all serve you well! As for the other bits of feedback, simply leave them where they are. You don’t need to believe or act on everything everyone says.

5. MIND YOUR BODY LANGUAGE

Last and not least, we come to another one of my favourites. I saw it first on a TED talk about successful interviewing techniques - using body language – and what a powerful thing it is!

The way you stand, sit and move around has a big effect on the energy flow in, out and around your body. The more you can free up that flow, the better you will feel. Self-doubt doesn't survive in such an environment. It really  doesn't! Adopt body moves that encourage that flow – sit up straight, keep your head up, stand tall, puff your chest out when you breathe in, put your arms up in a victory pose, avoid crossing your arms and my all-time favourite … SMILE. It may be simple, but it definitely works!

STEP TO STARTUP A COMPANY

1) They are looking for you!
               

Start-ups are constantly looking out for new candidates with high potential, new ideas and unlimited zeal.










2) Do you fit the bill?

What do you need to know if you are apt for a company at a start-up because this isn't your usual 9-5 job? Read on to find out what are the essentials of a start-up employee and whether you have them or not?






3) Have vision and empathy

You completely understand and identify with the vision of the start-up and see it achieving the targets it has set for itself.










4) Committed

You are willing to work hard and use all your skills to help achieve the targets and goals of the start-up.











5) Ready to learn

Start-ups are the best place to learn about the field you are entering as they often provide you a wider scope of what the 'real world' looks like.










6) Can multi task

Such a job never comes with a rigid company profile. You could be web-developer who is also taking interviews as well taking part in the online marketing of the start-up.










7) Ready to be pushed

Start-jobs also help you test yourself. You will be pushed to do your best by your leaders, co-workers and sometimes your own belief in the project.









8) Can't work with red tape

If you feel bogged down by the idea of red-tapism, then start-ups are the place for you to be. It's usually a free environment with direct communication channels always open with your seniors.









9) Can and want to network

You often end up meeting awesome people while on the company head and these connections go a long way in improving your network for company.










10) Bear multiple responsibilities

As there is no strict company profile, you go on to shoulder many responsibilities but the rewards, like potential part ownership or growth in terms of position on management ladder, make it worthy.









11) Ready to be inspired

Startup often have an inspiring person or a group of people who have a strong belief in their vision. Employees of a startup have shared that they draw their inspiration from their leaders on a repeated basis.









12) Like awesome work environment

This is one of the highlights of a start-up. Many allow work from home, and even while in office, it's a relaxed environment in terms of no rigid cubicles or hierarchical division of space.