Database Reference
In-Depth Information
Appendix A. Miscellaneous
Analysis
Services Topics
This appendix will cover:
• Considerations when building a SSAS solution against nonSQL Server data
sources
• Common yet confusing SSAS errors
• Dimension properties
• Performance considerations for many-to-many dimension relationships
• DirectQuery with Tabular Models
Working with non-SQL Server data
sources
As discussed throughout the topic you normally build cubes based on the SQL Server
relational database. SQL Server DBA's and cube developers don't always share
responsibilities, although some responsibilities may overlap. Generally, cube deve-
lopers have database owner permissions to the relational source, which allows them
to define necessary data structures for dimensions and partitions. However, in the real
world the aforementioned assumptions do not always hold. Analysis Services solution
can be developed on top of any relational data source to which you could connect to
using .NET or OLEDB providers. The data warehouse might be owned by a different
team than the one responsible for developing Analysis Services objects. Database
administrators might only be willing to provide read access to the tables and no per-
mission to create additional objects.
In the easiest scenario, you can exploit
SQL Server Integration Services
(
SSIS
)
to import data from a nonSQL Server relational source into a SQL Server database
and use it as the staging data repository. Once data is in the SQL Server you can
use either stored procedures or SSIS to load data into fact and dimension tables as
needed.
In a more complicated scenario, the company might not allow you to use the SQL
Server even for a relational data warehouse or a staging area. The reasons for this
Search WWH ::
Custom Search