Morning

Morning

@FaheemMitha looks on topic for the site, yes. But reading again, maybe it's more appropriate for SO (if it is LUA issue).

(more appropriate -> you'll get an answer faster. I think it's on topic here but I don't know if anyone with lua knowledge would follow and answer

Morning

@Vérace dbfiddle.uk is back up

@PeterVandivier I thought Oracle was still down?

topanswers.xyz/databases?q=1865#a2096

> Sorry about that, pretty much all the Linux machines were affected when I added MariaDB 10.6 and ran out of space in my ZFS pool (which I need to keep an eye on going forward)

@ypercubeᵀᴹ As it turns out, and was explained to me on TeX SE, it's entirely a Lua issue, so it really is off-topic here. It's to do with how Lua implements stateful iterators, and how it interacts with the Lua feature of returning multiple values. I think their design decision was nutty, but perhaps it was expedient.

And it's not the first time I've thought that of Lua, either.

It's also clearly documented to "Programming Lua". Though with no justification or explanation.

@PaulWhite i stand corrected. saw the message in chat - didn't notice there was an open question where he mentions Oracle still being down

@Vérace Croatia is good to relax I guess, have a swim or drinks. Florence is nice if you like architecture and museums

@McNets these: @var2 not in (3, 4) and @var2 in (3, 4) should have var1, not var2, right?

@ypercubeᵀᴹ ooops, yes, you're right

Morning all.

How does one de-normalise data for Power BI?

How can I determine the state (test, productive, QS) of each component in a Power BI chart where I add Server[Name], Instance[Name], Database[Name] and then State[Name] but for each entity?

@JohnK.N. Would a view suffice or will PowerBI not have the same functionality if you do that?

@Vérace There aren't any bases to cover - the question isn't to solve anything too complicated (number of ships that were in a given waterway at a point in time or the maximum number of ships at one time), rather just how many passages occurred in a given interval, with the definition of passage being fairly straightforward. There are no gaps, no islands, just waterways and boats.

@bbaird I was contemplating using a view (SQL Server-side), but I'm trying to keep the database/Power BI data structure as close to the original as possible.

This is a role-playing dimension problem. The State table is a dimension that applies to all the entities. I'm trying to find a good single source to refer you to for handling it. Often times the table can just be aliased multiple time in the tool if there is a modeling component. Sometimes you alias it yourself in the DB with views.

Surprisingliy Kimball doesn't have a lot to say

kimballgroup.com/2011/04/…

This one shows a DAX-specific way to handle those for users: martinschoombee.com/2020/06/24/…

To belabor the point, your nicely normalized parent-child hierarchy with shared states, instead of succumbing to the temptation of using a snowflake model, becomes a single factless fact table capturing the relationships (redundantly as you would expect given it's denormalized) and separate aliases of the state dimension in its three different roles

What's the solution to the excel data type woes when importing to db via sql server import export wizard?

@MohammadYusuf saving it as a CSV and importing it as a flat file

:-/

I see. Will try that. Thanks.

Just worked with oracle db2 once, liked the feature of copy from excel and directly paste into table.

@MohammadYusuf sounds expensive 😬

In ERP implementation projects, the client provides messed up data in the excel templates we provide them. 90% of the time we have to clean it.

Just going through some data woes. The item model group was wrong on the items and few items got sold. And microsoft has locked the access to production database. To just run 1 query we have to move the production db to uat, then uat to development and then reverse process till production after correction on development. :(

@MohammadYusuf Have you looked at the Powershell Import-Excel cmdlet? I find it tremendously useful for doing all kinds of testing and cleansing and Excel work in Powershell without having to compile C# programs or deal with GUI tools like SSIS that can be difficult to change on the fly. Useful tool to check files for problems first without having to load it into SQL Server. github.com/dfinke/ImportExcel

It's easily installable via Powershell Gallery: powershellgallery.com/packages/ImportExcel

For read/write Excel files, it uses EPPlus which is the same DLL we always used to use from C# to put data in templatized Excel files for some BI dashboard exports at an old job.