Month: January 2014

SQL Server Reporting Services: What are Drill Down & Drill Through reports?

Standard

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.

SQL Server Reporting services Drill Down reportDrill 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:

SQL Server Drill Through Reporting ServicesSo 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.

Related resources:
Drillthrough, Drilldown, Subreports, and Nested Data Regions (Report Builder and SSRS)

Author
Paras Doshi

Notes from Atlanta SQL BI meeting: “Bus Matrix”

Standard

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.

Thank you Bill & Meeting organizers!

Three Power Pivot Installation FAQ’s:

Standard

Q1: How Can I upgrade Power Pivot on my machine?

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.

Power Pivot Settings Excel

I hope this helps!

Business Intelligence Project for an International Law firm

Human Resource Analytics
Standard

Business Summary:
–          Customer was in need for a HR Analytics Solution.

Technical Summary:

–          Extract, clean & Standardize HR data from multiple offices
–          Formulate Business Metrics in the cube/semantic layer
–          Create a Business Intelligence presentation layer

Mockup:

Human Resource Analytics

#sqlpass business analytics VC event tommorow 16th Jan! “Analytics at Cisco”

Standard

Featured Presentation:

Analytics at Cisco

Date:  16th Jan. Time: 12 Noon EST.

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.

URL: http://bit.ly/PASSBAVC

Time Intelligence in DAX: Last n days

Standard

Problem:

How to formulate Last n days in DAX?

Solution:

It can be achieved using the combination of CALCULATE & DATESINPERIOD function.

Formula to sum up values for last 10 days

[code type=”sql”]
MeasureName:=CALCULATE(SUM([YourColumnName]),DATESINPERIOD(Calendar[DateKey],TODAY(),-10,day))
[/code]

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.

BI project for Digital Marketing agency: How to show value of social media marketing?

Gallery

Business Goal: Build customer loyalty by reporting ROI of agencies work to their customer/client.

Solution:

-Interviewed CEO, COO & account executives to understand their problem

– Their biggest issue was quantifying value of social media marketing to their customers

-Structured the problem in three main areas

1.Lack of metrics available from their tools

2.Lack of data analysis skills to formulate metrics

3.Lack of easy-to-follow internal/external frameworks

-Collected data about some customers to gather insights

-Worked with account-executives to help them present data to some of their customers

-Delivered a framework that account executives could use to demonstrate value & return on investment of social media marketing to their customers

Mockup:

Portfolio Digital Marketing Agency

SSRS: Why I prefer shared data source over embedded data source?

Standard

In one word. Manageability.

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:

1.

SSRS Add Data Source2.

SSRS embedded vs shared connection

conclusion:

In this blog post, we saw how shared data source can help you manage your reports efficiently.