Think of “continuum” as something you start and you never stop improving upon. In my mind, Business Analytics Continuum is continuous investment of resources to take business analytics capabilities to next level. So what are these levels?
Here are the visual representation of the concept:
Why do you want to do that?
One of the common tactic that you can consider to drive adoption of a Business Intelligence system is to integrate/embed the BI reports to the APPS/SITE that the users are already using. Don’t make your users come to you, go to them! As a part of that, I figured out a way to integrate/embed Power View in a Site that was used by existing user base.
You can integrate/embed Power View reports in SharePoint web Parts. Here’s How:
Image Credit & For step by step tutorial, please refer to: Integrate Power View with SharePoint using web parts
Note:
Environment: SQL Server 2012 in SharePoint Integrated mode w/ Power View Activated for the site. Also, SharePoint Enterprise 2010.
Power BI is an exciting new technology in the business analytics space from Microsoft. I’ve played with its current preview version & attended couple of sessions on Power BI at PASS Summit 2013. Based on my first impression, I noted down Problems that Power BI solves. Note that as of today, it’s in preview & so information around cost is not availale yet but I try to learn and understand as much as I can Today about how Power BI is going to help business users & power users in the future. As a part of that, I’m attending Business Analytics VC’s session on “Power BI Info Management and Data Stewardship” by Matthew Roche & Ofer Ashkenazi on Nov 7th 12 PM EST.
Topic Abstract:
“Business intelligence tools continue to improve, letting users shorten their time to insight and take that insight to more devices in more places. But this evolution of BI doesn’t change one fundamental fact of information management: You can’t gain insight from data you can’t access.
In this session, Matthew Roche and Ofer Ashkenazi will introduce the role of the data steward and the self-service information management capabilities included in Power Query and Power BI for Office 365, focusing on how Power BI empowers business users to add value to the organization.”
Closing note:
I recently volunteered at Business Analytics VC as VP of Marketing, so it’s in my interest to spread word about the event but I would not spread word about something unless it gets me personally excited about it! 🙂
I hope to see you at the session and for some reason if you can not make it, we usually record sessions & so you can check out the meeting archives section of the PASS BA VC site after the event.
Problem:
There’s an excel 2010 pivot table that’s getting its data from TestServer. How do I change the data source so that the Pivot Table get’s it data from ProdServer?
Note: This is a common scenario among Business Intelligence Developers who might want to switch between different servers (Test/Prod) to for comparing data.
Solution:
1. Establish the connection to a NEW data source via Data Tab in Excel.
2. Select the Pivot Table whose connection needs to be changed.
3. From the Excel Toolbar. Go to Pivot Table Tools > Options > Change Data Source
4. Choose the NEW connection from here. (you can use the connection that you created in step #1)
5. Click OK and that should have successfully changed the data source for you. Please test the column names/values that might have been affected since you changed the data source.
That’s about it for this post. Your comments are very welcome!
In this blog post, we’ll see steps to Parametrize a SQL Server Reporting Services report where data source is Analysis services (SSAS) cube:
Step 1: Let’s say you have reached to a point with your SSRS report where you’ve configured your data source, data sets and data fields that you want to use for your report. For the purpose of this blog post, I’ll be starting with a SSRS report that shows Sales VS country names:
Step 2: Now, let’s say the requirement are such that you want to parametrize the report by a data field in the Analysis services cube: continents
Step 3: Switch to Design View. Now navigate to query designer: Select your Data-set > Right Click > DataSet Properties > Query > Query Designer
Step 4: Drag the field to the filter area. For the purpose of this blog post, I am going to select Continent Name and add it to Filter area.
To add a field to filter area, there are two options:
#1: Select the field > Right click > Add to Filter
#2: Select the field > use your mouse to drag it to filter area
Step 5: Once you’ve added your desired field to the filter area, we’ll have to add it as parameter.
Now chances are that you are not seeing the parameter check box for this field because the dialog box is minimized. You can either maximize the dialog box or scroll to the right side of the filter area.
Once you see it, check it > click ok
Step 6: Once you’re back on Design View. Try “preview” report. you should be able to see the option to select parameter value before the report gets populated with data:
I selected Europe and then clicked on view report:
Step 7: One last thing, Let me also point out how you can change the properties of the parameters.
Go Back to design view > from the report data pane > Expand parameters folders > select the parameter > Parameter Properties
I’ll leave you with exploring what you can do with parameter properties! And with that I conclude this blog post, Your comments are very welcome!
This was a beginner’s level post, I’ll encourage you to follow up by watching three videos here: http://sqlserverbiblog.wordpress.com/2013/12/03/reporting-services-mdx-queries-video-tutorials/
Microsoft announced a cloud based business intelligence platform called Power BI – as a part of that, the project (in public preview) that was previously called “Data Explorer” will be released as “Power Query”. It’s a great tool that have used to find, clean and shape data in Excel 2010, very useful! So one of the first things I checked was whether Excel 2010 can run Power Query or not. Turns out, it does! It works with Excel 2010 professional plus (Please read the system requirements on the official download page for details)
And of course, I downloaded and installed it on my Excel 2010 professional plus.If you’ve not installed Office 2010 SP1 or higher, do that too.
Please note that this change affects some of the blog posts that I’ve published on this blog, Here’s the list:
1) Exploring, filtering and shaping web-based public data using Data Explorer Excel add-in
2) Web Scraping Tables using Excel add-in Data Explorer preview
3) Unpivoting data using the data explorer preview for Excel 2010/2013
4) Merging/Joining datasets in Excel using Data Explorer add-in
5) Remove Duplicates in Excel Tables using Data Explorer Add-in
That’s about it for this post. Update your “Data Explorer” tab to “Power Query” if you haven’t already! It’s a handy tool and I am glad to see that Data Explorer Power Query runs on Excel 2010 Pro Plus!
This is from the thread 
in the SQL Server PowerPivot for Excel forum.
Here’s the sample data:
| STATUS | WEEK | SALES | CUSTOMER |
| INVOICE | W01 | $150.00 | A |
| RETURN | W02 | $120.00 | B |
| INVOICE | W02 | $120.00 | B |
| INVOICE | W02 | $130.00 | C |
| INVOICE | W02 | $150.00 | D |
| INVOICE | W03 | $130.00 | E |
| INVOICE | W03 | $120.00 | F |
| RETURN | W01 | $150.00 | A |
| INVOICE | W04 | $100.00 | G |
| INVOICE | W05 | $150.00 | H |
| RETURN | W03 | $130.00 | E |
| RETURN | W02 | $120.00 | B |
| RETURN | W06 | $100.00 | I |
| INVOICE | W06 | $100.00 | I |
| RETURN | W05 | $150.00 | H |
What the user wanted was an output like this:
Without PowerPivo this is how the user was doing it:
“Create one pivot table filtered by INVOICE (WEEK in Columns, CUSTOMER in Rows) and second table filtered by RETURN (WEEK in Columns, CUSTOMER in Rows). Then manually calculate INVOICED pivot – RETURN pivot.”
Let’s see how DAX formula in PowerPivot can help the user so that it eliminates the “manual” calculation.
So Here are the steps:
Create two calculated measures:
Invoiced:=CALCULATE(SUM([SALES]),TABLENAME[STATUS]=”INVOICE”)
Returned:=CALCULATE(sum(DATA[SALES]),TABLENAME[STATUS]=”RETURN”)
Create one more calculated measure:
Invoiced-Returned:=[Invoiced]-[Returned]
Now from the usability standpoint, Hide measures created in step 1
Here’s the screenshot of the PowerPivot Model:
Let’s view this using PivotTables:
In this post, we saw how to create custom calculation to handle invoices and returns using PowerPivot DAX formula’s.
Data Explorer let’s you “Explore” (search) for web-based public data. This is a great way to combine data that you may have in your data-sources with public data sources for data analysis purposes. Sometimes your data might not tell you the reason behind the observed trends, so when that happens – you can try to see if a public data-set might give you the much-needed context. Let me give you an Example before we start hands-on w/ data explorer so that you have better understanding of importance of public datasets. Here’s a sample that I found here. So, Here’s a demo:
An auto company is seeing sales trends of Hybrid cars and SUV’s from the sales data-sources. But what is the reason behind that? company data does not show that. Someone hypothesizes that it might be because of gas prices. So they test out the hypothesis by combining gas prices information available via public data. And turns out gas prices might be the driving force of sales trends! SEE:
if the gas prices increase, then the sale of SUV go down and the sale of Hybrids go up:
You know that public data can be helpful! So how can you search for public data-sets? Well, You can manually search online, ask someone, browse through public data repositories like azure data market (and other data markets), there’s also a public data search engine! OR you can directly search for them via Data Explorer.
Here are the steps:
1) Excel 2010/2013 > Data Explorer Tab > Online Search > type “Tallest Buildings”
2) I selected one of the data-sets that said “Tallest completed building…. ”
3) Now let’s do some filtering and shaping. Here are the requirements:
– Hide columns: Image, notes & key
– clean columns that has heights data
– Show only city name in location
OK, let’s get to this one by one!
4) Hiding Columns:
Click on Filter & Shape button from the Query Settings:
Select Image Column > Right Click > Hide:
Repeat the steps for notes & key column.
Click on DONE
5) clean column that has heights data.
Click on Filter & Shape to open the query editor
A) let’s rename it. Select column > Right Click > Rename to Height > press ENTER
B) let’s remove the values in brackets. Select Column > right click > split column > By delimiter > At each occurrence of the delimiter > Custom and enter “(” > OK
This should transform the data like this:
Hide height.2 and rename the height.1 to height
Click on DONE
6) Let’s just have city names in the location column
click on Filter & shape to load query editor:
A) select location > right click > split column > by delimiter > Custom – Enter: ° in the text box like this:
click on OK
Hide Location.2, Location.3, Location.4 & Location.5
Select Location.1 > Right Click > Split Column > by Number of characters > Number of characters: 2 > Once, as far right as possible > OK
Hide Location.1.2 and rename Location.1.1 to Location
One last thing! making sure that the data type of height is numbers.
Select height > change type > number
Also,
Select floors > change type > number
click on DONE. Here’s our filtered and shaped data!
For the purpose of visualization I copied first 20 rows to a separate excel sheet and created a chart:
That’s about it for this post. Here are some related Posts on Data Explorer:
Unpivoting data using the data explorer preview for Excel 2010/2013
Merging/Joining datasets in Excel using Data Explorer add-in
Remove Duplicates in Excel Tables using Data Explorer Add-in
Web Scraping Tables using Excel add-in Data Explorer preview:
Your comments are very welcome!
Visual analytics is amazing – it helps “data enthusiasts” save time in answering questions using Data. Let’s see one such example. For the purpose of the blog post, I am going to show how to do it in Excel 2010:
Problem:
Here’s the Business Question: What was sales of Tea in North Region in 2012 Q1
Here’s the data:
| SALES DATA(2012 Q1) | East | West | Central | North | South |
| Coffee | $ 7,348.00 | $ 7,238.00 | $ 1,543.00 | $ 9,837.00 | $ 1,823.00 |
| Tea | $ 9,572.00 | $ 8,235.00 | $ 3,057.00 | $ 8,934.00 | $ 13,814.00 |
| Herbal Tea | $ 5,782.00 | $ 8,941.00 | $ 9,235.00 | $ 392.00 | $ 1,268.00 |
| Espresso | $ 9,012.00 | $ 2,590.00 | $ 4,289.00 | $ 7,848.00 | $ 340.00 |
So it’s easy to give out answer using the data: $8934
But let me CHANGE the business question:
WHICH Products in WHAT regions are doing the best?
Now this questions is not as easy as the previous one? WHY? because you’ll have to manually go through each number in a linear fashion to answer the question. Now imagine a bigger data-set. It’ll take even more time.
Solution
What can Excel Power users and Data Enthusiasts do to answer the new business question in an efficient way? Well, let’s see what conditional formatting can do it:
Now with the Data Bars, it’s easier to just glance at the report and see best performing products and regions. For instance, it’s very easy to spot that Tea is performing best in South among all products and region.
So how do you create data bars?
1. Select the data
2. Home > Conditional Formatting > Data Bars
3.Done! you’ll see this:
4. You can play with other options here to see what suits the best for your needs. But I just wanted to point out that there is a way for you to highlight the data in a way that helps you save time in answering business questions using data
Conclusion:
Visual analytics is a great way to quickly analyze data. In most cases, Human brain is much faster at interpreting the visual results as oppose to text/numbers – so why not use it to your advantage. And tools like Excel have inbuilt functionality to help you do that!
Business persons may not realize that Business Analytics project may involve significant efforts for the under the hood technical tasks like Data Cleaning, Data Integration, Building-a-data-warehouse, creating ETL processes, gathering business requirements among other tasks. And that explains the title of this blog: Business Analytics project is like an iceberg. It’s because, a business person may just see the tool used to visualize data but may not realize the work that went into making it “analytics-ready”. From a project management standpoint – before a project is initiated, the discussion about this different aspects of the project need to communicated to the business stakeholders so that they are in the know of efforts involved in building an analytics solution. And with that, Here’s the summary of this discussion in form of an Image:
Related articles