Upgrading a database can seem frightening! Fortunately, Agile BI offers exceptional tools from Microsoft and support to help you smoothly upgrade SQL Server 2005 to SQL Server 2014 and Microsoft Azure SQL Database before the end-of-support deadline on the 12th of April 2016. Find here three steps that can help make this an efficient upgrade:
First, do you know what is in your database environment?
For an efficient upgrade SQL Server 2005, it is necessary to organise everything before getting started. Step one in your planning process is to explore what is in your database environment: workloads, virtual or physical servers, what’s running on which versions and editions of software, and so on. For example, you will want to find out before upgrade SQL Server 2005 if SQL Server 2005 is running in your environment. Then, discover if any applications are running on SQL Server 2005 so you can protect critical data after the end-of-support date.
When you upgrade SQL Server 2005, Agile BI can help you running the Microsoft Assessment and Planning Toolkit, which automates the process of gathering data about your database environment. We can also help you find unidentified databases that might be impacted by end of support. Further we can also provide offerings to help you do a detailed assessment of your database environment and recommend next steps. The cooperation with the right partner can greatly reduce work and worries on an upgrade.
Second, do you target destinations for each application and workload?
One of the most important steps in upgrade SQL Server 2005 to SQL Server 2014 is the step to target destinations for each application and workload.
After collecting data about what is running in your IT environment, the next step to think about is where you will run your upgraded databases. When it comes to updating your data platform, you have a few options for where to move applications and workloads. You can
- upgrade your on-premises servers
- implement virtual machines
- or move to the cloud — a combination of three is the most common option
Microsoft published recently a guide about the SQL Server Upgrade Target Decision Tool to help you think through this process, compare your options, and identify upgrade targets based on your individual business needs.
How should I approach Consolidation and Virtualisation?
When you are upgrading it is the perfect time to look for ways to gain efficiency and manage performance — in this case consolidation and virtualisation. Have a look at the diagram which highlights how you might approach consolidating and virtualising your applications and workloads as well as taking Web applications to the cloud with Azure SQL Database.
It is possible to move certain applications and processes to upgraded on-premises servers to get the reliable performance necessary for mission-critical tasks.
If choosing to virtualise using Windows Server Hyper-V or Azure VMs, one of the great benefits of virtualisation is that you can run multiple applications or workloads on the same server while each maintains its own separate environment. Another benefit is that Azure VMs help further reduce total cost of ownership and increase availability. Because Azure VMs are hosted in the cloud, you can leverage the high availability of Microsoft’s cloud infrastructure while reducing capital expense for purchasing your own hardware and related IT costs.
Another option is to consolidate multiple databases or instances using Windows Server and Hyper-V to reduce IT sprawl, increase efficiency and free up resources for new tasks.
Moving your Web applications to the cloud and reducing administration is possible using Azure SQL Database, which is a database-as-a-service in the cloud. With this option, you get the benefits of a cloud infrastructure with enterprise-grade security and scale, high availability, superior performance and near-zero maintenance.
Are you ready for an increased ROI?
Of course you are! Check out the Microsoft Consulting study – Experience the benefits of SQL Server 2014 when migrating.
Third, have you found your upgrade strategy?
This is where an experienced and reliable Microsoft partner can help you. When upgrade SQL 2005 Agile BI can walk you through some common upgrade strategies and offer ways that you can approach the upgrade process. By planning thoughtfully, your upgrade will be less complicated, smoother and more cost-effective.
Larger upgrade projects can take six to 12 months to execute, so the time to contact Agile BI – a reliable Microsoft Gold Partner is now!
Marco Russo DAX expert: What is DAX? And why you have to know Marco Russo if you know DAX?
DAX (Data Analysis Expressions) is the native formula and query language for Microsoft Power Pivot and SQL Server Analysis Services Tabular models. Important to know is that DAX is NOT a programming language, DAX is a formula language!
DAX formulas are very similar to Excel formulas.
To create one, you type an equal sign, followed by a function name or expression, and any required values or arguments. Like Excel, DAX provides a variety of functions that you can use to work with perform calculations using dates and times, strings, or create conditional values.
Why is DAX so important?
It is not hard to create a workbook and import some data into it. You can even create PivotTables or PivotCharts that display essential information without using any DAX formulas. But, what if you need to analyse critical sales data across different product categories and for different date ranges? Or, you need combine important inventory data from several tables in different data sources? DAX formulas provide this capability and many other important capabilities as well. Learning how to create effective DAX formulas will help you get the most out of your data. When you get the information you need, you can begin to solve real business problems that affect your bottom line.
This is Business Intelligence, and Marco Russo DAX expert will help you get there.
Born and based in beautiful Turin (Italy), Marco Russo and Alberto Ferrari from SQLBI published in October this year the book “The Definitive Guide to DAX – Business intelligence with Microsoft Excel, SQL Server Analysis Services, and Power BI”.
This guide offers expert guidance on everything intermediate-to-advanced Excel users and BI professionals need to know, from the basics to innovative high-performance techniques. Marco Russo and Alberto Ferrari use this book as companion content for their courses Mastering DAX and Optimizing DAX. This book proofs the expertise of Marco Russo and Alberto as the Number one DAX experts in the world. Agile BI proudly announces that Marco Russo will present the first time ever in Australia his Mastering DAX and Optimizing DAX Trainings in Sydney and Melbourne.
Use the chance to get in touch with the DAX star Marco Russo at a training session of your choice in Sydney or Melbourne and ask all the questions you always wanted to ask about DAX!
Trying to get actual usage figures of any self service BI tool is pretty difficult – none of the big vendors will willingly release figures – and if they did they’d probably be suspect.
But in analytics sometimes there are useful proxies which, while not as accurate as hard numbers, can give a useful perspective. In this case, we will use our friend, google trends:
A quick note on methodology – i’m only looking for Qlikview, Tableau and Power BI. Unfortunately IBM Watson and its variants throw up too much noise because it’s part of a broader platform. Also I restricted the search to the US because Tableau is also french for “Table” so a worldwide search gets noise from that as well.
Self Service BI State of the Market
What you can see is that Tableau are the clear leaders in the self service field, with Qlik and Power BI trailing quite far behind. But from this data we can get a bit more insight. First of all in the last two years the market has gone from about 262 searches per month to around 468 (rolling 3m avg) implying a year on year market growth of around 40%.
Next up if we look at market share rather than sheer numbers, we see something interesting. Tableau has consistently held about 80% of the market. Naturally at the start of 2014, when Power BI was in its infancy, Qlik held the balance. However post the public release of Power BI that share has been declining rapidly – Qlik now holds about 11% of the market and Power BI about 7% and growing fast. This seems to be a result of Qlik having fairly static volumes and Power BI consuming a greater proportion of the growth.
What does the future hold?
Based on trends to date I would make the following headline predictions for self service BI in 2016:
- The Self Service market is set for continued high growth, with Tableau taking the lions share
- Power BI will experience strong growth and will consume more market share
- Qlikview may well be looking at a decline in not just market share but also volume
If you would like to know more, why not attend one of our Self Service BI workshops where we compare the tools. Check here for upcoming events: http://www.agilebi.com.au/events-list/
Ever since Microsoft announced that they acquired Datazen lots of people were not sure how Datazen fits into Microsoft BI and what the differences are between Datazen and Power BI as at first sight they seem to be similar tools.what the differences are between Datazen and Power
We from Agile BI researched and tested both tools. Here are our experiences:
Things that are good about Power BI:
+ Power BI offers a free option and a paid option with advantages such as being able to consume live data sources with full interactivity, access on-premises data using the Data Connectivity Gateways or collaborate with your team using Office 365 groups in Power BI.
+ The Power BI designer has a wide range of data sources to add such as Mailchimp or salesforce and they are about to release many more connectors in the coming months. The latest Data Connectors from July 2015 are appFigures, Quickbooks Online, Zendesk, Github, Twilio and SweetIQ.
+ Publish reports to PowerBI.com, directly from Power BI Desktop.
+ Power BI improved their report authoring with new visualisations such as Area Chart, Waterfall, Donut & Matrix. New visual formatting and customization options (labels, titles, background, legend, colours, etc.), insert Textbox and Picture in your report; Support for hyperlinks in reports and report tables; Undo/Redo actions.
+ PowerBI has a Question and Answer feature that can be very valuable for users who are not interested in diving into data sets, but who are looking for specific analytics, quickly.
+ The mashup data capability with the Power Query inside Excel or the Power BI Designer is a very important tool to search for data online or in your corporate data. Data can be imported to an Excel table. It is also possible to merge data replace values and other data modifications in a step-by-step process and rename columns. Also possible is the import of data to Power Pivot to work even more intense with your data.
+ Power BI Desktop provides you the safety that fields are correctly geocoded by setting the data category on the data fields. In Power BI Desktop, select the desired table, go to the Advanced ribbon and then set the Data Category to Address, City, Continent, Country/Region, Country, Postal Code, State or Province. These data categories help Bing to correctly encode the date.
+ It is easy to share Dashboards and KPIs with each other. A tool that can be a big advantage regarding sharing things with each other no matter where you are.
+ Power BI is available in 42 languages, which makes it working with companies overseas much easier and understandable.
Things that could be better about Power BI
– Power BI has limited visualisation for forecasting and statistical analysis.
– There seem to be some limitations in regards to being able to change credentials on a saved report.
– After our test Power BI was very slow when connecting to tabular SSAS cubes with over 50k rows. When putting a filter against that dataset, it takes a long time to display.
– Also there is no ability to edit/alias the field names of SSAS tabular cubes like it is possible in Power Pivot.
– There are no horizontal slicers.
– Power BI could be more user friendly.
– We wish that Microsoft Power BI was more easily accessible. As it is now, it requires either Excel 2010 (free download), or an enterprise version of Excel 2013 (for either Professional Plus or 365)- unless you are willing to purchase a standalone version of Excel 2013. Even if there is added cost, Power BI should be made available to non-commercial users within a standard downloadable Office suite or within Office 365.
– An enhanced drag-drop like in Tableau would be an advantage.
Things that are good about Datazen:
+ Power BI requires more technical skills to handle it, while Datazen is a tool that focused mainly on visualisations only which can make connecting to your data and creating a dashboard very easy, especially for people that want to have less technical effort.
+ Datazen publisher is for free. It is possible to download it from the Windows store and use local Excel files to create dashboards right away. But for sharing those dashboards or get data from your databases or other sources, access to a Datazen server is required.
+ With Datazen it is easier to see straight away what the dashboard is going to look like. You can make the decision which charts, graphs or KPIs you want to see.
+ Custom shape files are handled easily and have a variety of built in maps.
+ Datazen allows you to put your branding on top of your dashboards.
+ It is easy and very quick to create really nice Dashboards, but there are some limitations. See below cons of Datazen.
+ Team Collaboration with Datazen is possible as each Datazen KPI and dashboard includes a dedicated activity stream. Comments are ordered chronologically and contain context information, such as the value of the KPI at the moment when the comment was made.
+ If you want to refresh your data on a schedule there is an option for this.
Things that could be better about Datazen:
– Compared to the Power BI help tools on the internet via community or forum, Datazen offers almost no help online. It is hard to find information when you need help. Although here are some links where you possibly can find help. Datazen Support Blog, Microsoft Blog, Datazen website or Power Pivot Pro website.
– A small range of charts are offered by Datazen. Stacked bar is missing and line charts are limited. The colours of the charts and descriptions are hard to control, sometimes the ability toconfigure is simply missing.
– A scroll bar is missing at the data view, making it hard to use if there is lots of fields.
– Datazen is not a data discovery tool like Qlik or Tableau, it is focused on visualisation so you need to have your data organized in order to get the full effect or rely on what’s already in the data sources.
– The cross platform behavior isn’t always great as it didn’t work consistently between Windows, iOS and Android when we tested it.
– A Datazen Server is required to get data from other sources than local Excel files because almost every data source, except for local excel files has to be routed through Datazen Server. Here is some technical effort needed, when you have to produce a custom query for every data set that you want to expose to the users.
Our hints for users: Datazen is the easy to handle tool for less technical users that want to create easily nice dashboards and share them, especially for mobile users. But if you want to build your own data models or use your technical skills to sort data and fill it into the program, Power BI is the right choice. What Datazen definitely is missing is the big online community that Power BI has. This can either be because Microsoft focuses more on Power BI or not enough people are using Datazen yet to build such as community.
Check out how one of our customers, a leading vehicle manufacturer implemented Datazen successfully.
Ask us for free demos!
We from Agile BI hold lots of events and workshops during the year. We love to teach and inspire our attendees. But we also love the fact that we get at every workshop something back – feedback, contacts or inspiring questions. After every workshop our team comes together again and reflects the workshop. At this point we choose the most inspiring question from one of our attendees that we answer here again with some additional material. Either our attendees from our last workshop can read it again or our community gets new information.
The question of the day was at our Self-Service Data Analytics sessions where we informed attendees about the capabilities of Microsoft Power BI, Tableau, Qlik View and IBM Watson. We had four speakers who are very experienced with these four products and gave a good overview and insights.
One of our attendees described the following company situation and asked the following question after the presentations at our Q&A session:
“My organisation wants to get hands on data very quickly. Especially my sales and marketing team who have already had some training with programs and came back to get help from the IT guys in saying they have no time to learn too technical things – they want something easy to use. If I want to have a program that is pretty straight forward, where our employees can drag and drop and create some presentations very quickly which would be the easiest program for that?”
The answers of our presenters was that for Power BI and IBM Watson there are no technical skills needed. In terms of modelling Power BI is recommended as it is easy to use. It is possible to customize dashboards. If you want to see a certain info in the program it is possible to resize and change it very easily and you can ask questions in the program to give you the answer you are looking for.
Regarding learning curve, all the tools were very agreed to be quick to get started with, but they all ramped up in complexity once users want to do more advanced visualisations or data modelling. Power BI and Watson were the easiest to get started with, and for more advanced Data Modelling Power BI (though PowerPivot) was considered to be the easiest for inexperienced users. For advanced visualisations Tableau delivered the best results, but that came at a cost of time invested to make those visualisations.
In the last few weeks I have encountered a number of clients who have all faced the problem that their BI Solution has not achieved the adoption they had hoped for. This in turn has reduced the impact of the solution and thus the ROI. A common thread in the examples I have seen is the horrifying kitchen sink.
To explain to those not familiar with the idiom, to include “everything but the kitchen sink” means to “Include just about everything, whether appropriate or not”. What it means in this context is that in the BI solution so many dimensions, measures and KPI’s have been exposed to the user that the experience becomes confusing, overwhelming and ultimately useless.
Why building your BI solution is like making a hit movie.
No Hollywood movie is ever made without considering the audience appeal – they even use predictive analytics to drive scripting decisions. So why should your project be any different? You have consumers that need to be satisfied, and their wishes must be taken into account.
A key element of our BI Strategy Framework is to ensure that the end users different needs are taken into account. To do this we construct personas that define what level of detail gets exposed to each persona. To stretch our analogy a little further, your executive team may only care that there *is* a kitchen sink and whether it is working or not. A management team may need to know how hot the water is and how water efficient the tap is. The analysts will need to know detailed water usage statistics over time for analysis. Not everyone needs to know the same thing.
Most BI tools allow you to provide filtered views of the data model so that you can target even a very complex model to users with simple needs. An executive may only need a few key metrics and dimensions to explore before they pass further analysis downstream. The same applies for some end-users. If they are not technically minded they may find a much simpler model less intimidating. Just because you have a kitchen sink model doesn’t mean it works for everyone.
How do I make a smash hit?
Building and understanding the personas of your end user team is of course only part of the equation. There are significant human components in our BI Strategy Framework that need to be implemented. Change management, training and ongoing communication help ensure that what you deliver works. Contact us to find out more:
Data quality a uniform cause of deep pain in BI projects. The more systems that are involved the harder it gets to clear it up, before you even start accounting for how old they are, how up to speed the SME’s are, how poor front end validation was – there’s a host of potential problems. However something tells me that the number of BI projects where the customer has said that it’s OK if the numbers are wrong on the reports is going to remain pretty small.
Scope, Cost, Time – Choose one. But not that one.
I’m sure most of you are familiar with the Project Management Triangle which dictates that you vary two of Scope, Cost or Time to fix the other. The end result being that in the middle, Quality gets affected. In practice for BI projects Cost and Time tend to be least negotiable, so scope gets restricted. Yet, somehow Time and Cost get blown out anyway. Whilst BI is hardly unique in terms of cost and schedule overruns, there is one key driver which is neglected by traditional methods. Leaning once again on Larissa Moss’s Extreme Scoping approach, she calls out the reason. It’s because in a BI project Quality – specifically Data Quality – is also fixed. The data must be complete and the data must be accurate for it to be usable – and there is no room for negotiation on this. Given that the data effort consumes around 80% of a BI/DW projects budget, this becomes a significant concern.
How do we centralise Quality as a constraint?
So now we have to get the business to accept that the traditional levers can’t be pulled in the way they are used to and that requires a little end user education. The business needs to be made aware that it is a fixed constraint – one that they are imposing, albeit perhaps only implicitly – The business has to accept that if Quality is not a variable, then the three traditional “pick two to play with” becomes “prepare to vary all of them”. Larissa Moss refers to this as an “Information Age Mental Model” which centralises quality of output above all else. Here is where strong leadership comes into play. Ultimately if one business demands a certain piece of information the BI team will have to be clear to them that to obtain that piece of data to the quality which is mandated, they must be prepared to bear the costs of doing so, including the cost of bringing it up to a standard that means it is enterprise grade and reusable, so that it integrates with the whole solution for both past and future components of the system. This of course does not mean that an infinite budget is opened up to deal with each data item. Some data may not be worth the cost of acquisition. What it does mean is that the discussion about the costs can be more honest, and the consumer can be more aware of the drivers for the issues that will arise from trying to obtain their data.
Power BI general availability
Self Service Business Intelligence is the holy grail for many organisations. The ability to let users explore data by themselves and “cut the cord” with the IT department enables data driven decision making to improve business processes and identify new opportunities. Microsoft’s vision is cloud based, mobile friendly and priced very competitively – Power BI general availability was announced for the 24th July
After half a year with the beta version, Microsoft announced last Friday the good news and also added a full list of new features and capabilities.
Some of the exciting news are:
Power BI Desktop:
Currently the Power BI Designer is the preview of the Power BI Desktop. Contacts us for a free demo.
This is Microsoft’s new interactive reporting tool that includes powerful new visual data exploration and formatting.
The Power BI Desktop will be enhanced to include:
- New visualizations including matrix, area, waterfall, and donut charts.
- New visualization formatting such as color setting, titles, labels, and legends.
- New data source support with Zendesk, Intuit Quickbooks Online, AppFigures, GitHub, Twilio, and SweetIQ.
- Direct connection to SQL Server Analysis Services tabular models for data exploration.
Power BI web authoring and data exploration enhancements
The Power BI service has been enhanced with increased web-based authoring capability making it easier to create and format visualizations for dashboards and reports though the browser.
Power BI as an open source project
Microsoft is opening its commercial grade visualization framework and visuals built on D3.js, enabling customers and partners to easily extend and build custom visuals which will be available to use in Power BI in your dashboards, reports and content packs.
Power BI connects to other services
Due to his corporations with other companies, Power BI can easily connect to other services. A subscriber to a supported service can quickly connect to their account from Power BI and see their data through live dashboards and interactive reports that have been pre-built for them. One of them is the corporation with Mailchimp or Salesforce.
Direct connection to Apache Spark
Power BI users can connect directly to SQL Server Analysis Services, Azure SQL Database, and Azure SQL Data Warehouse, querying live against these data sources. Power BI now extends this support to Apache Spark for Big Data scenarios. Big data can be directly accessed with connected dashboards and reports that query live to the data from the popular Apache Spark open source framework.
And here is a full list of features that will be available in Power BI
- Globally available and localized to 44 languages
- New visuals – Tree Map, Combo Chart, Funnel, Gauges, Area Map, Waterfall, Donut, Area Chart, Matrix and others.
- Team collaboration and enhanced Excel support
- Significantly enhanced Power BI Desktop (renamed from Designer)
- Native touch optimized mobile apps for iOS, Android and Windows
- Rich control over visual coloring, including conditional formatting in Reports
- Visual formatting and customization in Reports (Title, Background Color, Legend, Data Labels, and X/Y axis for Cartesian charts)
- Support for Hyperlinks in Reports and report tables
- Report publishing directly from Power BI Desktop to the PowerBI.com service
- New styling, look and feel
- Simple and categorized Get Data Experience
- A dozen ISVs delivered content packs for Power BI, providing rich, out-of-the-box reports and dashboards for a variety of popular business services (Acumatica, appFigures, Google Analytics, MailChimp, Microsoft Dynamics Marketing, QuickBooks Online, SQL Server Database Auditing, SweetIQ, Twilio, UserVoice, Visual Studio Online)
- Four direct query sources enabling users to build Power BI reports and dashboards without the need to move data (Azure SQL Database, Azure SQL Data Warehouse, SQL Server Analysis Services, Spark on Azure HDInsight)
- Easy import of CSV files
- Support for connecting and refreshing from on-Premises data sources
- Enhanced data modeling in Power BI Desktop (Calculated Columns, Data Categorization, Smart DAX Formula Editor, 12 new DAX functions)
- Enhanced data transformation and mashup capabilities in Power BI Desktop with ten new transformation functions
- Direct report exploration over Analysis Services tabular model from Power BI Desktop
- Building and publishing Organizational content packs
- Power BI Groups to enable collaboration on a jointly owned set of datasets, reports and dashboards
- Browser rendered Excel reports in Power BI
- Power BI REST API
- Support for real time dashboards over massive data streams through Azure Stream Analytics
- Open source Power BI visualization stack to enable Developers to starting building custom visuals
- Developer samples and test bed for getting started quickly using the REST API
- New community forum site at community.powerbi.com
- Many, many more small but impactful enhancements sprinkled all across the product
Also the Power BI app for Android is now available
Ask us for a free demo!
Agile BI is a leading Data Analytics consulting firm based in Sydney. We are a Gold Microsoft partner with fully-certified experienced people that can implement, tailor, and support Microsoft Business Intelligence products and solutions.
- May and June Webinars: Power BI Security, Best Practices from the Microsoft Operations Team, Power BI Embedded, Marco Russo on Design and more! 20 Apr 2017
- Meet 1:1 with a Microsoft expert at Microsoft Data Insights Summit - book your appointment today! 20 Apr 2017
- Connecting to datasets in the Power BI service from Desktop 19 Apr 2017
- How Microsoft uses Power BI to run its growing cloud business 19 Apr 2017
Get in touch
1300 441 618
(02) 8088 7440
Suite 2, Level 1, 18-20 York Street, Sydney, NSW 2000