| 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?
| Now, ever and forever 8 facts will be? |
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.
| 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.
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.
|Well, even here I forgot two columns with nulls.|
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."
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.
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.
| 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, |
make an olap die on which you power your powerBI
Attached file Size - 15Kb
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.
Statistics - in general, the thing is: how you turn, you will get. Here, people want to twist it like this, and twist it differently.
Well, make a view generator from a simplified template.
Null will insert an iron mechanism.
| Charles Weyland, |
Create a select-insert data collection, do not torment the views.
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 ) ...
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
| a_voronin, |
what cube? The author does not have SSAS.
So the question is what can do this through a cube / SSAS.
| Vladislav Kolosov, |
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