Favorite Quick & Easy DAX: Multiple FILTER Criteria
Did you know that you can enable passing multiple criteria in DAX to filter a calculated value in a column or measure?
Favorite Quick & Easy DAX: Calculate Moving Averages with EARLIER
Sometimes reporting data values day-by-day can create the impression of dramatic ups and downs, when really what we want to see is the general trend of changes over time. We need, in other words, to create a chart based on moving averages. Such a measure is commonly used in tracking the price of stocks, but there are many other cases where showing, for example, 7-day moving averages is preferable to just showing the value for a given day. DAX in Power Pivot can handle this calculation quite easily. Here’s how, using the EARLIER function
The 3 Elements Necessary For Guaranteed CRM User Adoption
Clients often ask me what's needed to guarantee user adoption on a new or re-designed CRM system. Over the years, I’ve whittled my response to these three essential elements.
How to Implement DQS (Data Quality Services)
Last fall, I attended SQL PASS in Seattle and learned about DQS (Data Quality Services) that Microsoft released with SQL 2012. It was perfect timing (I was in the middle of merging several third party data sources, and we'd decided to use MDS to maintain our domain data). This blog is a brief description of how I implemented DQS for our environment.
Favorite Quick & Easy DAX: Lookup Values
The more I work in Power Pivot, the more I’m amazed at what can be done using DAX in Excel, Microsoft’s powerful expression language for creating custom calculations, aggregations, and references within and across data entities. In this post, I take a look at the very useful LOOKUP VALUE expression, for when you want to scan any table in your data model, find a specific value, and relate that value to the current row.
How to Produce Visualization Reports with Excel 2013 Power View
In a previous blog post by Brett Stewart, he introduced the geo-coding capabilities that utilize the Map visualization feature in Power View add-in for Excel 2013. In this post, we'll look deeper into how to produce visualization reports with Excel 2013 Power View.
Favorite Quick & Easy DAX: Sum Distinct Values
In my last post, I explained how to create a Calculated Column that aggregates values in the Power Pivot data set for rows that meet specific filter criteria (by using the CALCULATE, SUMX, and ALLEXCEPT functions, you can customize exactly which rows you want to have included in your sums for a column). In this post, I explain how to do a distinct sum on that column, where you specify the values that you want to have included in the totals. We’ll look at how to use a DISTINCT so that you don’t double count duplicated data.
Use SQL Server 2012 with Dynamics CRM 2011
With the lifecycle of SQL Server 2012 in full swing, many companies are looking to upgrade their 2008/2008 R2 instances in order to take advantage of what 2012 has to offer - especially now that SQL 2012 Service Pack 1 (SP1) has been released with its additional new BI specific features.
Favorite Quick & Easy DAX: Calculate Percent of Total Scenario
One of the greatest benefits of creating a PowerPivot or tabular model is the ability to easily build custom calculations, using Data Analysis Expressions (DAX). In this post, I explain some of my favorite DAX expressions in the context of a common scenario – calculating the percentage of an individual value in a data set relative to a total value derived from summing across rows that meet specific filter criteria.
Examining Geo Data with Excel 2013 Power View
Geo visualization of data is an amazing way to find insights and meaning from information. These features are an awesome new tool in the “self-service” BI tool set available to the Office 2013 user.
Collaborative BI: A Quicker, Easier Way to Find the Business Data You Need
“Big data”. “Predictive modeling”. “In-memory analytics”. There are a lot of new technologies out there in the business intelligence space. But, as a business person with relatively basic reporting and analytic needs, I ask all my IT peers to give another trend more attention – Collaborative BI.
The Top 3 Reasons to Deploy PowerPivot to SQL Server Analysis Services (SSAS)
Microsoft’s PowerPivot add-in to Excel (2010 and 2013) is a powerful tool for business users to combine multiple types of data sources into a single pivot table. Learn the top three reasons to deploy PowerPivot to SQL Server Analysis Services (SSAS).
Formatting Excel to Populate DQS Parent-Child Relationships
Getting formatting errors when importing records from Excel into DQS? Here's a process that might help you.
Configuring Excel Services, PowerPivot & SharePoint 2013
Like most of Microsoft’s prominent consumer products, 2012 was also a banner year for the Server and Business Tools groups – with Office 2013, SharePoint 2013, and SQL Server 2012 SP1 being the notable items. Also, just like the consumer tools, the server/business tools have become much more integrated in this release cycle, with Office 2013 and SharePoint 2013 leading the charge. Excel Services – a part of SharePoint Enterprise since 2007, is leading this welcome trend.
A Quick Tip on DQS Data Mapping Validation
After importing our mapping data into DQS, the business I was working with wanted to verify that all the values were mapped/matched correctly. We had a lot of data, so scrolling thru the UI to visually test was not realistic, and we'd also need an export of the mappings. Here's how I did it.
SQL 2012 DQS Import Error “Value "" cannot be linked because it is not a leading value.”
SQL 2012 SP1 has some great features, one of which I saw at SQL PASS this past week, DQS (Data Quality Services).
Where is the SSIS Package configuration?
Wondering where the SSIS Package configuration is?
Microsoft’s Power Pivot Opens the Door to Agile, Self-Service, Low-Cost BI
Learn how Power View further realizes the “self-service” promise of the Microsoft BI stack.
How to Configure SQL Server Reporting Services 2012 for use in SharePoint 2010
Starting in SSRS 2012, installation has been greatly simplified. Here is a guide to configuring the new way of setting up SSRS 2012 in SharePoint 2010.
Self-Service BI. Really?
Self-Service BI. Really? Really. Get it with Microsoft’s PowerPivot, Power View, and SharePoint.
Managing Project Challenges: Logical Data Models
Some projects are challenging to deliver. Complex and convoluted ecosystems continue to grow. Company tribal knowledge makes it difficult to involve non-subject matter experts (SMEs).
Would You Adopt Your Own Business Intelligence?
Recently, Gartner published a report revealing that business intelligence (BI – Reports, scorecards, dashboards, and ad-hoc analytic tools) has been adopted by only 30% of business users.
Easy Windows Phone App Development with the Windows Phone 7.1 SDK
In the Windows Phone 7.1 SDK , Microsoft has included a number of template start-up projects to accelerate development on the Windows Mobile platform.
Forecast: Cloudy with Eventual Challenges
Understanding data sources for acquiring and integrating data is rarely a slam dunk. After all, each data source is like an employee in a company, unique and coming to the table with similar content and behavior.
The Truth About Front Office/Back Office Integrations
Front office / back office integrations set my teeth on edge. For anyone that has spent a significant amount of time working on them, you know that they often elicit fear from those involved.
A Process Framework in Action
In this month’s blog, we will look at a case study illustrating the power and importance of using a process framework to implement working solutions that achieve results.
A Critical Framework to Turn Goals into Results
Without clarity into how your company operates, it is easy to make uninformed decisions, which materialize in costly projects that deploy poor solutions that produce little benefit.
