What are the benefits and challenges of using multiple data sources in a data source view?

由人工智能和领英社区提供技术支持

Data source views (DSVs) are a way of defining logical models of your data sources in SQL Server Analysis Services (SSAS). They allow you to combine data from different sources, such as relational databases, flat files, or web services, and apply transformations, calculations, and filters to create a unified view of your data. In this article, you will learn about the benefits and challenges of using multiple data sources in a DSV, and how to create and manage them in SSAS.

此文章中的业界达人

由社区从 6 条内容中精选。了解更多

Boka Aga

Cloud Migration | Data Warehouse | BI & Data Solutions
Alex Bishop

Senior Technical Consultant at Domo
Sarah Mabrouk

Chemical engineer- Aspiring Data Scientist

1 Benefits of multiple data sources

Using multiple data sources in a DSV can provide a variety of benefits, such as enriching your data with external information, integrating data from various systems, and reducing the complexity and size of your data sources. Additionally, it can improve the performance and scalability of your SSAS projects by optimizing the data access and processing.

添加您的观点

Alex Bishop

Senior Technical Consultant at Domo
举报内容
I've found that another benefit of being able to use multiple data sets is that you can instantly and accurately see the big picture which would have been impossible before without lots of different reports

已翻译

赞
Sarah Mabrouk

Chemical engineer- Aspiring Data Scientist
举报内容
Using multiple Data sources can also help with improving Decision-making: the access to diversified datasets helps Businesses make more informed decisions by having different angles to view the problem, which can also help identify trends, correlations and understanding that may not be visible from a single source.

已翻译

赞

2 Challenges of multiple data sources

Using multiple data sources in a DSV can be a challenge, as it requires ensuring the data quality and consistency across different sources, handling the data security and privacy issues, managing the data dependencies and relationships, and dealing with the data refresh and synchronization. To do so, it is necessary to validate, cleanse, and standardize the data; apply authentication, authorization, and encryption mechanisms; define primary and foreign keys, joins, and cardinalities; and set up schedules, triggers, and notifications.

添加您的观点

Boka Aga

Cloud Migration | Data Warehouse | BI & Data Solutions
举报内容
Moreover, multiple data sources a in DSV require very careful mapping to correctly align the fields and their respective schemas. This could be a very time consuming and expensive task esp in big projects.

已翻译

赞
Alex Bishop

Senior Technical Consultant at Domo
举报内容
Very often this is the layer where both you and the business discover the biggest challenges. The requirements may sound simple enough on paper to the business, but the granularity and relationships are often overlooked by the business as 'tech problems to solve' when in reality it requires a collaboration.

已翻译

赞

3 How to create a DSV

To create a DSV in SSAS, you need to begin by right-clicking on the Data Source Views folder in the Solution Explorer and selecting New Data Source View. From there, you can select the data sources that you want to include in your DSV, either by adding new ones or using existing ones. On the Select Tables and Views page, choose the tables and views that you want to include in your DSV. Additionally, you can add named queries or named calculations to create custom expressions or transformations. Finally, name your DSV in the Completing the Wizard page and click Finish.

添加您的观点

Alex Bishop

Senior Technical Consultant at Domo
(已编辑)
举报内容
Data source views start earlier than the SSAS view. Ideally they should already be normalised views held in your datamart in SQL. The less SSAS has to figure out the better, leaving it just to the measures for roll ups. Also it's a lot easier to support going forward as you can change a SQL view on the fly, rather than needing to redeploy your entire cube.

已翻译

赞

4 How to manage a DSV

To manage a DSV in SSAS, you can use the Data Source View Designer, which provides various tools and features such as the Diagram Organizer, Diagram View, Grid View and Query Builder. The Diagram Organizer enables organizing tables and views into logical groups and folders. The Diagram View shows the graphical representation of your tables and views and allows you to edit their properties, relationships and filters. The Grid View offers tabular representation of your tables and views and allows you to edit their columns, data types and expressions. Finally, the Query Builder allows creating and modifying named queries and named calculations using a graphical interface or SQL code.

添加您的观点

5 Best practices for multiple data sources

To make the most of using multiple data sources in a DSV, you should use descriptive names and descriptions for your data sources, tables, views, queries, and calculations. Additionally, aliases or synonyms should be used for tables and views to prevent name conflicts or confusion. Furthermore, views or queries should be employed to filter or aggregate data sources, and calculations should be used to create derived or computed columns. Finally, relationships should be established to define the joins and cardinalities between tables and views, while avoiding circular references or orphaned records.

添加您的观点

Alex Bishop

Senior Technical Consultant at Domo
举报内容
Use of Sun modelling can really help define the levels of granularity you're working with. Getting as many edge cases from the business up front can also make your modelling easier down the line.

已翻译

赞

Analysis Services

+ 关注

给文章评分

我们借助人工智能创建了此文章。您认为这篇文章怎么样？

很棒不太好

举报此文章

查看全部

What are the benefits and challenges of using multiple data sources in a data source view?

1

2

3

4

5

1 Benefits of multiple data sources

2 Challenges of multiple data sources

3 How to create a DSV

4 How to manage a DSV

5 Best practices for multiple data sources

Analysis Services

给文章评分

感谢您的反馈

更多Analysis Services相关文章

更多相关阅读内容