FoodMart数据仓库mysql表及数据初始化及重度汇总脚本

已于 2023-12-05 21:19:46 修改
阅读量1.9k 收藏 4
点赞数 2
分类专栏： 数据库与大数据 文章标签： mysql 数据库 database
于 2022-02-17 12:24:35 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/shenliang1985/article/details/122980468
版权

FoodMart数据仓库学习必看

FoodMart简介

FoodMart是一个小型的数据仓库的示例,它基于食品超市的场景。

Mondrian 是一个JAVA写的OLAP引擎.，它从JDBC里读取聚合的数据并缓存在内存里，同时支持MDX查询和提供olap4j、 XML/A 相关API。

注：其它数据库如SQL Server、posgres、Oracle类似（即修改JDBC的URL并配置相应的JDBC驱动）。

下载mondrian-3.3.0

1 在Compare, Download & Develop Open Source & Business Software - SourceForgeicon-default.png?t=N7T8https://sourceforge.net/ 主页内搜索 Mondrian 或者直接访问

Mondrian download | SourceForge.net

2 点击File 选项后点击mondrian链接进入。

3 找到mondrian-3.3.0.14703后点击下载。

注：也可以选择3.2，当前使用的是3.3。

找到WAR包并解压 

在下载的mondrian-3.3.0.14703.zip里找到mondrian.war。

找到需要的JAR包拷贝出来

用解压工具提取处WAR包里的7个jar包并拷贝到指定目录，当前是D:\FoodMart

olap4j.jar

mondrian.jar

log4j-1.2.8.jar

commons-logging-1.0.4.jar

eigenbase-xom.jar

eigenbase-resgen.jar

eigenbase-properties.jar

下载mysql驱动

当前下载的是mysql-connector-java-5.1.19.jar，并放在D:\FoodMart目录内。

拷贝数据文件对应SQL

将之前下载的mondrian-3.3.0.14703.zip里的FoodMartCreateData.sql拷贝到D:\FoodMart目录内。

Mysql里新建库

CREATE DATABASE IF NOT EXISTS foodmartDEFAULT CHARSET utf8;

Windows命令里执行数据加载程序

打开windows命令行，执行如下程序

java -cp D:\FoodMart\mondrian.jar;D:\FoodMart\log4j-1.2.8.jar;D:\FoodMart\commons-logging-1.0.4.jar;D:\FoodMart\eigenbase-xom.jar;D:\FoodMart\eigenbase-resgen.jar;D:\FoodMart\eigenbase-properties.jar;D:\FoodMart\mysql-connector-java-5.1.20-bin.jar;D:\FoodMart\olap4j.jar;D:\FoodMart\mysql-connector-java-5.1.19.jar mondrian.test.loader.MondrianFoodMartLoader -verbose -tables -data -indexes -jdbcDrivers="com.mysql.jdbc.Driver" -inputFile=D:\FoodMart\FoodMartCreateData.sql -outputJdbcURL="jdbc:mysql://localhost:3309/foodmart?user=root&password=root1234

注：数据库名、用户名、密码、端口按需修改。  

数据库里查看

-- 查看sales_fact_1998数据
SELECT * FROM sales_fact_1998 limit 10

 -- 查看表数量及数据大小
