asked 2 days ago
Hello,
I have couple dimensions:
1. Contract (ID, Name, Surname, FromDate, ToDate, ...) - about 5000 rows, date interval is about 2-5 years
2. Date (year, monts, day) - around 20 years * 365 ~7500 rows.
I need to have report/chart per each date, how many contracts there were daily. Currently I do: Contract INNER JOIN Date WHERE date BETWEEN Contract.FromDate AND Contract.ToDate. Analysis works fine, but loading time is very long - I get 500 MB size dataset. Contract data is repeated for every day, so it becomes LARGE! There is less than 1 MB uncompressed data in total.
How can I have separate dimensions and data, which are joined by key in flexmonster?
⋅ 2 days ago
Hello, Mantas!
Thank you for reaching out to us.
Kindly note that Flexmonster is designed to fetch the data from one source, e.g. a CSV/JSON file, and then process and aggregate it. For a JSON/CSV data source, we need to load all the data into the browser memory before processing it.
We understand that loading a file of 500 mb into the browser memory might be a time-consuming operation. To improve the performance, we suggest moving the aggregation logic to the server side and only loading the aggregated values to the Flexmonster client.
Could you please let us know which data source you are using to connect Flexmonster to your data and which database you are working with? With this information, we'll be able to advise you further.
It would be also helpful to have more details about your use case, e.g. a sample report object.
Looking forward to hearing your feedback.
Kind regards,
Solomiia
⋅ 23 hours ago
I'm using json data source, which is generated by C# ApiController.
I have several MS SQL data tables, and I use this query to get data:
"siauliai.Rent" has data with date range,
"siauliai.CalendarCaches" is date dimension
SELECT r.[ID]
,r.[Nr]
,r.[Remarks]
,r.[StudentID]
,(SELECT TOP 1 __.Nr FROM siauliai.StudentGroups _ INNER JOIN siauliai.Groups __ ON __.ID=_.GroupID WHERE _.ID=r.StudentGroupID) [Group_Nr]
,(CASE WHEN r.[StudentGroupID] IS NULL THEN 0 ELSE 1 END) IsStudent
,r.[StudentGroupID]
,r.[LeaseHolderID]
,r.[Name]
,r.[Surname]
,r.[Phones]
,r.[Emails]
,r.[NightPrice]
,r.[TotalPrice]
,COALESCE(b.[Name], '') Building
,r.[BuildingID]
,COALESCE(a.[No], '') Apartment
,a.Size Apartment_Size
,r.[ApartmentID]
,COALESCE(af.[Floor], 0) ApartmentFloor
,r.[ApartmentFloorID]
,(SELECT TOP 1 _.[Name] FROM siauliai.Genders _ WHERE _.ID=r.GenderID) Gender
,r.[GenderID]
,r.[TakeWholeApartment]
,(SELECT TOP 1 _.[Name] FROM siauliai.Countries _ WHERE _.ID=r.CountryID) Country
,r.[CountryID]
,r.[ExpelRequestNo]
,r.[MonthPrice]
,(SELECT TOP 1 _.[Name] FROM siauliai.ExpelRequestReasons _ WHERE _.ID=r.ExpelRequestReasonID) ExpelRequestReason
,r.[ExpelRequestReasonID]
,c.Date
,c.IsWorkday
FROM siauliai.Rents r
INNER JOIN siauliai.CalendarCaches c ON c.Date BETWEEN r.StartTime and COALESCE(r.[ExpelTime], r.EndTime)
LEFT OUTER JOIN siauliai.Buildings b ON b.ID=r.BuildingID
LEFT OUTER JOIN siauliai.Apartments a ON a.ID=r.ApartmentID
LEFT OUTER JOIN siauliai.ApartmentFloors af ON af.ID=r.ApartmentFloorID
WHERE r.Deleted IS NULL
Here is the range data is generated for:
select count(*), MIN(StartTime), MAX(EndTime) from siauliai.Rents WHERE Deleted IS NULL
count: 765, date range from '2022-03-04' to '2028-06-30'
What I need is chart per each day (attached) with possibility to filter by all fields + display fields (like name, contract no and al values in the select I wrote)
Data is served by C# ApiController code - it retrieves data by this select, it does column name translation and sends all the data, eg:
Mapping (...api/Pivot/LoadMapping?table=View_Rents&id=0&4):
Data: (.../api/Pivot/LoadData?table=View_Rents&id=0&4&483368903):
[
{
"ID": 33,
"Nr": "MBS-15",
"Remarks": "",
"StudentID": 12516,
"Group_Nr": "SvM23",
"IsStudent": 1,
"StudentGroupID": 13565,
"LeaseHolderID": null,
"Name": "*******",
"Surname": "*******",
"Phones": "+3706*******",
"Emails": "*******@gmail.com",
"NightPrice": null,
"TotalPrice": 255.0,
"Building": "*******",
"BuildingID": 3,
"Apartment": "104",
"Apartment_Size": 17.52,
"ApartmentID": 86,
"ApartmentFloor": 1,
"ApartmentFloorID": 8,
"Gender": "Moteris",
"GenderID": 2,
"TakeWholeApartment": 0,
"Country": "Lietuva",
"CountryID": 6,
"ExpelRequestNo": "MBP-2",
"MonthPrice": null,
"ExpelRequestReason": "Keičiant kambarį",
"ExpelRequestReasonID": 2,
"DateWY": 2024,
"DateW": "35",
"DateY": 2024,
"DateQ": "3 ketvirtis",
"DateM": " 9 mėn.",
"DateD": " 1 d.",
"IsWorkday": 0
},
{
"ID": 33,
<...>
}
]
⋅ 17 hours ago
Hello, Mantas!
Thank you for your swift response and providing additional details about the use case.
For the described case, our team would like to suggest our server-side tool called Flexmonster Data Server. The Data Server is responsible for fetching the data from your database and aggregating it, so only the aggregated values are loaded into the browser.
Here is the detailed guide on how to connect the Microsoft SQL database to Flexmonster directly using Flexmonster Data Server: https://www.flexmonster.com/doc/connect-to-mssql-database/.
Alternatively, if you need more customizations before loading data into Flexmonster, you can also use Flexmonster Data Server as a DLL: https://www.flexmonster.com/doc/getting-started-with-data-server-dll/.
Hope you will find our answer helpful.
Feel free to ask if you have any further questions about Flexmonster Data Server.
Kind regards,
Solomiia
