asked
SQL Server Analysis Services - who uses them?
We all know Sql Server Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality for business intelligence applications. Who has used this service in their projects? I need some guidance on what this really involves?
The Data Mining tools and the OLAP functionality are really two different things. My former company has implemented OLAP, but they're not using the Mining. As far as OLAP goes, you really, really, have to understand your data. You need to be able to identify areas where aggregation is going to help. I'd make sure you have a very good understanding of how to build a good star schema, because standard OLTP, while it can be used, is just not conducive to building cubes. The technology for building the cube is extremely simple to learn and implement. It's when you want to start putting on role based entitlement for security or taking direct control over what is displayed to the end users that you have to move into MDX. Then things get hard.
This is a good answer. As you mention, sooner or later you will have to implement dynamic security so that users browsing can only see data appropriate for them. Some MDX is required for this and also for creating calculated members, so to take full advantage of the technology you can't escape it. One additional thing I will say is: create proper dimensions. Which means a separate table with a surrogate key. I have worked on many cubes where the dimension is defined only as a query against a fact table to pull out the necessary data. This has been the source of cube processing failure 95% of the time due to missing dimension attributes. Doing it the right way with foreign keys, this can never happen due to referential integrity... Data mining is powerful if you have data germane to this type of analysis. I have only done rudimentary analysis with sql server's built in data mining. Even the decision trees algorithm is more impressive than I had originally thought esp. if you have more than a handful of features it is very good. Recently I have doing more external bayesian type analysis with the microsoft
infer.net libraries. If you are into machine learning definitely check it out
SSAS Cubes are mainly used for easy retrieval of data for creating reports and analysing the data from different perspectives to make crucial business decisions.SSAS plays a major role in the MS Business Intelligence. More often the historical data is stored in the SSAS cubes from the datamart(Fact and Dimension tables).Designing the SSAS Cubes is very important since the performance of the cube highly depends on the Cube design.