SELECT COUNT(TABLE_NAME) tabcnt,
(sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024.0/1024 dbsize_M
from information_schema.tables
where table_schema='foodmart';

 

重度汇总脚本

# Create aggregate tables for Mondrian

##################################################################
## agg_pl_01_sales_fact_1997 done
##################################################################
# physical
# lost "promotion_id" "store_id"

/*
7 fact tables: sales_fact_1997, sales_fact_1998, sales_fact_dec_1998, inventory_fact_1997, inventory_fact_1998, salary, expense_fact
19 dimension tables: product, customer, time_by_day, employee and more
11 aggregate tables
*/

INSERT INTO "agg_pl_01_sales_fact_1997" (
    "product_id",
    "time_id",
    "customer_id",
    "store_sales_sum",
    "store_cost_sum",
    "unit_sales_sum",
    "fact_count"
) SELECT
    "product_id" AS "product_id",
    "time_id" AS "time_id",
    "customer_id" AS "customer_id",
    SUM("store_sales") AS "store_sales",
    SUM("store_cost") AS "store_cost",
    SUM("unit_sales") AS "unit_sales",
    COUNT(*) AS fact_count
FROM "sales_fact_1997"
GROUP BY "product_id", "time_id", "customer_id";


INSERT INTO "agg_ll_01_sales_fact_1997" (
    "product_id",
    "time_id",
    "customer_id",
    "store_sales",
    "store_cost",
    "unit_sales",
    "fact_count"
) SELECT
    "product_id" AS "product_id",
    "time_id" AS "time_id",
    "customer_id" AS "customer_id",
    SUM("store_sales") AS "store_sales",
    SUM("store_cost") AS "store_cost",
    SUM("unit_sales") AS "unit_sales",
    COUNT(*) AS "fact_count"
FROM "sales_fact_1997"
GROUP BY "product_id", "time_id", "customer_id";

##################################################################
## agg_l_03_sales_fact_1997 done
##################################################################
# logical
# lost "product_id" "promotion_id" "store_id"


INSERT INTO "agg_l_03_sales_fact_1997" (
    "customer_id",
    "time_id",
    "store_sales",
    "store_cost",
    "unit_sales",
    "fact_count"
) SELECT
    "customer_id",
    "time_id",
    SUM("store_sales") AS "store_sales",
    SUM("store_cost") AS "store_cost",
    SUM("unit_sales") AS "unit_sales",
    COUNT(*) AS "fact_count"
FROM "sales_fact_1997"
GROUP BY "customer_id", "time_id";

##################################################################
## agg_lc_06_sales_fact_1997 done
##################################################################
# collapse "customer_id"
# lost "product_id" "promotion_id" "store_id"


INSERT INTO "agg_lc_06_sales_fact_1997" (
    "time_id",
    "city",
    "state_province",
    "country",
    "store_sales",
    "store_cost",
    "unit_sales",
    "fact_count"
) SELECT
    "B"."time_id",
    "D"."city",
    "D"."state_province",
    "D"."country",
    SUM("B"."store_sales") AS "store_sales",
    SUM("B"."store_cost") AS "store_cost",
    SUM("B"."unit_sales") AS "unit_sales",
    COUNT(*) AS fact_count
FROM "sales_fact_1997" "B", "customer" "D"
WHERE
    "B"."customer_id" = "D"."customer_id"
GROUP BY
         "B"."time_id",
         "D"."city",
         "D"."state_province",
         "D"."country";

##################################################################
## agg_l_04_sales_fact_1997 done
##################################################################
# logical
# lost "customer_id" "product_id" "promotion_id" "store_id"

INSERT INTO "agg_l_04_sales_fact_1997" (
    "time_id",
    "store_sales",
    "store_cost",
    "unit_sales",
    "customer_count",
    "fact_count"
) SELECT
    "time_id",
    SUM("store_sales") AS "store_sales",
    SUM("store_cost") AS "store_cost",
    SUM("unit_sales") AS "unit_sales",
    COUNT(DISTINCT "customer_id") AS "customer_count",
    COUNT(*) AS "fact_count"
FROM "sales_fact_1997"
GROUP BY "time_id";

##################################################################
## agg_c_10_sales_fact_1997 done
##################################################################
# collapse "time_id"
# lost "customer_id" "product_id" "promotion_id" "store_id"


INSERT INTO "agg_c_10_sales_fact_1997" (
    "month_of_year",
    "quarter",
    "the_year",
    "store_sales",
    "store_cost",
    "unit_sales",
    "customer_count",
    "fact_count"
) SELECT
    "D"."month_of_year",
    "D"."quarter",
    "D"."the_year",
    SUM("B"."store_sales") AS "store_sales",
    SUM("B"."store_cost") AS "store_cost",
    SUM("B"."unit_sales") AS "unit_sales",
    COUNT(DISTINCT "customer_id") AS "customer_count",
    COUNT(*) AS fact_count
FROM "sales_fact_1997" "B", "time_by_day" "D"
WHERE
    "B"."time_id" = "D"."time_id"
GROUP BY
         "D"."month_of_year",
         "D"."quarter",
         "D"."the_year";

##################################################################
## agg_l_05_sales_fact_1997 done
##################################################################
# logical
# lost "time_id"


INSERT INTO "agg_l_05_sales_fact_1997" (
    "product_id",
    "customer_id",
    "promotion_id",
    "store_id",
    "store_sales",
    "store_cost",
    "unit_sales",
    "fact_count"
) SELECT
    "product_id",
    "customer_id",
    "promotion_id",
    "store_id",
    SUM("store_sales") AS "store_sales",
    SUM("store_cost") AS "store_cost",
    SUM("unit_sales") AS "unit_sales",
    COUNT(*) AS fact_count
FROM "sales_fact_1997"
GROUP BY "product_id", "customer_id", "promotion_id", "store_id";



##################################################################
## agg_c_14_sales_fact_1997 done
##################################################################
# collapse "time_id"


INSERT INTO "agg_c_14_sales_fact_1997" (
    "product_id",
    "customer_id",
    "promotion_id",
    "store_id",
    "month_of_year",
    "quarter",
    "the_year",
    "store_sales",
    "store_cost",
    "unit_sales",
    "fact_count"
) SELECT
    "B"."product_id",
    "B"."customer_id",
    "B"."promotion_id",
    "B"."store_id",
    "D"."month_of_year",
    "D"."quarter",
    "D"."the_year",
    SUM("B"."store_sales") AS "store_sales",
    SUM("B"."store_cost") AS "store_cost",
    SUM("B"."unit_sales") AS "unit_sales",
    COUNT(*) AS fact_count
FROM "sales_fact_1997" "B", "time_by_day" "D"
WHERE
    "B"."time_id" = "D"."time_id"
GROUP BY "B"."product_id",
         "B"."customer_id",
         "B"."promotion_id",
         "B"."store_id",
         "D"."month_of_year",
         "D"."quarter",
         "D"."the_year";


##################################################################
## agg_lc_100_sales_fact_1997 done
##################################################################
# drop "promotion_id"
# drop "store_id"
# collapse "time_id"


INSERT INTO "agg_lc_100_sales_fact_1997" (
    "product_id",
    "customer_id",
    "quarter",
    "the_year",
    "store_sales",
    "store_cost",
    "unit_sales",
    "fact_count"
) SELECT
    "B"."product_id",
    "B"."customer_id",
    "D"."quarter",
    "D"."the_year",
    SUM("B"."store_sales") AS "store_sales",
    SUM("B"."store_cost") AS "store_cost",
    SUM("B"."unit_sales") AS "unit_sales",
    COUNT(*) AS fact_count
FROM "sales_fact_1997" "B", "time_by_day" "D"
WHERE
    "B"."time_id" = "D"."time_id"
GROUP BY "B"."product_id",
         "B"."customer_id",
         "D"."quarter",
         "D"."the_year";


##################################################################
##################################################################
## SPECIAL
##################################################################
##################################################################
## agg_c_special_sales_fact_1997 done
## based upon agg_c_14_sales_fact_1997
##################################################################
# collapse "time_id"


INSERT INTO "agg_c_special_sales_fact_1997" (
    "product_id",
    "customer_id",
    "promotion_id",
    "store_id",
    "time_month",
    "time_quarter",
    "time_year",
    "store_sales_sum",
    "store_cost_sum",
    "unit_sales_sum",
    "fact_count"
) SELECT
    "B"."product_id",
    "B"."customer_id",
    "B"."promotion_id",
    "B"."store_id",
    "D"."month_of_year",
    "D"."quarter",
    "D"."the_year",
    SUM("B"."store_sales") AS "store_sales_sum",
    SUM("B"."store_cost") AS "store_cost_sum",
    SUM("B"."unit_sales") AS "unit_sales_sum",
    COUNT(*) AS "fact_count"
FROM "sales_fact_1997" "B", "time_by_day" "D"
WHERE
    "B"."time_id" = "D"."time_id"
GROUP BY "B"."product_id",
         "B"."customer_id",
         "B"."promotion_id",
         "B"."store_id",
         "D"."month_of_year",
         "D"."quarter",
         "D"."the_year";

##################################################################
# agg_gender_ms_state_sales_fact_1997
##################################################################

INSERT INTO "agg_g_ms_pcat_sales_fact_1997" (
    "gender",
    "marital_status",
    "product_family",
    "product_department",
    "product_category",
    "month_of_year",
    "quarter",
    "the_year",
    "store_sales",
    "store_cost",
    "unit_sales",
    "customer_count",
    "fact_count"
) SELECT
    "C"."gender",
    "C"."marital_status",
    "PC"."product_family",
    "PC"."product_department",
    "PC"."product_category",
    "T"."month_of_year",
    "T"."quarter",
    "T"."the_year",
    SUM("B"."store_sales") AS "store_sales",
    SUM("B"."store_cost") AS "store_cost",
    SUM("B"."unit_sales") AS "unit_sales",
    COUNT(DISTINCT "C"."customer_id") AS "customer_count",
    COUNT(*) AS "fact_count"
FROM "sales_fact_1997" "B",
    "time_by_day" "T",
    "product" "P",
    "product_class" "PC",
    "customer" "C"
WHERE
    "B"."time_id" = "T"."time_id"
AND "B"."customer_id" = "C"."customer_id"
AND "B"."product_id" = "P"."product_id"
AND "P"."product_class_id" = "PC"."product_class_id"
GROUP BY
    "C"."gender",
    "C"."marital_status",
    "PC"."product_family",
    "PC"."product_department",
    "PC"."product_category",
    "T"."month_of_year",
    "T"."quarter",
    "T"."the_year";

/*
# Above query, rephrased for Access (which does not support
# COUNT(DISTINCT ...) explicitly.
#
#INSERT INTO "agg_g_ms_pcat_sales_fact_1997" (
#    "gender",
#    "marital_status",
#    "product_family",
#    "product_department",
#    "product_category",
#    "month_of_year",
#    "quarter",
#    "the_year",
#    "store_sales",
#    "store_cost",
#    "unit_sales",
#    "customer_count",
#    "fact_count"
#) SELECT
#    "C"."gender",
#    "C"."marital_status",
#    "PC"."product_family",
#    "PC"."product_department",
#    "PC"."product_category",
#    "T"."month_of_year",
#    "T"."quarter",
#    "T"."the_year",
#    SUM("B"."store_sales") AS "store_sales",
#    SUM("B"."store_cost") AS "store_cost",
#    SUM("B"."unit_sales") AS "unit_sales",
#    (
#    SELECT COUNT("customer_id")
#    FROM (
#        SELECT DISTINCT
#            "DC"."gender",
#            "DC"."marital_status",
#            "DPC"."product_family",
#            "DPC"."product_department",
#            "DPC"."product_category",
#            "DT"."month_of_year",
#            "DT"."quarter",
#            "DT"."the_year",
#            "DB"."customer_id"
#        FROM
#            "sales_fact_1997" "DB",
#            "time_by_day" "DT",
#            "product" "DP",
#            "product_class" "DPC",
#            "customer" "DC"
#        WHERE
#            "DB"."time_id" = "DT"."time_id"
#        AND "DB"."customer_id" = "DC"."customer_id"
#        AND "DB"."product_id" = "DP"."product_id"
#        AND "DP"."product_class_id" = "DPC"."product_class_id") AS "CDC"
#    WHERE "CDC"."gender" = "C"."gender"
#    AND "CDC"."marital_status" = "C"."marital_status"
#    AND "CDC"."product_family" = "PC"."product_family"
#    AND "CDC"."product_department" = "PC"."product_department"
#    AND "CDC"."product_category" = "PC"."product_category"
#    AND "CDC"."month_of_year" = "T"."month_of_year"
#    AND "CDC"."quarter" = "T"."quarter"
#    AND "CDC"."the_year" = "T"."the_year"
#    GROUP BY
#        "gender",
#        "marital_status",
#        "product_family",
#        "product_department",
#        "product_category",
#        "month_of_year",
#        "quarter",
#        "the_year") AS "customer_count",
#    COUNT(*) AS "fact_count"
#FROM "sales_fact_1997" "B",
#    "time_by_day" "T",
#    "product" "P",
#    "product_class" "PC",
#    "customer" "C"
#WHERE
#    "B"."time_id" = "T"."time_id"
#AND "B"."customer_id" = "C"."customer_id"
#AND "B"."product_id" = "P"."product_id"
#AND "P"."product_class_id" = "PC"."product_class_id"
#GROUP BY
#    "C"."gender",
#    "C"."marital_status",
#    "PC"."product_family",
#    "PC"."product_department",
#    "PC"."product_category",
#    "T"."month_of_year",
#    "T"."quarter",
#    "T"."the_year";

# End insert.sql
*/

彩蛋 

可访问已经整理好的数据库建表及数据脚本。

链接：https://pan.baidu.com/s/1Yddyy8XKkWaVF7qKwi2y_Q 
提取码：nbmu

ShenLiang2025
关注
  • 2
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论
专栏目录
foodmart数据库实例
03-22
OLAP 多维数据分析 数据库实例 SQLServer 2008 版本mdf 文件
聚类分析算法
chenglansky的专栏
06-09 974
聚类算法应用广泛：模式识别，数据分析，图像识别及其他许多方面。
eigenbase-xom.jar
03-12
jar包，官方版本，自测可用
foodmart-queries:Mondrian 针对 FoodMart 数据集发出的 SQL 查询，采用 JSON 格式
06-20
foodmart-查询 Mondrian 针对 FoodMart 数据集发出的 SQL 查询，采用 JSON 格式 它是由的测试套件生成的，但对于任何 SQL 引擎来说都是一个具有挑战性的基准。 更多信息 许可证：Apache 许可证，版本 2.0 作者：朱利安·海德 博客： : 项目页面： : 源代码： : 开发人员列表： （、） 问题： :
构建基于FoodMart数据库的数据仓库建立与OLAP(实验一).pptx
10-24
构建基于FoodMart数据库的数据仓库建立与OLAP(实验一).pptx
R语言关联规则及其可视化（Foodmart数据）
brilliant_o的博客
12-05 6198
R语言关联规则-Foodmart数据数据集描述初步探索关联规则挖掘关联规则可视化商品品类的关联规则如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants创建一个自定义列表如何创建一个注脚注释也是必不可少的KaTeX数学公式 数据集描述 上周数据挖掘学习了关联规则，今天来实操一下。数据集是Foodmart1997年和19...
foodmart数据库实例_3)使用foodmart数据集的销售样例进行商品关联规则分析(需要写清楚数据清理过程)。,food mart数据集
05-26
OLAP 多维数据分析 数据库实例 SQLServer 2008 版本mdf 文件
FoodMart数据和实验分析及报告.rar
02-12
利用FoodMart数据进行数据实验，对数据进行分析，了解具体数据之间的关系，使用该数据进行实验分析，通过实验分析完成相关报告，本部分操作的前提条件是安装好实验的具体环境。
购物篮数据集
04-21
关联规则算法应用的购物篮数据集。
Hive作为Mondrian的数据源
Hello World
05-04 5424
使用hive作为mondrian数据源，实现基于hadoop的数据分析
Foodmart1997年和1998年的购物篮数据
12-11
从Foodmart的数据集中抽取其1997年和1998年的购物篮数据，其中sale1997.txt为1997年购物篮数据，sale1998.txt为1998年购物篮数据，productList.txt为产品列表
neo4j-foodmart-dataset:Neo4j Food Mart数据集
05-11
Neo4j FoodMart数据集 基于经典的Food Mart SQL数据集的Neo4j使用的数据集。 当前它包括产品，客户和销售数据。 要加载数据集，请使用随附的Cypher脚本。 例如： ./bin/neo4j-shell < foodmart-import.cyp
foodmart数据为mysql
05-04
mondria中的例子foodmart数据库结构及数据
foodmart数据for mysql
11-15
mondrian中foodmart样例数据的mysql脚本，其中包含了结构和数据
数据仓库Foodmart2005 实例数据库
05-13
数据仓库Foodmart2005 实例数据库
mondrian foodmart SQL SERVER 数据库
08-14
mondrian foodmart SQL SERVER 数据库
foodmart 2000.mdb
02-03
sql server 2000 经典数据库实例。 可导入sql server 2005。
foodmart 2000
10-23
foodmart数据库用于数据挖掘联系的数据库，可以导入到SQL server 也可以导入到Oracle中，是练习数据挖掘的最大数据资源！
Foodmart批发店销售分析系统
11-13
Foodmart批发店销售分析系统 课程设计
FoodMart超市业务分析系统数据仓库
最新发布
05-05
FoodMart超市业务分析系统数据仓库是一个用于存储、管理和分析FoodMart超市业务数据的专用数据库。它包含了所有与超市业务相关的数据，例如销售数据、顾客数据、产品数据、供应商数据等。这些数据被组织成事实表和维度表，并通过ETL（抽取、转换、加载）过程从多个源系统中提取、转换和加载到数据仓库中。 通过数据仓库，FoodMart超市可以进行一系列的业务分析，例如销售趋势分析、产品销售分析、顾客行为分析等。这些分析可以帮助FoodMart超市了解其业务运营情况，发现潜在的商机和问题，并制定相应的业务策略和决策。 数据仓库的建设和维护需要大量的精力和资源投入，但是它可以为企业带来巨大的商业价值和竞争优势。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

ShenLiang2025

您的鼓励是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值