Posts tagged MS SQL Server
This is a little issue that bugged me…. instead of going in the Visual Studio project and processing dimensions in there, I wanted to be able to multi-select my dimensions and reprocess them.
But, multi-select is not something that you can do in SQL Management studio.
So… here’s the (hidden trick)….
- Select the dimension
- Hit F7
- And then you have “Object Explorer Details” open, where you can do a multi-select.
You can also find “Object Explorer Details” in the “View” menu when you select the dimension.
My scenario was this:
Pulling in several million rows of data from a DB2 database system into an Enterprise 2012 SQL Server Analysis Services setup. This data was basically tranasction records keyed on several fields, like Location, job numbers…etc. And this data was from a main system that was not developped or maintained by the devs I work with.
But in the data’s structure there was an element that wasn’t completely rock solid. That was trying to get the most recent transaction of it’s type based on a max date, max time (on the given date) and also a max sequence number.
Problem was on occasion there was no sequence number and even no time records… really making it impossible to accurately get the desired results. This transactional data is used, from what I can tell, as a log that is compiled by a few different “sub programs” or modules within the main application.
This data was accessible through the legacy system, an ancient green screen program, which still worked quite well but was no longer being used. However, in this program when viewing the data you could see record level keys… but these were not accessible by query field.
These were loaded in correct sequence and give the desired results (in my case) with the proper criteria.
We didn’t think, at first, that it was possible to get these record numbers which wouldn’t just fix the current problem but also simplify our update scripts.
After a little research (thanks Google!) I found that it was so very easy.
I found DB2’s RRN() function. All I had to do was pass the table name to the function in the query and the RRN function would return this Relative Record Number.
SELECT RRN(users) AS rID,FirstName,LastName
FETCH FIRST 10 ROWS ONLY
Or using an alias on the table name
SELECT RRN(u) AS rID,FirstName,LastName
FROM users AS u
FETCH FIRST 10 ROWS ONLY
This query would return 10 records (SQL server TOP 10 equivalent) with rID as the Relative Record Number.
Thought I’d add this for future reference.
Working with a column that was defined as a varchar in a database, but it was intended the contain integer values. Whatever the original purpose for this I’m not sure as I’m not the database’s author.
The program that is used to enter data also allows the input of non-numeric characters.
So, in my scenario, this could be a problem when joining onto that column in a query.
Using ISNUMERIC() in this way eliminated all non-numeric data for me.
ISNUMERIC(dbColumn + ‘.0e0’) = 1
I’ve been using Reporting Services a lot. As with many things I learn on a “needs” basis.
Something I’ve done quite a bit of is have to run counts on the data returned by datasets. Something that stumped me early on when I started was how to run a count against data that was returned based on dates.
It was actually nice and simple:
=SUM(IIF(Fields!DateField.Value = Today(),1,0))
In this example what we’re basically doing is checking if the date is equal to “today”, if it is it’s given a value of 1, if not then 0. And of course, it’s summed up. So this would give us the SUM() of dates equal to today.
A variation of this conditional counting (and something I’ve had to do) is to run a comparison like this against a date and a “non date” value.
So in this example our Fields!DateField.Value is actually stored as an integer. So we need to convert the integer to a date to have it count properly.
=SUM(IIF(new DateTime(Fields!WOODDT.Value / 10000, (Fields!WOODDT.Value / 100) mod 100, Fields!WOODDT.Value mod 100) = Today(),1,0))
So in the above example, we are creating a datetime type with our DateTime() function and then comparing it to the Today() value. If the newly created date value (which is again based off of the data returned from the dataset) is equal to today’s date it is given the value of 1, if not then 0. And this is summed up.