January 20, 2010 at 4:35 am
I currently use Integration Services and Reporting Services quite extensively to query and populate data warehouses. I've always been curious as to what the third sibling, Analysis Services, does and whether it be of use to my organisation. What are Analysis Services benefits and what would we gain by using this above using Integration and Reporting Services please?
January 20, 2010 at 7:04 pm
Depending on how your data warehouse is setup this could really compliment what you have and provide a very robust analytical platform. SSAS provides a way to centralize all of your business logic, calculations, business definitions, KPIs, incorporate time and financial intelligence, and utilize data mining capabilities.
The SSAS engine can preaggregate the results and build aggregations based on usage to provide for quick data retrieval and results. This tool will also work well with SSRS and Excel 2007 to provide a rich environment for end-users to slice-and-dice the metrics based on the dimensions (entities) that you have defined and the attributes and hierarchies that you have configured for the user to navigate through the data with sub-second response.
If you are thinking about looking at PerformancePoint Server (PPS) then you will definitely want to explore SSAS to leverage the analytical capabilities, drilldown, cross-drill capabilities of PPS.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
January 21, 2010 at 4:42 am
So, are you saying SSAS provides a more intuitive platform for producing and displaying statistics from the data warehouse? In other words, I can do what SSAS would do in SSRS, SSIS populating my data warehouse, but in a far more laborious and inefficient (slow) way? Sounds like totals/aggregates are already pre-defined so data retrieval is a lot quicker.
Must admit, I had not heard of PerformancePoint Server before. Looks like it can be used to produce dashboards etc which management would love. I looked in to this a bit further following your response and found this link:
http://www.microsoft.com/bi/media/microsoftbi_intro.asx
Doesn't sound like we can purchase PerformancePoint Server 2007 anymore however as it's been incorporated in to Office Sharepoint Server Enterprise. Think cost may therefore be a big factor.
Thanks for the reply though. Has been very useful.
January 22, 2010 at 6:47 am
Do not confuse the purposes of the components of the MS SQL/BI stack.
THink of the SSAS as an alternative to RDB SQL component of the server.
SSAS firstly is a database engine, though the database there isn't stored in the traditional relational way, but rather in the multidimensional way.
There are other features there (on top of the abovementioned data storage) which facilitate the solution, but as I said --- you would use SSAS and MDX as the alternative to the usage of the RDB database and T-SQL.
VAL
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
January 22, 2010 at 7:29 am
Ewh. Would you say then that if we've already developed a SQL data warehouse being populated by SSIS packages and reported on via SSRS that it's probably not worth while swapping over to SSAS?
If so, can PerformancePoint Server be used to show dashboards etc overlaying data produced from our existing solution?
January 23, 2010 at 4:45 am
I wouldn't say that SSAS is an alternative to a relational data warehouse. SSAS compliments it and would be a natural progression. It does require a different skillset than a relational database and T-SQL, so you will want to make sure that you full understand how the engine works and are familiar with MDX and XMLA.
I would suggest taking a look at this article and I have pasted a section below from the article - Best Practices for Data Warehousing with SQL Server 2008
Several different architectures can be successfully employed for data warehouses; however, most involve:
· Extracting data from source systems, transforming it, and then loading it into a data warehouse
· Structuring the data in the warehouse as either third normal form tables or in a star/snowflake schema that is not normalized
· Moving the data into data marts, where it is often managed by a multidimensional engine
· Reporting in its broadest sense, which takes place from data in the warehouse and/or the data marts: reporting can take the form of everything from printed output and Microsoft Office Excel® spreadsheets through rapid multidimensional analysis to data mining.
SQL Server 2008 provides all the tools necessary to perform these tasks [MMD07].
· SQL Server Integration Services (SSIS) allows the creation and maintenance of ETL routines.
· If you use SQL Server as a data source, the Change Data Capture feature simplifies the extraction process enormously.
· The SQL Server database engine holds and manages the tables that make up your data warehouse.
· SQL Server Analysis Services (SSAS) manages an enhanced multidimensional form of the data, optimized for fast reporting and ease of understanding.
· SQL Server Reporting Services (SSRS) has a wide range of reporting abilities, including excellent integration with Excel and Microsoft Office Word. PerformancePoint Server™ makes it easy to visualize multidimensional data.
Here is an additional link for reference to further investigate the total capabilites of data warehousing with SQL Server - http://www.microsoft.com/sqlserver/2008/en/us/data-warehousing.aspx
As far as PPS, it will report off a relational database, but to full leverage the analytical capabilities you will want to reference an SSAS database.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
January 24, 2010 at 3:05 pm
Okay my bad. should not have said "alternative".
It is obviously not an alternative to the whole lot of TSQL business happening in the solutions, but it might become a great alternative to a large portion of data queries presently written in SQL.
I suggest us not talk about it much here, but leave the owner of the topic to the manuals and to their own discovery.
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
October 12, 2011 at 2:52 am
in which applications we use ssas cubes ?In which ssas will be used
October 12, 2011 at 2:53 am
In which projects ssas cubes will be used ?
October 12, 2011 at 3:43 am
This thread is almost Two years old, probably best to start a new one with your questions
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply