Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

James Serra's Blog

James is currently a Senior Business Intelligence Architect/Developer and has over 20 years of IT experience. James started his career as a software developer, then became a DBA 12 years ago, and for the last five years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant, contractor, and owner of his own business. All these experiences along with continuous learning has helped James to develop many successful data warehouse and BI projects. James has earned the MCITP Business Developer 2008, MCITP Database Administrator 2008, and MCITP Database Developer 2008, and has a Bachelor of Science degree in Computer Engineering. His blog is at .

Microsoft BI tools: How they use data sources

A quick list of how each of these Microsoft BI tools handles the two data sources “SQL Server” (relational-based) and “Analysis Services” (multidimensional-based):

  • Report Builder – Using “SQL Server”, auto-detects joins if source system has foreign-key relationships (by selected “Auto Detect” relationships on the “Design a query” screen).  Otherwise will have to create your own joins.  If use “Analysis Services”, will get a different query designer, and has the benefit of not needing to create joins as a cube has them built-in
  • PowerPivot –  Using “SQL Server”, auto-detects joins if source system has foreign-key relationships (via “Select Related Tables” button on the “Table Import Wizard” screen).  Otherwise will have to create your own joins.  If use “Analysis Services”, will get a different Table Import Wizard, very similar to one in Report Builder, and has the benefit of not needing to create joins as a cube has them built-in, but the result returns just one flattened table.  I like to think of PowerPivot as essentially a way of making an analysis services cube from a relational source using Excel as the design tool
  • PerformancePoint – If use “Analysis Services” has the benefit of not needing to create joins as a cube has them built-in.  “SQL Server” can only be used to represent tables as KPIs on scorecards or have them appear as data values within filters (see http://www.jamesserra.com/archive/2012/10/using-performancepoint-against-tabular-data/)
  • Excel Pivot Tables – If use “Analysis Services” has the benefit of not needing to create joins as a cube has them built-in.  If use “SQL Server”, can only use one table
  • Power View – Can only connect to the Tabular model and the multidimensional model (which is in CTP, see Microsoft SQL Server 2012 With Power View For Multidimensional Models CTP)
  • Visual Studio Reporting Services (SSRS) - Using “SQL Server”, auto-detects joins if source system has foreign-key relationships (when adding tables on the “Query Designer” screen).  Otherwise will have to create your own joins.  If use “Analysis Services”, will get a different query designer, very similar to one in Report Builder, and has the benefit of not needing to create joins as a cube has them built-in

Comments

Leave a comment on the original post [www.jamesserra.com, opens in a new window]

Loading comments...