I have about 150 000 rows of data written to a database everyday. These row represent outgoing articles for example. Now I need to show a graph using SSRS that show the average number of articles per day over time. I also need to have a information about the actual number of articles from yesterday.
我每天有大约150 000行数据写入数据库。例如,这些行代表传出文章。现在,我需要使用SSRS显示一个图表,该图表显示了每天的平均文章数量。我还需要提供有关昨天的实际文章数量的信息。
The idea is to have a aggregated view on all our transactions and have something that can indicate that something is wrong (that we for example send out 20% less articles than the average).
我们的想法是对所有交易都有一个聚合视图,并且有一些东西可以表明出现了问题(例如我们发送的文章比平均值少20%)。
My idea is to have yesterdays data moved into SSAS every night and there store the aggregated value of number of transactions and the actual number of transaction from yesterdays data. Using SSAS would hopefully speed up the reports.
我的想法是每天晚上将昨天的数据移入SSAS,并存储交易数量的汇总值和昨天数据的实际交易数量。使用SSAS有望加快报告速度。
Do you think this is the right idea? Should I skip SSAS and have reports straight on the raw data? I know how use reporting services on raw data using standard SQL queries but how would this change when querying SSAS? I don't know SSAS - where do I start ..?
你认为这是正确的想法吗?我应该跳过SSAS并直接报告原始数据吗?我知道如何使用标准SQL查询在原始数据上使用报告服务,但在查询SSAS时这会如何变化?我不知道SSAS - 我从哪里开始..?
5 个解决方案
#1
2
The neat thing with SSAS is that you can get those indicators that you talk about quite easily either by creating calculated measures or by using KPIs.
SSAS的优点在于,您可以通过创建计算度量或使用KPI轻松获得您所谈论的那些指标。
I started with Delivering Business Intelligence with Microsoft SQL Server 2005. It had some good introduction, but unfortunately it's too verbose when it comes to the details. But if you want to understand SSAS, OLAP and reporting using this framework it's a good start.
我开始使用Microsoft SQL Server 2005提供商业智能。它有一些很好的介绍,但遗憾的是,它在详细信息方面过于冗长。但是,如果您想了解SSAS,OLAP以及使用此框架进行报告,那么这是一个良好的开端。
Mosha Pasumansky has a blog on SSAS and MDX with great links.
Mosha Pasumansky有一个关于SSAS和MDX的博客,链接很棒。
Other than that I would recommend Microsofts Online books.
除此之外,我会推荐微软在线书籍。
#2
1
Are you sure you aren't mixing up SSAS (Analysis Services) and SSIS (integration services)?
您确定没有混淆SSAS(Analysis Services)和SSIS(集成服务)吗?
SSAS is not an ETL, it is an OLAP tool.
SSAS不是ETL,它是OLAP工具。
SSIS is an ETL tool.
SSIS是一个ETL工具。
I agree with everything that Rowan said. I'm just confused by the terms.
我同意罗文所说的一切。我只是对条款感到困惑。
#3
0
SSAS is an ETL tool. Basically you get data from somewhere (your outgoing articles), do something to it (aggregate), and put it somewhere else (your aggregates table, data warehouse, etc). Check the link for details.
SSAS是一种ETL工具。基本上你从某个地方(你的传出文章)获取数据,对它做一些事情(聚合),并把它放在其他地方(你的聚合表,数据仓库等)。查看链接以获取详细信息。
You probably won't be keeping all of the rows in the DB indefinitely and if you want to be able to report on longer trends you need in any case do some kind of aggregating of historical data. So making the reports use this historical data store as their source makes sense. You can then use it to do all kinds of fancy reporting.
您可能无法无限期地保留数据库中的所有行,如果您希望能够报告更长的趋势,则无论如何都要进行某种历史数据的聚合。因此,使报告使用此历史数据存储作为其来源是有意义的。然后,您可以使用它来执行各种奇特的报告。
TL;DR: Define your aggregated history table with your future reporting needs in mind. Use the SSAS to populate the table and refresh it from the daily updates. Report from that table. Further reading: Star Schemas and data warehousing.
TL; DR:定义您的聚合历史记录表,记住您未来的报告需求。使用SSAS填充表并从每日更新中刷新它。该表的报告。进一步阅读:星型模式和数据仓库。
#4
0
@Sergio and @Rowan
@Sergio和@Rowan
Yes, we're not talking about loading and transforming data into the database (like a SSIS tool would do). That's solved using our integration platform.
是的,我们不是在谈论将数据加载和转换到数据库中(就像SSIS工具一样)。这是使用我们的集成平台解决的。
#5
0
@Riri maybe SSAS is overkill for the situation you presented. If you only need to daily populate sumarization tables, you can accomplish it by creating a regular JOB in SQL Server and doing it in a regular T-SQL script.
@Riri也许SSAS对你提出的情况有点过分。如果您只需要每天填充sumarization表,您可以通过在SQL Server中创建常规JOB并在常规T-SQL脚本中执行它来完成它。
I've used this approach for several years in a daily process to calculate business indicators from about 9GB new data / day. It works, it's fast, it's simple and it uses a technology you're already used to. If your daily process get's more complicated (it needs to read from files, use FTP, send emails) you can move to a SSIS package (or any other ETL tool you like), but I cannot recommend using SSAS unless you need to provide OLAP capabilities to your users.
我已经在日常流程中使用这种方法多年来从大约9GB的新数据/天计算业务指标。它很有效,它很快,很简单,它使用了你已经习惯的技术。如果您的日常过程变得更复杂(需要从文件读取,使用FTP,发送电子邮件),您可以移动到SSIS包(或您喜欢的任何其他ETL工具),但除非您需要提供OLAP,否则我不建议使用SSAS为您的用户提供的功能
#1
2
The neat thing with SSAS is that you can get those indicators that you talk about quite easily either by creating calculated measures or by using KPIs.
SSAS的优点在于,您可以通过创建计算度量或使用KPI轻松获得您所谈论的那些指标。
I started with Delivering Business Intelligence with Microsoft SQL Server 2005. It had some good introduction, but unfortunately it's too verbose when it comes to the details. But if you want to understand SSAS, OLAP and reporting using this framework it's a good start.
我开始使用Microsoft SQL Server 2005提供商业智能。它有一些很好的介绍,但遗憾的是,它在详细信息方面过于冗长。但是,如果您想了解SSAS,OLAP以及使用此框架进行报告,那么这是一个良好的开端。
Mosha Pasumansky has a blog on SSAS and MDX with great links.
Mosha Pasumansky有一个关于SSAS和MDX的博客,链接很棒。
Other than that I would recommend Microsofts Online books.
除此之外,我会推荐微软在线书籍。
#2
1
Are you sure you aren't mixing up SSAS (Analysis Services) and SSIS (integration services)?
您确定没有混淆SSAS(Analysis Services)和SSIS(集成服务)吗?
SSAS is not an ETL, it is an OLAP tool.
SSAS不是ETL,它是OLAP工具。
SSIS is an ETL tool.
SSIS是一个ETL工具。
I agree with everything that Rowan said. I'm just confused by the terms.
我同意罗文所说的一切。我只是对条款感到困惑。
#3
0
SSAS is an ETL tool. Basically you get data from somewhere (your outgoing articles), do something to it (aggregate), and put it somewhere else (your aggregates table, data warehouse, etc). Check the link for details.
SSAS是一种ETL工具。基本上你从某个地方(你的传出文章)获取数据,对它做一些事情(聚合),并把它放在其他地方(你的聚合表,数据仓库等)。查看链接以获取详细信息。
You probably won't be keeping all of the rows in the DB indefinitely and if you want to be able to report on longer trends you need in any case do some kind of aggregating of historical data. So making the reports use this historical data store as their source makes sense. You can then use it to do all kinds of fancy reporting.
您可能无法无限期地保留数据库中的所有行,如果您希望能够报告更长的趋势,则无论如何都要进行某种历史数据的聚合。因此,使报告使用此历史数据存储作为其来源是有意义的。然后,您可以使用它来执行各种奇特的报告。
TL;DR: Define your aggregated history table with your future reporting needs in mind. Use the SSAS to populate the table and refresh it from the daily updates. Report from that table. Further reading: Star Schemas and data warehousing.
TL; DR:定义您的聚合历史记录表,记住您未来的报告需求。使用SSAS填充表并从每日更新中刷新它。该表的报告。进一步阅读:星型模式和数据仓库。
#4
0
@Sergio and @Rowan
@Sergio和@Rowan
Yes, we're not talking about loading and transforming data into the database (like a SSIS tool would do). That's solved using our integration platform.
是的,我们不是在谈论将数据加载和转换到数据库中(就像SSIS工具一样)。这是使用我们的集成平台解决的。
#5
0
@Riri maybe SSAS is overkill for the situation you presented. If you only need to daily populate sumarization tables, you can accomplish it by creating a regular JOB in SQL Server and doing it in a regular T-SQL script.
@Riri也许SSAS对你提出的情况有点过分。如果您只需要每天填充sumarization表,您可以通过在SQL Server中创建常规JOB并在常规T-SQL脚本中执行它来完成它。
I've used this approach for several years in a daily process to calculate business indicators from about 9GB new data / day. It works, it's fast, it's simple and it uses a technology you're already used to. If your daily process get's more complicated (it needs to read from files, use FTP, send emails) you can move to a SSIS package (or any other ETL tool you like), but I cannot recommend using SSAS unless you need to provide OLAP capabilities to your users.
我已经在日常流程中使用这种方法多年来从大约9GB的新数据/天计算业务指标。它很有效,它很快,很简单,它使用了你已经习惯的技术。如果您的日常过程变得更复杂(需要从文件读取,使用FTP,发送电子邮件),您可以移动到SSIS包(或您喜欢的任何其他ETL工具),但除非您需要提供OLAP,否则我不建议使用SSAS为您的用户提供的功能