Go to Main page..

How to make a fact table? , Microsoft SQL Server





How to make a fact table? [new]
I have 8 tables with facts from which it is necessary to make analytics.
And a bunch of table-dictionaries such as surnames, names of cities, warehouses, etc.

For analytics (powerBI) it is necessary that all the facts are located in the central table. This will make it possible to do some kind of analytics like “the sum of the facts in the third table is divided by the sum of the facts in the first”, etc.
You have to do this:
 VIEW facts ( A_fact , B_fact , C_fact , D_fact , E_fact , F_fact , G_fact , H_fact ) CREATE VIEW facts ( A_fact , B_fact , C_fact , D_fact , E_fact , F_fact , G_fact , H_fact )
 ФИО , Офис , A_fact , null , null , null , null ... SELECT Name , Office , A_fact , null , null , null , null ...
 A FROM A
 ALL UNION ALL
 ФИО , Офис , null , B_fact , null , null , null ... SELECT Name , Office , null , B_fact , null , null , null ...
 B FROM B
 ALL UNION ALL
 ФИО , Офис , null , null , C_fact , null , null ... SELECT Name , Office , null , null , C_fact , null , null ...
 C FROM C
 ALL UNION ALL
 ...

Is there any way to not specify these "null"? When trying to add a new fact column to table B
 ...
 ALL UNION ALL
 ФИО , Офис , null , B_fact1 , B_fact2 , null , null , null ... SELECT Name , Office , null , B_fact1 , B_fact2 , null , null , null ...
 B FROM B
 ALL UNION ALL
 ...

You have to patiently write null in all other tables.

Or is this task solved completely differently?
Re: How to make a fact table? [new]
Now, ever and forever 8 facts will be?

Analyze it.
Re: How to make a fact table? [new]
aleks222
Now, ever and forever 8 facts will be?

Analyze it.

facts may be added.
And what could be the suggestions? Here, I have 8 tables, and the facts are not 8. There are also text types like "categories", etc. - I already add these columns and null on all 8 tables to scatter.

These are not facts of the type "amount in rubles", to which at any time can be added "amount in dollars", "amount in yens", etc.
Those. these are exactly global facts: "revenue volume", "cost volume", "order volume", etc.
There is a table with information about signed contracts - it contains information about income,
there is a table with all sorts of accounts - it contains information about costs
there is a table of incoming orders - it contains information about orders

and so on. Then in the PowerBI I subtract the second from the first one and make a profit, etc.

In cubes, similar problems are solved. But I can’t do anything about it - I don’t have a cube server here.
Re: How to make a fact table? [new]
Charles Weyland,

Columns are attributes, and you get that columns are entities.

But the task as a whole is not clear to me. An example would be much clearer.
Re: How to make a fact table? [new]
Pizza pizza
Charles Weyland,

Columns are attributes, and you get that columns are entities.

But the task as a whole is not clear to me. An example would be much clearer.

All columns, starting with the fourth, are in rubles.
Executive_ID Office_ID date Planned income Volume of the received order (rub) volume (qty) shipped (rub) shipped (qty)
one one 01.01.2018 400 null null
one one 1.02.2018 600 null null
one one 1.03.2018 600 null null
one one 01/24/2018 null 80 five null null
one one 01/26/2018 null 120 3 null null
one one 01.31.2018 null 110 four null null
one one 4.02.2018 null 50 3 null null
one one 02.02.2018 null 220 6 null null
one one 02/15/2018 null 90 6 null null
one one 02.25.2018 null 190 9 null null
one one 4.02.2018 null 230 four null null
one one 01/26/2018 null null null 40 2
one one 01/29/2018 null null null 140 2
one one 03.02.2018 null null null 130 four
one one 02.27.2018 null null null 100 6
one one 02.02.2018 null null null 300 6

Table - the result of combining several tables (plans, revenues, orders). This allows me to make the very notorious "star" scheme, zafigachiv all the facts in a single table. And thus, choosing a specific artist working in a particular office, I can find out his work efficiency in January, February, March and the total, compare "Expectations and reality", etc.
Re: How to make a fact table? [new]
Well, even here I forgot two columns with nulls.
Re: How to make a fact table? [new]
Charles Weyland
Table - the result of combining several tables (plans, revenues, orders). This allows me to make the very notorious "star" scheme, zafigachiv all the facts in a single table . And thus, choosing a specific artist working in a particular office, I can find out his work efficiency in January , February, March and the total, compare "Expectations and reality", etc.

