Analyzing data at summary level is great! it’s gives business users information that they need at summary level to make informed business decisions. But often, they also need to look at details. To satisfy this business requirement, there are two types of reports that you can create in SSRS to show detail level information
1. Drill Down Report.
2. Drill Through report.
So what is the difference in their layout?
Drill Down report:
Note the presence of Toggle Items. + for expand. and – for collapse. As you can see, this makes it easy to go a level below and see the details.
Drill Through report:
Note the presence of a formatted text. It’s shown like a hyper linked text. For this report, To see Product level detail of Bikes Category, click on Sales Amount for Bikes category & it would take you a drill through report that shows the details:
So when to choose Drill Down report vs Drill Through report?
They have different feature & serves different purposes. But there are two things that help me decide in majority of the cases:
1. Is the performance of Drill Down report acceptable (especially if data is surfaced through stored-procedures/queries hitting the data source without a semantic layer in between)?
Yes? Great. If not, then try drill through reports.
2. Are there too many (30-40+) data items show if a user drill downs? Yes? Try drill through.
Bus matrix is a very important (if not the most important) topic for a Data Warehouse Developer/Architect. And it was great to re-visit and grow knowledge around this topic at Atlanta SQL BI meeting presented by Bill Anton (b|t).
Here are some of my notes:
Dimensional Modeling is more important than ever specially w/ the rise in self-service BI. it’s important to lay out data in an intuitive way to business users.
Dimensional Modeling is great! But at the same time it’s important to communicate this to business users. The solution doesn’t involve teaching ER diagrams to users, simplified designs, offloading mapping data to business processes work to business users. So, what’s the solution? That’s where Bus Matrix comes into picture!
Bus Matrix is a 2 dimensional intersection of Facts & Dimensions.
Bus matrix can not only help in communication w/ Business users, it can also be useful for IT in project planning and documentation purposes.
In the requirement gathering phase, a Bus Matrix could be a 2 dimensional intersection of Subject Areas (Sales, Inventory, etc) by Source Systems w/ priorities shown at the intersection. It’s great for road-mapping a BI/DW project.
Business Matrix is created during requirements gathering stage (subject area vs source systems), before development (Facts vs Dimensions) and needs to be kept updated over time. After the solution is live, it could be one of the important end-user documentation.
Also, Here’s a great resource page posted by the Presenter (includes his slides): http://byobi.com/blog/bus-matrix/ – check it out! it has some great resources in there.
A1: Uninstall Existing version (Yes, it’s not intuitive, but you have to uninstall existing version). Download the version that you want to upgrade to. Install it.
Q2: I’ve SQL 2008 R2 on my machine. Can I install “Microsoft® SQL Server® 2012 SP1 PowerPivot for Microsoft Excel® 2010”?
A2: Yes, go ahead! “SQL server 2012 sp1” in the name is confusing 🙂 It doesn’t matter which version of SQL server you’ve on your machine – In fact, Power Pivot does not need a SQL server to be installed on your machine.
Q3: How do I check Power Pivot version?
A3: Excel > Power Pivot > Settings > You’ll see version # on the top part of the screen.
Arun Saksena, Director, Data Science and Analytics Cisco Consulting Services
Big Data and Analytics is an important component of Cisco’s strategy to become the #1 IT company in the world. Cisco is taking a unique, network-differentiated approach to Big Data and Analytics. I will briefly discuss Cisco’s strategy and approach to Data Analytics, and describe the Analytics Services that we offer to customers. I will then discuss our approach to Data Science, and the skills we are developing within Cisco in the area of Data Science and Analytics. I’ll also discuss the role of a Data Scientist on cross-functional teams, and close off by sharing a perspective on how this role is expected to evolve in the next few years.
Note the use of a calendar table in the DATESINPERIOD. It is pretty common to have a “date” dimension & I’ve used it the DateKey from the dimension as the “date” column which is required by the DATESINPERIOD function.
You can use the above example as a starting point now.
Let me explain. In SSRS, reports need data sources. And data sources can be of two types: 1) Embedded 2) Shared. When a report uses the embedded data source, the data source properties are stored along with the report. If you’ve 5 reports that use the same data source & you decide to go via the embedded data source route then you’ll have 5 data source properties. If you need to change the data source property like change the server name then you’ll have to edit 5 different data source properties. Not efficient!
But if you use the Shared Data Source, then you can update data source properties from one location. Multiple reports can link to the shared data source & if there is a need to change the data source property, then you’ll have to do it only once.
Other benefit, you can take a data source offline if you ever have that need. This will pause all reports that are using this data source & also, stop all report subscriptions.
So, where do you configure embedded/shared data source? When you “Add a data source” to a report, you have the option to choose the data source type:
In this blog post, we saw how shared data source can help you manage your reports efficiently.