Blog ala Vidar
SQL, AppFrame and other cool technologies
Tag Archives: SSAS
Nordnes.Me
Posted by on June 15, 2010
A couple of weeks ago I looked in the mirror. What did I see? I saw a guy that used most of his spare time on Xbox games. So, what did I do? Well, I had to find something else to do. What else? Build a new server! Yeah yeah, I could go outside having some fresh air etc., but fresh air also comes through the windows, at least if you open them. ANYHOW! I bought two domains; nordnes.me and nrdn.es and though I’d have some fun. A while back I bought a Quad Core AMD CPU for my HDPC, but I bought a new Intel CPU for that, so the AMD processor with the main board was just lying around without being used. I bought 8 GB RAM for it, and 2 x 2TB disks. I’ve also got a Microsoft TechNet subscription, which allows me to download almost any Microsoft product for free to use in a testing environment. What’s more testing environment than my home network? 😉
Now, everything is up and running and here are the specs.
Physical server
vs – Hyper-V Server
Virtual servers, all running with 1GB RAM (2-4 while setting them up):
vsAD – Active Directory, DNS and similar
vsHS – Home Server 2003 (not running. Will probably be deleted soon)
vsLINUX – Debian. Not running, but installed in case I need to test some Linux-stuff
vsMAIL – Exchange Server 2010 (hosting vidar@nordnes.me)
vsSQL – SQL Server, running 5 instances (2005, 2008, 2008R2, Express etc)
vsVAIL – Home Server VAIL
vsWEB – Web server, hosting Nordnes.me and nrdn.es
Home Server
I’ve learned a whole bunch of stuff in the process of installing and configuring everything. Today I finished moving over to the new home server (VAIL) which based on Windows Server 2008 R2. It’s awesome! One of the new features is that you can stream your videos via web (using Silverlight). Oh, while I’m talking about home server. If you’ve got more than one computer at home (or at the office), you might want to think about buying one. It makes sharing videos, music, backing up your computers and making sure all computers have updated antivirus etc. a whole lot easier. But wait till VAIL is released! VAIL will only run on x64 bit, while the old version only runs on x86.
One thing worth mentioning: DO NOT add your Home Server to AD. You will be able to do it, but it causes so many problems. The reason I wanted to do it was to use the same username and password as on all my other machines (both physical and virtual). Also, joining machines to the home server should be done on a wired network, not on wireless. When joined, you can put them back on wireless without any problems, but I haven’t been able to join any of my computers while on wireless. Have no clue why though, since my wireless router is set up as an AP (Access Point).
Active Directory
The main reason I installed AD is that I wanted to use the same username and password on all computers, but also to play around with group policies. Also, Exchange is pretty meaningless, if not impossible, without AD.
Web
This is the server I’ve played most with. I installed SharePoint, played with Office Web Apps and most recently Nordnes.me and nrdn.es. The last one (nrdn.es) is my own test-project for bit.ly/tinyurl.com-like sites. If you’re interested I can send you the project for this one, but there’s no rocket science here. I’ve also built Nordnes.me using Master pages in ASP.NET. This is actually my first site ever in ASP.NET!
Nordnes.me is just a place where I’ve gathered all my public feeds (blog, twitter etc) into one stream. I’m using a windows service to poll all the feeds every 15 minutes or something and then using LINQ to XML (which btw is AWESOME!) with ASP.NET to display them.
Exchange
Since Omega just moved over to Exchange, I felt I had to get some more knowledge about this. It would be interesting anyways since 99% of our customers use it, so I would probably eventually “have to” build something that communicated with Exchange. Before starting the installation I was convinced that this was just another “next next next next finish” project, and I was right. At least to get it up and running. To get it to send and receive mail took a couple of hours though. I am still not able to connect to Exchange outside my network although testexchangeconnectivity.com says everything is working as it should be. Since it’s working locally it’s very hard for me to test outside my network, so I might have to pick someone up from Microsoft and get them home to see my server-collection 😀
SQL
All SQL geeks with self-respect should have at least 5 instances running at home. I’m only using 2008 R2 at the moment, where I’ve got mainly two databases. One for the tiny URLs (nrdn.es) and one for polling twitter. The reason I’m polling twitter is to gather very much data, so I can start playing with SSAS (SQL Server Analysis Services). There are two arguments for polling twitter for this; it’s public and there’s A LOT of data being generated (by people) every second.
SQL Server Analysis Services
Posted by on April 15, 2010
Today a couple of us in Ølen had the pleasure of attending a session with Jan Ove Halvorsen with SSAS (SQL Server Analysis Services) as topic. This was an introduction to SSAS, although most of it was heavy stuff. I’ve played with SSAS before, but have had problems understanding some of the basics. This might help you on the way.
What is SSAS?
SSAS is part of the OLAP BI (Business Intelligence) part of SQL Server. It’s also a part of the SQL Server licensing. This means that if you own a SQL Server license, you also own a SSAS license. It is recommended to install it on a server beside SQL Server, because it’s heavy on RAM usage. To connect to it you either use Visual Studio, Management Studio or an API. You can also connect to it using ADOMD.NET.
SSAS Modes
There are three main modes in SSAS:
MOLAP – Multidimensional OLAP – Data, aggregations and structures are stored in OLAP
HOLAP – Hybrid OLAP – Mix of MOLAP and ROLAP
ROLAP – Relational OLAP – Uses data from SQL Server but stores some structural data in OLAP
This means that you don’t need to run non-live data. For instance you can run MOLAP on monthly updates with a SQL Server Agent job running every month (or similar), storing everything in SSAS. This makes it VERY fast to query. If you need more live data, you can run ROLAP, so you get live data all the time. This will not be as fast, and it will also give a load on the SQL Server each time a query is executed.
With the 2005 version there came a new feature to SSAS, enabling it to “subscribe” to updates on the SQL Server. So, if you’re running ROLAP, it will generate the cube, and just use that local cube till there’s updates in the underlying data (on the SQL Server). When there’s an update SSAS gets a notification that there are changes, and it will rebuild the cube. Either the whole cube, or just parts of it.
OLAP Cubes and Dimensions
In SSAS you’re browsing cubes. A cube is a “collection” of dimensions. A dimension is a set of data, in many cases a one to one relation to a table or similar in SQL Server. This might for example be “Products”, “Cities”, “Countries” or similar. You also must have a time dimension. This means that all your data are aggregated so that every possible join of these (Products, Cities, Countries and Time) are stored as one “table”. This is what’s making this so fast to query, because everything’s “prejoined”.
Hierarchy
You can create hierarchies in SSAS. The most common is the Year –> Month –> Day hierarchy. This enables you to group by year, and just by clicking expand, you’ll get group by month in your client without having to get new data from the server.
Creating cubes
There are several ways of creating cubes, dimensions and other stuff. You can either use Visual Studio, Management Studio, or you can use a query language. This can be done using MDX (MultiDimensional eXpressions) which looks a lot like Transact SQL, but is also VERY different. An example could be:
SELECT
{ [Measures].[Store Sales] } ON COLUMNS,
{ [Date].[2002], [Date].[2003] } ON ROWS
FROM Sales
WHERE ( [Store].[USA].[CA] )
In this example Sales is the name of the cube. Store is one of their dimensions.
Blog ala Vidar 