And there is the date of the order. There is a date of receipt of goods at the warehouse under the order of the client. There is a date of commencement of the order (when the customer expects receipt of goods) There is a date of the first shipment. There is a date of the last shipment. There is a first payment date. There is a date of last payment. There is a refund date for part of the payment as a correction between the accounting departments.

The seller sold the wagon clips to the client on 1 January. February 1, the car arrived at the warehouse of the company. March 1, he was overloaded on trucks and sent. On April 1, truck number 1 reached the customer and was accepted at his warehouse. April 2, the truck issued the invoice. April 3 issued a payment order to pay for the first truck. April 4 came the second truck. It turned out that the clips had rusted while they were driving. On May 4, the returned truck was issued as a return from the client at the company's warehouse. On June 1, another wagon arrived with staples for ANOTHER customer. With whom it was agreed that one of two trucks would be sent to him, and one would be sent to the client who had been waiting for a long time. Of course, when making a receipt for the warehouse on June 2, all the clips from the second car were issued as customer order number 2.
Then the second truck was sent and unloaded at the first client in July, in August the last - the second payment - until the last shekel was sent to a bank account. On September 1, after reconciliation, the accounting department closed the supply agreement as executed. The manager is preparing to receive their bonuses for the sale.


The question is how to track the success of sales for customer number 1 and sales manager number 1 by date, if you select January orders as a filter? See, what a good question? Your customers generally understand WHAT they will see in the PowerBI reports, or it will be like in the song of the little-known Russian performer Alyona Apina - "I blinded him from what was, and then what happened - I fell in love."
Re: How to make a fact table? [new]
Charles Weyland
Then in the PowerBI I subtract the second from the first one and make a profit, etc.

And then it turns out that the amounts on orders excluding Russian VAT, the amount on payments including VAT, and so on. And that you can not deduct and share anything with nothing.

You still know, I will tell you, only you do not be offended. Russian IT is so senseless and merciless that first of all it amazes the imagination of the performers - and only then along the chain of customers of such “accounting” - “reporting” systems and their users.
Re: How to make a fact table? [new]
Andy_OLAP
Charles Weyland
Then in the PowerBI I subtract the second from the first one and make a profit, etc.

And then it turns out that the amounts on orders excluding Russian VAT, the amount on payments including VAT, and so on. And that you can not deduct and share anything with nothing.

You still know, I will tell you, only you do not be offended. Russian IT is so senseless and merciless that first of all it amazes the imagination of the performers - and only then along the chain of customers of such “accounting” - “reporting” systems and their users.

so why be offended.
All right describe. All detailed information on the movement of goods, with IDshniki and others - lies in its tables. In the right currencies, with the necessary notes and other things.

The table described above has a specific task that is not tied to specific orders.
And the bottom line is that you just need to look at the performance of employees and departments. It is planned that he will be able to perform for one amount, he receives tasks for another, performs for the third. There is no task to follow a specific customer.
Re: How to make a fact table? [new]
Charles Weyland
It is planned that he will be able to perform for one amount, he receives tasks for another, performs for the third. There is no task to follow a specific customer.

And when orders are actually served by another sales manager? The first went to the hospital, and the second runs on divisions, controls the shipment, payment, calls up, when there will be a return of the rejected, and so on.

You will now have several such "fact tables", each of which is tightly tied to one date. And then the customer of the system will remember a small nuance and will ask to make a "minor alteration". Now imagine how all the fact tables are redone, how all the formulas in the PowerBI reports "go" completely.

To summarize, I don’t believe in a silver bullet or in a single fact table for any very small task when a plan and a fact from different calendar periods are compared.
Re: How to make a fact table? [new]
Charles Weyland,

for me it is not clear how you are going to fill this one table.

if purely for consolidated or overview reports I’d make a view / s and from it / them would have done
Charles Weyland
some analytics like "the sum of the facts in the third table is divided by the sum of the facts in the first", etc.
Re: How to make a fact table? [new]
Charles Weyland,

make an olap die on which you power your powerBI
Re: How to make a fact table? [new]
Pizza pizza
Charles Weyland,

for me it is not clear how you are going to fill this one table.

if purely for consolidated or overview reports I’d make a view / s and from it / them would have done

no way

Attached file Size - 15Kb
Re: How to make a fact table? [new]
Stariknavy
Charles Weyland,

make an olap die on which you power your powerBI


Charles Weyland
In cubes, similar problems are solved. But I can’t do anything about it - I don’t have a cube server here.
Re: How to make a fact table? [new]
Andy_OLAP
And when orders are actually served by another sales manager?

Questions sound.
But the goal of the years is to make a complex ideal system that does not need to be changed for dozens of years - the task is wrong.
Everything is simpler here: he riveted the request, drove it into a view, made a report, people looked, thought - like / dislike. Rejected, made another decision.

