紧急帮助-AAS，如何在日期和时间维度之间建立关系? [英] urgent help -- AAS, how to create relationship between date and time dimension?

问题描述

我们的导入源是将erp数据下载到sqldb中，其中所有实体交易日期列附带日期&出发时间ddmmyyyyhh:mm:ss.

Our import source is erp data downloading into sqldb, where all entities  transaction date columns comes with date & time fromat  ddmmyyyyhh:mm:ss.

在AA中，我创建了Dimdate表，其中填充日期的格式为"ddmmyyyy"，并且可以通过转换与所有目标表建立关系.缩短日期，但企业希望报表在小时和分钟之内展开，而不是秒.如果 我创建组合日期&时间维度放入单个表中，以保持5年的日期时间，这将使成千上万的数据量爆炸，并会影响性能问题.

In AAs, i have created the Dimdate table with populate dates in the format 'ddmmyyyy' and able to create the relationshipwith all targettables by converting  to shortdate but business wants the reports to drill down in hours and minute not second. if i create combination date & time dimension into single table for 5 years worth of datetime, it will explode the volume which will be in millions and will hit performance issues.

在一些Google链接中，建议创建一个单独的表而不是一个表，例如dimdate(仅日期)和dimtime(仅24小时时间信息)，我的问题是如何创建dimdate与dimtime表之间的关系以及如何链接业务表谁 日期列的格式是ie. ddmmyyyyhh:mm:ss?

In some of google links , suggested to create seperate table instead of one i.e dimdate(only date) and dimtime (only 24hrs time information), my question how to create the relationship between dimdate vs dimtime table and how to link the business table who's date column format is ie. ddmmyyyyhh:mm:ss?

请告知如何实现?

推荐答案

事实/交易表需要具有2个独立的列和2个独立的关系:要链接的1个列/关系指向日期表，并有1列/关系链接到时间表.

You fact/transaction table will need to have 2 separate columns and 2 separate relationships: 1 column/relationship to link to the date table and 1 column/relationship to link to the time table.

DimDate和DimTime表应该不相关.

The DimDate and DimTime tables should not be related.

仅转到时间表中要求的最低粒度.例如，如果只需要分析到分钟，则DimTime表将具有1440行(24小时* 60分钟/小时)；如果确实需要转到第二个位置，则DimTime表将相当可观 更大的数据(86.4k行= 24小时/天* 60分钟/小时* 60秒/分钟)，并且(取决于您的计算)最终将消耗更多的CPU解析查询.

Only go to the lowest required granularity in your time table. For example, if you only need to analyze down to the minute, then you DimTime table will have 1440 rows (24hrs * 60 minutes/hr); If you do need to go to the second, your DimTime table will considerably larger (86.4k rows = 24hrs / day * 60 min/hr * 60sec/min) and (depending on your calculations) you'll end up burning a bit more CPU resolving queries.

有关此建模模式的更多指导，请参见Kimball数据仓库.

See Kimball Data Warehouse for further guidance on this modeling pattern.

这篇关于紧急帮助-AAS，如何在日期和时间维度之间建立关系?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！