Solution 4: Add a Type Column
In Itzik Ben-Gan's March 2003 column, "Calculating Concurrent Sessions" (InstantDoc ID 37636), Solution 4's ORDER BY clause needs to include a type column so that a session that starts in the same minute as another session ends can be counted as concurrent with that session.
—Kate Luxemburg
c-kluxembu@state.pa.us
You're right. You should add the type column as the last sort column in the query for Solution 4 (Listing 8):
SELECT app, starttime AS ts,
1 AS type
FROM sessions
UNION ALL
SELECT app, endtime, -1
FROM sessions
ORDER BY app, ts, type
<ASC | DESC>
Whether the type order should be ascending or descending depends on how you want to treat sessions that start and end at the same time. If you want to treat such sessions as concurrent, the type order should be descending, causing positives to come first But you should definitely avoid my original implementation, without the type column in the ORDER BY clause, because it isn't deterministic.
—Itzik Ben-Gan
Another Way to Track Uptime
I saw Brian Moran's SQL Server Savvy tip "Tracking Uptime" (April 2003, InstantDoc ID 38042) and hadn't thought about looking at SPID=1 to determine how long SQL Server has been running. As an alternative solution, here's the script I use on my servers to see how long the SQL Server service has been running:
SELECT DATEDIFF(hh, crdate, getdate()) AS UpTimeHours
FROM master.dbo.sysdatabases
WHERE name = 'tempdb'
By the way, great magazine!
—Chuck Wilson
cwilson@tmhsi.com
Thanks for sharing your tip! Both solutions show that it's worthwhile to understand how SQL Server works internally when you're trying to solve a problem. In this case, Microsoft doesn't provide a direct system function to tell us how long SQL Server has been running. My solution works because SPID 1 is created when SQL Server starts up, and we can trust the login_time column to give us an accurate idea of when SQL Server started. Your solution works because SQL Server recreates tempdb each time the server is stopped and started. Both solutions require knowledge of how to query system tables to retrieve information that's not otherwise available.
—Brian Moran
Drilling into Dimension-Level Security
I've read Russ Whitney's columns about dimension-level security ("Security and Parameterization," December 2002, InstantDoc ID 27040, and "Customizing Dimension Security," January 2003, InstantDoc ID 27305) and have a question. We have a cube that consists of three parent-childlevel dimensions and a Time dimension. One of these dimensions, User, contains a parental relationship, UserID to ParentUserID, where a null ParentUserID denotes the root node of the hierarchy. The cube's fact table contains information such as CostPerMonth of an application service (e.g., cost per month per user for Microsoft Word).
The cube design works well for reporting, showing level-decomposition based on the parent-child relationships. But how can we secure the cube so that users can access only the part of the hierarchy that pertains to them? For example, if John is at Level 3, his manager, Joe, is at Level 2, and Joe's manager, BigBoss, is at Level 1, John should be able to view only the facts that pertain to him and the employees he manages. Any guidance?
—Pat Martin
patmar@microsoft.com
The scheme I outlined in the January column should work with one exception: It won't help you limit access to the higher levels of the dimensions. In your example, you want John to see his facts and his employees' facts but not his managers' facts. The only solution to this part of the problem that I'm familiar with is to create a calculated measure for each normal (loaded) measure in the cube that you want to secure. Use a formula in the calculated measure that determines whether the currently selected member (in the organization dimension) is at a level you want to secure and, if so, returns null or zero; otherwise, it should return the value from the loaded measure. You can use Analysis Manager to hide the loaded measures so that users can access only the calculated measures.
—Russ Whitney
CLARIFICATION
XP SP1 Bug Affects Poormon Solution
Regarding the monitoring solution in "Introducing Poormon" (February 2003, InstantDoc ID 37468), a bug in Windows XP Service Pack 1a (SP1a) causes the CounterDetails table data to be duplicated whenever a System Monitor log that's logging to SQL Server is stopped and started. The bug isn't present if you run the solution on Windows XP without SP1 applied. We apologize for any inconvenience and have informed Microsoft of this problem.
End of Article
Prev. page
1
[2]
next page -->
