产品和评审系统的维度建模

Question

我遇到了以下场景，其中我需要将OLTP数据库系统转换为维度建模或DWH系统。

我从源代码收到了两个JSON格式的文件。它确实包含产品和审阅者的详细信息。产品: ID，名称，价格，brand产品，相关产品，销售排名，品牌，类别

Product data sample :
{
  "ID": "0000031852",
  "name": "Girls Ballet Tutu Zebra Hot Pink",
  "price": 3.17,
  "urlofproduct ": "http://ecx.images-amazon.com/images/I/51fAmVkTbyL._SY300_.jpg",
  "relatedproducts ":
  {
    "also_bought": ["B00JHONN1S", "B002BZX8Z6", "B00D2K1M3O", "0000031909"],
    "also_viewed": ["B002BZX8Z6", "B00JHONN1S", "B008F0SU0Y", "B00D23MC6W", "B00AFDOPDA"],
    "bought_together": ["B002BZX8Z6"]
  },
  "salesRank": {"Toys & Games": 211836},
  "brand": "Coxlures",
  "categories": [["Sports & Outdoors", "Other Sports", "Dance"]]
}

审阅者: ID，ProductID，名称，帮助，审阅文本，整体，摘要，统一审阅时间，审阅时间

审阅者示例数据：

{
  "ID": "A2SUAM1J3GNN3B",
  "ProductID": "0000013714",
  "Name": "J. McDonald",
  "helpful": [2, 3],
  "reviewText": "I bought this for my husband who plays the piano.  He is having a wonderful time playing these old hymns.  The music  is at times hard to read because we think the book was published for singing from more than playing from.  Great purchase though!",
  "overall": 5.0,
  "summary": "Heavenly Highway Hymns",
  "unixReviewTime": 1252800000,
  "reviewTime": "09 13, 2009"
}

我正在努力如何在维度上对其进行建模。我可以看到product和reviewer是两个维度。但是我应该如何在我的数据仓库中存储购买、查看或bought_together的相关产品呢？

有谁能帮上忙吗？只需给出一些如何进行的想法。

Answer 1

如果您使用的是MS SQL Server，则可以尝试使用OPENJSON (SQL Server)函数。此函数允许解析和转换JSON数据。