3,114 questions
Best practices
0
votes
0
replies
57
views
Materialising tables for multiple end user profiles in Redshift
Imagine there's a reporting tool for which users might have the permission 'Admin' or 'User'.
We have a dimension in our models called admin_view and if the value is true then only users with Admin ...
- 572
Best practices
0
votes
2
replies
121
views
How to model and persist entities whose state changes over time
I’m designing a domain model where some entities evolve over time, and I need to keep a historical record of their state that can be retrieved later.
A simplified example:
A Credit Line represents a ...
- 605
0
votes
0
answers
48
views
How can I make an "is a" correlation in an ERD where it limits one entity set?
How do I make it so the entity set, SUV, is a CAR, though the entity set CAR is not a SUV in an ERD?
- 21
1
vote
1
answer
80
views
Power BI / Microsoft Fabric Semantic Model – “Sort by column” not working
Screenshot from DAX Studio
Screenshot from Semantic Model
I’m working in a Microsoft Fabric Semantic Model (same behavior as Power BI Desktop model) and I’m trying to sort one column by another using ...
- 11
0
votes
1
answer
46
views
How to model a many-to-many relationship in DynamoDB with composite keys and enforce unique (id, ruleId) pairs?
I have a DynamoDB table defined as follows:
Partition key: id (string)
Sort key: ruleId (string)
Example item:
{
"id": "123",
"ruleId": "abc"
}
What I want is ...
2
votes
1
answer
54
views
Query based on fields from 2 different Collections in MongoDB
I am developing web application using Gin and MongoDB. I have defined models for user like,
type User struct {
Id nftdb.ObjID `bson:"_id,omitempty"`
Tenant string
...
1
vote
0
answers
53
views
How to show "PAST" (before today) + next 12 months with MMM X-axis labels
I'm trying to build a bar chart in Power BI where the X-axis shows the following:
A "PAST" bucket representing all data before today
The next 12 months, displayed as 3-letter month names ...
- 49
1
vote
1
answer
262
views
Implement Slowly Changing Dimensions (SCD) - Type 2 Using DuckDB
I want to implement SCD-Type2, and keep track of historized data, I am using for this task DuckDB, but I found out that DuckDB does not support Merge Statement.
The idea I have is to have two separate ...
- 165
2
votes
0
answers
144
views
Is it possible to create true sentinel values in Rust?
Say I have some simple struct, eg:
struct SomeStruct {
things: Vec<usize>
recent_thing: Option<usize>
}
I'm trying to assert some invariant on this struct. For example, maybe we want ...
- 968
0
votes
1
answer
36
views
Dimensional Modeling- How to handle Code-Name-Description lookup table
Is there a best practice to handle OLTP lookup tables( code-name-description tables) when converting to Dimension? Eg, the ACCOUNT table in OLTP has almost 15 codes (Eg: TRADING_PLATFORM_CD, ...
- 61
1
vote
2
answers
35
views
Where to store the "best-before-date" in my ER-Model
I have a model about a butcher shop. The entities refer to
"Bestellung" => "Order" (Like order to get more inventory)
"Lieferant" => "Supplier"
"...
- 431
1
vote
1
answer
66
views
Is a localField $lookup faster than a pipeline?
In MongoDB (version 8) I'm wondering about the most read-efficient way of storing a "sharing mechanism". I.e. the users can share items with other items within the same collection.
When ...
- 1,403
0
votes
0
answers
23
views
Database based upon schema.org or MS Common Data Model
I want to start picking up modern development so I’m looking at writing a simple web app for CRM / contact management.
I’ve seen the likes of https://schema.org and also Microsoft’s Common Data Model, ...
- 359
0
votes
1
answer
88
views
a confusion about schema design for storing store's profits and how to retrieve them in mongoose [closed]
For displaying the (daily-weekly-monthly-annual) total profits for the store's dashboard.
I want to store the data in an array for each store to not have multiple docs for the same store but different ...
- 198
0
votes
0
answers
18
views
Data modelling, data mapping and erwin related story
As a data modeler, I want to data model the "Manual _Update_ODS.xlsx" file.
Per agreement with Karthik, L1s do not go through Domain Model process. However, if you easily find a match then ...
- 1
-1
votes
1
answer
124
views
How to draw an ER diagram where there are 2 entities with exactly same attributes
Each appliance is classified as either: Kitchen appliances, Entertainment appliances, and it’s important to keep track of their type.
Kitchen appliances and Entertainment appliances have details about ...
- 97
0
votes
1
answer
28
views
Database modelling for 2 many-to-many relations that have their own one to many relation
With reference to the attachment, data has a many to many relationship with both entities. What would be the right thing to do?
To add more context, each data entry can have 0, 1, 2, ..., N categories,...
- 59
0
votes
1
answer
83
views
Power BI matrix - get data into correct format
I'm usually quite good with Power BI, but weirdly stuck on this matrix, that I'm trying to create. I just can't seem to get the data in the right format to produce the below chart. Can anybody help?
...
- 149
0
votes
1
answer
94
views
How to model a M:M using bridge table into a star schema when there is a need for bidirectional relationships?
I have FactSales and FactCharges.
Granularity of FactSales is OrderId+OrderLineId.
Granularity of FactCharges is OrderId+ChargeDesc.
There is a M:M relation between them on OrderID.
I have a table ...
- 9,886
0
votes
0
answers
24
views
How to optimize a data model with bidirectional filtering?
I have Product dim that filters the FactSales (1:M)
And Charges dim that filters the FactCharges (1:M)
There is a bridge table with a key value (concat of saleid, poid). This has a 1:M relation with ...
- 9,886
0
votes
0
answers
41
views
Why do sales (fact) without charges (fact) not show up in table visual when using a bridge table to model M:M relation using bidirectional relation?
I have 2 fact tables: FactSales and FactCharges.
The sales is a header detail (order header and details) level of granularity (OrderDetId), whereas Charges are at the order header level of granularity ...
- 9,886
0
votes
0
answers
28
views
Power BI relationships
I created following data set as illustrated below:
You can see four level's.
level 4: the records that tie everthing together, in this case : reservations. Or more precisely: object-reservations, ...
1
vote
1
answer
113
views
power query design for performance of queries (using SOAP connector) for Power BI/Excel
I need to manage a large dataset of approx 100.000 records per year over many years.
The data is retrieved with SOAP connectors using custom query's.
Each records has 'basic info' and 'historic info' ...
0
votes
0
answers
100
views
How to Use a Dimension for Filtering While Avoiding SQL Execution in Looker?
I’m working on a Looker project with two main datasets:
Table A: Contains only id values and other related data.
Table B: Maps id values to human-readable name values (e.g., id-name).
My goal is to ...
- 91
0
votes
1
answer
159
views
How to Create a Generic Semantic Data Model in Tabular Editor for Power BI Reports with Separate Datasets?
I’m working on a Proof of Concept (POC) to create a generic semantic data model using Tabular Editor that connects to multiple datasets for Power BI reports. The goal is to have one centralized ...
- 15
0
votes
1
answer
50
views
Obtain avg number of monthly active customers on the web over the past 6 months. An active cust is one that has purchased within the prior 12 mnths
I am seeking help with a complex data modelling problem I am trying to solve. Was wondering if any kind soul with a good SQL expertise is able to help me.
The question is: obtain the average number of ...
0
votes
0
answers
133
views
Having Business Key in Kimball style fact tables
Many Kimball style star schema articles suggest that not to have a BK in Fact tables, the grain/uniqueness of Fact tables are supposed to be maintained by the combined SKs from its Dimension tables.
...
- 1,625
1
vote
1
answer
70
views
Optimizing low-level structure for CPU memory performance in my game simulator [closed]
I am writing a world simulator for a turn-based game. The key feature is that there will be many NPCs with detailed psychology modelling, perhaps 20,000 - 30,000 of them. I want all NPCs to make their ...
- 23
0
votes
1
answer
51
views
Power BI - Tree chart out of only numerical data
In my data model I have a table with data that approximately looks like this:
ID
Cash_All_Accounts
Cash_Current_Accounts
Cash_Saving_Accounts
Cash_Long_Term_Saving_Accounts
...
-1
votes
1
answer
75
views
Kimball Model Design - When to Snow or Star
I have data model for a Kimball style data warehouse. Regarding the whole Star vs Snowflake schema conundrum when it comes to dimensional tables and their ownership.
Scenario: A bunch of IoT Devices ...
- 247
0
votes
1
answer
184
views
DynamoDB Hot Paritions Key Prefix and Overloading GSIs
I'm trying to implement a single table design in DynamoDB and I've seen recommendations to use prefixes for partition keys to identify entities. For example, a car's record might have a partition key ...
- 3
-1
votes
3
answers
176
views
What is a good way to determine if all fields on a record except one are empty?
I've got a record that I use for making update requests via json/Jackson:
public record UpdateRequest(
Optional<String> field1,
Optional<LocalDate> field2,
Optional<@Max(24) ...
- 1,861
0
votes
1
answer
56
views
the right way to model the data in collection and documents [closed]
I am building an app where I am planning to use the Firestore database. the structure needs to be like this:
users (collection) --> uids (documents one per user uid)
now each user uid document ...
- 1,338
1
vote
0
answers
126
views
How to model change log history for opensearch document property, and aggregate on it?
I need to keep track of changes to a particular field in an opensearch document, and to be able to find aggregations on that field per date basis (for example, per day), or find count/IDs of docs that ...
- 693
0
votes
1
answer
74
views
Get latest values from a SCD dimension table
I have a requirement where I maintain type 2 SCD in my dimension table as below.
For example on day_1 the material code M-01 has a name mat_1_old - now the transactions on day_1 are mapped with ...
0
votes
0
answers
51
views
Query is not working properly in my Mern app
I'm building a lead data website using the MERN stack. Users can filter data based on various criteria like country, city, job title, seniority, industries, department, email type, and more (over 10 ...
0
votes
1
answer
135
views
Building a Fluid Flow Simulation App: Seeking Advice on Pipe Network Architecture
I’m working on creating a web application to simulate piping systems, where you can place pipes precisely and track the fluid flow theoretically (using thermodynamic calculations), including pressure ...
0
votes
1
answer
61
views
Determining the Appropriate Grain for a Pharmacy Consultation and Prescription Fact Table
I’m working on a data warehouse for a pharmacy business process and need some advice on determining the appropriate grain for the fact table.
The business process is as follows:
A pharmacy provides ...
- 244
-1
votes
1
answer
58
views
Power BI Data Modelling - select dish based on the ingredients available
Not sure if this is possible in Power BI, I have a table with Dish and Ingredients. I want to create a filter on what ingredients are available so that I can then make the Dish.
If I filter on [Flour] ...
- 149
0
votes
1
answer
301
views
A relationship is not working when using a measure
My model contains a fact table and a dimension. The dimension contains Full Name, Login, Access, DOMAIN. Domain is a numeric number which is used as a key to the fact table. Here is some dummy data:
...
- 412
0
votes
1
answer
41
views
Power BI Average Measure - replace values with 0
I have a power BI measure which sort of works. I have a dataset with half hourly data (for one year) indicating either a value or 0. The measure calculates the average of each half hour across the ...
- 123
0
votes
0
answers
74
views
Can nullable column (column with blanks) be made into a dimension table?
I have a fact table called ProjectTasks.
It's a left join of project and tasks table. A project can have 0 or more tasks. Thus for projects that don't have any task, the task columns are nulls (blanks)...
- 9,886
1
vote
1
answer
330
views
The column 'Date[Year]' either doesn't exist or doesn't have a relationship to any table available in the current context
I'm going through a Coursera course, and they're asking to add a calculated table based on different columns from other tables (Step 3 in the attached image).
They propose the DAX solution attached, ...
- 41
0
votes
1
answer
213
views
How to handle multiple dimension records having the same name even though they have unique key/relation with the fact table (1:M)? [closed]
I'm looking at Product dimension table.
It has got products table obtained by performing a SQL UNION between Product tables in 2 databases. The columns are:
Dbname,productid,productname,...
- 9,886
-1
votes
1
answer
76
views
Modifying column values in Pandas?
How do I modify a coumn value in Pandas where new values are two times the previous values?
I tried this:
employees.loc['salary'] = employees['salary']*2
Input:
DataFrame employees
+---------+--------...
0
votes
0
answers
42
views
Is this star schema model valid or does it required updates to be more efficient?
I am working with a DataWarehouse from which I'll be collecting data, I am trying to build a star model schema on data modeler in order to be then use on Snowflake and then later in Power BI for BI ...
0
votes
2
answers
65
views
Should sales order number and purchase order number be placed in FactOrder table or should it have its own dimension?
I have FactOrders with date,saleid,productid,salesorderno,purchaseorderno,qty,price
There are date and product dimension that link to FactOrders on date and productid respectively.
Should sales order ...
- 9,886
1
vote
0
answers
38
views
Is it okay to flatten hierarchical data in MongoDB and repeat data in the documents
I'm trying to model the data as follows. Is this good practice or is there a better way so that I don't have to repeat the data in the checklist collection.
/* technical book 1
reference number
...
- 1,003
1
vote
1
answer
309
views
Why, when changing the double format to int64 in power bi, the memory occupied by a column decreases, but the total weight of the file increases?
I have a report in which one of the columns contains many unique integers. I saw that the data format is decimal (double) , I decided to convert this column to int64 (integer) format. In dax studio, ...
0
votes
1
answer
40
views
Is there a way to relate a Calendar lookup table and a 24hr Time lookup table to 2 different tables on more than 1 column in PowerBI?
I have 2 tables, encounters table and procedures table, that both have 2 columns containing a date data type and 2 columns containing a time data type. I have created a Calendar lookup and a Time ...
