复杂的立方体设计
分析团队想要分析销售渠道数据。当产品经历不同阶段时，我们的 CRM 系统（源系统）发送数据仓库团队交易。
仅举几例：Creation、CallMade、LeadEngged、CreditCheck、PapersReceived、Closed。上面提到的跟踪状态有 100 多种。
事务事实表示例：
ID LeadID TransDateTime Status TransPersonID
1 111 5/8/2008 Creation 123
2 111 5/9/2008 CallMade 123
3 112 5/9/2008 Creation 124
4 111 5/10/2008 LeadEngged 123
5 112 5/ 10/2008 LeadEngged 124
6 111 5/11/2008 CreditCheck 123
7 111 5/12/2008 PapersReceives 123
8 111 5/12/2008 Closed 123
问题：团队想要分析：
•从一种状态转变为需要多长时间另一种。（状态之间） •有多少潜在客户从一种状态转移到另一种状态（状态之间）。 •线索可以在雕像之间来回移动。 •例如：那么如何知道在任何给定时间点有多少潜在客户在Creation 和LeadEngged 之间移动。 任何帮助表示赞赏。
The Analysis team wants to analyze sales pipeline data. Our CRM system(Source system) send the Data Warehouse team transaction as the Product moves through the different stages.
To mention a few: Creation, CallMade,LeadEngaged,CreditCheck,PapersReceived,Closed.There are more than 100 tracking statuses like mentioned above.
Sample transactions fact table:
ID LeadID TransDateTime Status TransPersonID
1 111 5/8/2008 Creation 123
2 111 5/9/2008 CallMade 123
3 112 5/9/2008 Creation 124
4 111 5/10/2008 LeadEngaged 123
5 112 5/10/2008 LeadEngaged 124
6 111 5/11/2008 CreditCheck 123
7 111 5/12/2008 PapersReceived 123
8 111 5/12/2008 Closed 123
Problem: Team wants to analyze:
•How long it took to move from One Status To Another.(Between Statuses)
•How many leads moved from one status to another(Between Statuses).
•Leads can move back and forth between statues.
•For Eg: Then what to know at any given point of time how many leads moved between Creation and LeadEngaged.
Any help is appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
使用 SSAS 可以相对轻松地完成此操作。使用 TransDate 设置时间维度。设置销售计数度量，以便您可以轻松查看交易数量。
不确定线索的变化。如果有一个纸质记录类型的表格显示它们曾经是什么和现在是什么，或者某种类型的状态变化表，您可以用它来创建一个维度。
This can be done relatively easily with SSAS. Set up a time dimension with TransDate. Set up a salescount measure so that you can easily see number of transactions.
Unsure about the changing leads. If there is a paper trail type of table that shows what they used to be and what they are now or some type of status change table, you can make a dimension with that.