4 ms·

4. Holy shit 4. I actually disagree with some of your other points; if I need DF calculation I'll do pandas in python, and in a pinch can make some raw SQL/te

4. Holy shit 4. I actually disagree with some of your other points; if I need DF calculation I'll do pandas in python, and in a pinch can make some raw SQL/terrible LINQ work; dynamic dashboards exist but data segregation is indeed a problem I don't see solved outside of the slightly aformentioned "make views and give very fine grained permissions" runaround; 3 I'm not sure I am understanding you right because I feel like I can do this in a combination of sql views and powerpivot, and 5, see the above, powerpivot. But 4, someday if it weren't the property of some company now I would LOVE to show off some of the hacks I've done to get manual value input in Excel to work to do live re-processing on data originally sourced elsewhere. Probably some of the least maintainable and robust code I have _ever_ written, and that's saying something at this point, unfortunately...

kfk 11y ago
I should have mentioned that all my points should be easily done in an Excel-like interface, so no coding allowed. Point 3 is how you look stuff, maybe team A defines China as China, but team B defines it as China + Taiwan + Hong Kong, at scale this kind of use case is very hard to manage on a centralized BI system. I'd be interested in hearing more about your hack, my email is in my profile, manual inputs are one of the biggest pain in BI and one of the reason people default to Excel files by email. In general, I am very passionate around those topics, but I am having a hard time building an audience around this.
- existencebox 11y ago
  Re: your profile, I saw a contact us for what looked to be a startup, and your medium page, but no email (unless I'm particularly blind/you meant the "contact us). That being said, it's a simple enough explanation so I'll just divest it here. The process I use certainly is constrained to certain requirements on the dataset, but it's "worked for me" as a pattern a few times now. 1. Get your data into powerBI, data source of your choosing. Build any custom columns you want primarily in the data model. 2. Bring the powerBI as a pivotTable (not directly as a chart.) 3. this is where things get messy/constrained, and will have needed to plan ahead for this bit. Treat a cell in the chart as your "input cell", and create new cells/new rows with formulas that utilize the input cell as WELL as the cells underneath the pivot chart you pulled in. NOTE: excel REALLY DOESN'T like you doing this. If you try to select the raw cells in the pivot table, it will get confused and not behave as you expect. If you type in the cell identifiers as you would with raw excel (B3:B24, etc) and they "just happen" to be under a pivot chart, however, it works fine. This step is largely where the "unmaintainability/fragility" comes into play since as you can probably tell, the whole workaround relies on the _exact_ structure of the data as it relates to cell locations. You may need to ensure this constraint yourself; e.g. if I had a timeseries pivotchart that I append to, I'd need to order descending of the most recent 100 rows, to ensure my static excel formulas were being populated as expected. I feel like my explanation of this is a bit lacking, so please don't hesitate to ask questions if I'm unclear, it's a rather mundane solution in and of itself. 4. Once you're confident your data isn't going to change "structure" during a refresh, you can build up a typical formulation chain -> chart as you would with pure Excel, except that the formulation utilizes your input row for custom on the fly user inputs, and is sourced from refresh-able external data.

kfk 11y ago

I should have mentioned that all my points should be easily done in an Excel-like interface, so no coding allowed. Point 3 is how you look stuff, maybe team A defines China as China, but team B defines it as China + Taiwan + Hong Kong, at scale this kind of use case is very hard to manage on a centralized BI system. I'd be interested in hearing more about your hack, my email is in my profile, manual inputs are one of the biggest pain in BI and one of the reason people default to Excel files by email. In general, I am very passionate around those topics, but I am having a hard time building an audience around this.

existencebox 11y ago
Re: your profile, I saw a contact us for what looked to be a startup, and your medium page, but no email (unless I'm particularly blind/you meant the "contact us). That being said, it's a simple enough explanation so I'll just divest it here. The process I use certainly is constrained to certain requirements on the dataset, but it's "worked for me" as a pattern a few times now. 1. Get your data into powerBI, data source of your choosing. Build any custom columns you want primarily in the data model. 2. Bring the powerBI as a pivotTable (not directly as a chart.) 3. this is where things get messy/constrained, and will have needed to plan ahead for this bit. Treat a cell in the chart as your "input cell", and create new cells/new rows with formulas that utilize the input cell as WELL as the cells underneath the pivot chart you pulled in. NOTE: excel REALLY DOESN'T like you doing this. If you try to select the raw cells in the pivot table, it will get confused and not behave as you expect. If you type in the cell identifiers as you would with raw excel (B3:B24, etc) and they "just happen" to be under a pivot chart, however, it works fine. This step is largely where the "unmaintainability/fragility" comes into play since as you can probably tell, the whole workaround relies on the _exact_ structure of the data as it relates to cell locations. You may need to ensure this constraint yourself; e.g. if I had a timeseries pivotchart that I append to, I'd need to order descending of the most recent 100 rows, to ensure my static excel formulas were being populated as expected. I feel like my explanation of this is a bit lacking, so please don't hesitate to ask questions if I'm unclear, it's a rather mundane solution in and of itself. 4. Once you're confident your data isn't going to change "structure" during a refresh, you can build up a typical formulation chain -> chart as you would with pure Excel, except that the formulation utilizes your input row for custom on the fly user inputs, and is sourced from refresh-able external data.