March 19, 2012 at 9:35 am
Hi All
I've been teaching myself SSAS recently and hit a snag. I've got two tabes in the database, each of which holds a date held in a varchar (not a date or datetime field). Worse, they hold them in slightly different formats. One is dd mm yyyy, the other is dd-mm-yyyy.
Now, I have control over this database so I can change them to proper date fields. Great. But this isn't exactly the first time I've come across this sort of thing in a database and, often, it's out of my control to change it. In the past I've come across similar situations where e.g. part numbers are encoded to contain data in known character positions (e.g. the product catalogue reference is actually character 5 & 6 of the part number - that sort of thing).
So my question is, when I come up against a situation like that and it's outside my control to change the DB, how can I define the relationship so that SSAS can use it? In SQL I can just use Convert, SubString etc, to get at the bit's I want in the join but the SSAS interface doesn't allow me to define my relationships in SQL terms. All I can do is drag and drop so, if it's an exact match, I can't find a way to define it.
March 19, 2012 at 11:49 am
You can do the same SQL CAST/CONVERT to the desired datetime in the DSV, but instead of using a table or view, a SQL Query.
In the DSV, right Click, "New Named Query".
This is just an option, and not necessarily the best or most efficient.
My first suggestion would be to get the formatting correct in the Source, but as you mentioned, it's not possible.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
March 19, 2012 at 2:01 pm
Ah, didn't realise you could create dsv entities from queries. That'll do it for me in a pinch. Thanks.
Is there a way I can just affect the join though? That just feels more comfortable to me. If not then doing it from a query will do just fine.
March 20, 2012 at 12:29 am
FunkyDexter (3/19/2012)
Ah, didn't realise you could create dsv entities from queries. That'll do it for me in a pinch. Thanks.Is there a way I can just affect the join though? That just feels more comfortable to me. If not then doing it from a query will do just fine.
Nope, the datatypes need to match. Modifying it with TSQL is your only option. (besides slapping the developers for such a bad design :-D)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 20, 2012 at 3:03 am
Ooh, just tried it and, actually, I think I prefer it to creating elements direct from the tables. This would give me much more control, save me loading up data I don't need etc. Thanks.
Like I said, here I can make changes so it's not so bad and I can forgive the original devs, this DB was only ever meant to be a rough "play area". I've seen just as bad practice in off the shelf product before, though, and then the dev's need slapping till they look like this: :hehe: (or possibly this :blush:)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply