Create Dimensions and Fact from Excel Spreadsheet

[sudhirkumra]

@takbb I need some help in KNIME. I have got a spreadsheet and want to create a Star Schema for my Power Bi reporting. I need to create Dimensions and Fact from this. What would be the approach. I mean I need to select columns, deduplicate data to create Dimensions

[sudhirkumra]

Can anyone help with this please?

[iCFO]

You might be able to create a temporary H2 database in your workflow, then execute a SQL statement to get your schema. Not DB admin / schema are not really my area of expertise, but this would be my first direction.

Question re: Creation of Schemas in MS SQL Server using KNIME?

Hi Everyone, I dug through the forum, documentation, and youtube before posting this question as I didn’t see a clear answer. My team recently was given access to a new empty Data Mart on a MS SQL Server. The Data Mart has no schemas other than the default ‘dbo’. We’d like to exercise more control over our area’s structure and create new Schemas using KNIME. However… after looking for an extended period of time - it doesn’t seem that there is a node or functionality within the existing DB n…

[takbb]

Hi @sudhirkumra , a good quick summary of of the process of “normalisation” is described here by @Daniel_Weikert

How to bring data into (at least) 3rd normal form?

You create the dimension tables by copying the fact table (filter the relevant columns), remove the duplicates, add an identifier e.g. counter node, join it back to the original table to map the id (counter node created) to the column (e.g. name in your screen. You do this for all your dimensions. Finally you remove the text in the fact table (original table) as you have now each column as an id. br

This aligns with your comment “I need to select columns, deduplicate data to create Dimensions”

I’d imagine that if you had a spreadsheet of department and employee info in a de-normalised form…

Dept_Name	Dept_Budget	Employee_Name	Start_Date
Accounts	1000000	Alan	2021-02-05
Accounts	1000000	Belinda	2016-03-02
Sales	2000000	Claire	2020-12-03
Marketting	3000000	David	2017-06-01
IT	50	Elgin	2024-02-27
IT	50	Frances	2016-09-05

…You could put together the column filters, duplicate row filters and value lookups something like this:

(I’ve not uploaded a workflow as this as it is just a non-functional mockup)

So you’d filter out the columns related to Employees, and separately the columns related to departments. Remove duplicate, and create an id for each. Then back on the “Fact” table, you can apply Value Lookups to get the IDs for each employee and department, and then use column filter to remove the columns relating to Employee and Department other than the IDs

Employee_Name	Start_Date	Emp_ID
Alan	2021-02-05	0
Belinda	2016-03-02	1
Claire	2020-12-03	2
David	2017-06-01	3
Elgin	2024-02-27	4
Frances	2016-09-05	5

Dept_Name	Dept_Budget	Dept_ID
Accounts	1000000	0
Sales	2000000	1
Marketting	3000000	2
IT	50	3

and the relationship between departments and employees, which in this case represents the “Fact” table, although you’d normally have some additional information/columns besides the IDs:

Dept_Emp_ID	Dept_ID	Emp_ID
0	0	0
1	0	1
2	1	2
3	2	3
4	3	4
5	3	5

I don’t know what form you specifically need the data to be for working with PowerBI, but this would effectively turn your data into a normalised “relational” structure.

[sudhirkumra]

Is Math Formula like a Surrogate Key? I want a Number to be generated like 100, 101, 102,103, 104, 105 for each dimension How can I use a node to do this for me?

[MartinDDDD]

Not sure if this is what you are after, but once you have your items in a table in the desired order, you can create a column with the number format you described using Column Expressions: 100 + rowIndex()