The approach to data analysis is always chosen on the basis of reality and with a certain degree of reliability.
A vivid example - the mean and median values ​​are also largely meaningless and not accurate, and the analysis of mean values ​​can be criticized indefinitely. But still in demand, they allow you to understand some of the trends of the measured values. And they are used perfectly normally, keeping in mind the peculiarities of the initial data.

Yes, the order was taken in January, and completed in March. But the calculations will be made "March revenue" / "March order volume".
This is largely moronic.
But the customer wants to see in practice how far this reflects reality, because many orders are actually closed on the same day.
CUSTOMER
"Let's do something for a start, show at least this solution for a start, and in practice we will talk about why it has insufficient accuracy and decide how to improve it."

Statistics - in general, the thing is: how you turn, you will get. Here, people want to twist it like this, and twist it differently.
Re: How to make a fact table? [new]
Charles Weyland
no way

Well, make a view generator from a simplified template.
Null will insert an iron mechanism.
Re: How to make a fact table? [new]
Charles Weyland,

Create a select-insert data collection, do not torment the views.
Re: How to make a fact table? [new]
Charles Weyland
Pizza pizza
Charles Weyland,

for me it is not clear how you are going to fill this one table.

if purely for consolidated or overview reports I’d make a view / s and from it / them would have done

no way


if the problem is with nulls, then

 ФИО , Офис , a . SELECT Name , Office , a .  b . A_fact , b .  c . B_fact , c .  C_fact ...
 a FROM a
 JOIN b ON ( id or key or ФИО Офис ) apparently LEFT JOIN b ON ( id or key or full name Office )
 JOIN c ON ( id or key or ФИО Офис ) LEFT JOIN c ON ( id or key or full name Office )
 ...
Re: How to make a fact table? [new]
Charles Weyland
Pizza pizza
Charles Weyland,

Columns are attributes, and you get that columns are entities.

But the task as a whole is not clear to me. An example would be much clearer.

All columns, starting with the fourth, are in rubles.
Executive_ID Office_ID date Planned income Volume of the received order (rub) volume (qty) shipped (rub) shipped (qty)
one one 01.01.2018 400 null null
one one 1.02.2018 600 null null
one one 1.03.2018 600 null null
one one 01/24/2018 null 80 five null null
one one 01/26/2018 null 120 3 null null
one one 01.31.2018 null 110 four null null
one one 4.02.2018 null 50 3 null null
one one 02.02.2018 null 220 6 null null
one one 02/15/2018 null 90 6 null null
one one 02.25.2018 null 190 9 null null
one one 4.02.2018 null 230 four null null
one one 01/26/2018 null null null 40 2
one one 01/29/2018 null null null 140 2
one one 03.02.2018 null null null 130 four
one one 02.27.2018 null null null 100 6
one one 02.02.2018 null null null 300 6

Table - the result of combining several tables (plans, revenues, orders). This allows me to make the very notorious "star" scheme, zafigachiv all the facts in a single table. And thus, choosing a specific artist working in a particular office, I can find out his work efficiency in January, February, March and the total, compare "Expectations and reality", etc.


The issue is global architecture.

IF MAKE CORRECT, THEN

No need to merge into one table.
1) Keep different tables,
2) Make the date granular up to the day.
3) Make a cube and for each table a group of measures in the cube
4) They are vertically matched by date and other matching measurements.
SSAS has already worked

IF YOU DO IT QUICKLY, PUT THIS IN COLUMNSTORE and don’t worry

The question is that you are trying to implement self-made OLAP, and you can use ready-made
Re: How to make a fact table? [new]
a_voronin,

what cube? The author does not have SSAS.
Re: How to make a fact table? [new]
Vladislav Kolosov
a_voronin,

what cube? The author does not have SSAS.


So the question is what can do this through a cube / SSAS.
Re: How to make a fact table? [new]
Vladislav Kolosov,

Everything connects

https://docs.microsoft.com/en-us/power-bi/desktop-ssas-multidimensional
Re: How to make a fact table? [new]
a_voronin
Vladislav Kolosov,

Everything connects

https://docs.microsoft.com/en-us/power-bi/desktop-ssas-multidimensional


Here you are a strange man. You say that "everything" is connected, and you yourself give a link to an article, where it is said in black and white that not everything is connected.

I have a cube, I can not connect it. For now. Then they will buy it - then I can. So the problem described above is only a temporary solution.

Attached file Size - 23Kb



Old articles you may read here
Go to Main page






ZZZZZZZZZZZZ