СОДЕРЖАНИЕ

1.	СОВЕТЫ
1.1.	Введение в SQL Server 2000 Analysis Services: Создание первого куба. Часть 3.
2.	ССЫЛКИ НА СТАТЬИ
2.1.	Новые и обновлённые технические статьи Microsoft
2.2.	Англоязычные статьи
3.	ФОРУМ SQL.RU
3.1.	Самые популярные темы недели
3.2.	Вопросы остались без ответа
4.	ПОЛЕЗНОСТИ
4.1.	Что такое "системные базы данных" и почему я должен о них заботиться?

СОВЕТЫ

Введение в SQL Server 2000 Analysis Services: Создание первого куба. Часть 3.

По материалам статьи William Pearson: Introduction to SQL Server 2000 Analysis Services: Creating Our First Cube

Создание измерения по схеме - снежинка: Несколько связанных таблиц измерений

Так как по ключу product_id таблица фактов связана с несколькими таблицами измерений товаров, содержащих поля, по которым определяются соответствующие уровни иерархии измерений, мы сталкиваемся с необходимостью рассмотрения более сложной схемы измерений - снежинки. За исключением описания объединений таблиц, и некоторых дополнительных шагов мастера, Dimension Wizard поможет нам также просто и эффективно создавать более сложные схемы измерений, чем рассмотренная ранее схема - звезда. Для того, что бы смоделировать схему измерений - снежинка, в окне списка измерений мастера Cube Wizard нажмите New Dimension (там, где мы остановились в предыдущем примере). Потом, в окне Welcome Dimension Wizard, нажмите кнопку Next и в открывшемся окне Choose How You Want to Create the Dimension выберете схему Snowflake Schema, как это показано ниже, на Иллюстрации 22. Ознакомьтесь с дополнительной информацией об этой схеме в расположенной внизу области Description, и опять нажмите кнопку Next.

Иллюстрация 22: Выбор схемы - снежинка

Нужно выбрать таблицы product и product_class, как это показано на Иллюстрации 23, а затем нажмите Next, чтобы перейти к диалоговому окну Create and Edit Joins, в котором Вы увидите выбранные нами и реально существующие отношения между таблицами.

Иллюстрация 23: Выбор product и product_class

Иллюстрация 24: Окно Create and Edit Joins, где можно просматривать и редактировать связи между таблицами

Объединение этих двух таблиц, которые мы выбрали, основано на связи по полю product_class_id, как видно на Иллюстрации 24. Соответствие типов данных и другие ограничения должны быть рассмотрены на этом шаге, иначе, Вы почти наверняка потом столкнётесь с проблемами, но в нашем простом случае, представленное на иллюстрации объединение вполне правомерно. Для получения подробной информации об удалении/создании новых объединений или о критериях построения правильных связей, обратитесь к разделу Analysis Services в MSSQL Server Reference Library. Нажмите кнопку Next, чтобы перейти к следующему шагу мастера. В следующем диалоговом окне Мы снова выбираем уровни для измерений, и если мы расположим уровни в не правильном порядке, будет выдано предупреждение. Как показано на Иллюстрация 25, Мы выбираем уровни от наиболее общих к более детальным, сверху вниз: product_category, product_subcategory и brand_name.

Иллюстрация 25

Указав иерархию уровней, нажмите кнопку Next, и потом пропустите не критичные для нас окна Specify Member Key Columns и Advanced Options.
Наконец, когда Вы попадёте в диалоговое окно Finish ... , введите имя Product в поле Dimension Name, оставим всё остальное в значениях по умолчанию, как это показано на Иллюстрации 26.

Иллюстрация 26: Завершение создания измерения Product

После нажатия кнопки Finish, Вы снова попадёте в диалоговое окно Cube Wizard (Иллюстрация 27), и мы видим, что теперь измерение Product также находиться в списке Cube Dimensions.

Иллюстрация 27: Измерение Product в списке существующих измерений

ПРОДОЛЖЕНИЕ СЛЕДУЕТ

[В начало]

ССЫЛКИ НА СТАТЬИ

Статьи на русском языке

Стратегия профессиональной защиты аналитических срезов данных
Владимир Иванов
Данная статья посвящена обзору того, как можно активно противодействовать инсайдерам и хакерам, защищая информацию на уровне аналитических срезов с помощью систем профессиональной аналитики. Материал особенно актуален для комплексов, в которых система профессиональной аналитики является интегрированным компонентом в поставке, как например в Microsoft Project Professional или Microsoft Navision Axapta. Также материал важен для корпоративных пользователей, которые имеют счастье или несчастье эксплуатировать 1С:Предприятие, которое не гарантирует защиту данных от инсайдеров даже на базовом уровне...
Модели хранения XML-данных: единственного варианта на все случаи нет
Марк Скардина
XML: Выбор модели хранения XML-данных для вашего приложения может определить его успех или неудачу...

[В начало]

Новые и обновлённые технические статьи Microsoft

An Office Web Components 10 PivotChart May Crash When You Open It
FIX: A scan of each partition table may be performed when you run an UPDATE statement on the partitioning column of a partitioned view
FIX: Cut-and-paste operations fail with an internal error in Analysis Manager in SQL Server 2000 Analysis Services
FIX: Incorrect Results Are Returned If a Slice Occurs on a Virtual Dimension in a Cube That Has Multiple Partitions
FIX: MDX queries to cubes where real-time updates are enabled may not reflect updates to the relational table
FIX: Merge Agent for a Filtered Publication Might Fail
FIX: New Functionality for Incremental Update of Changing Dimensions
FIX: Restoring a SQL Server 7.0 database backup in SQL Server 2000 Service Pack 2 (SP2) may cause an assertion error in the Xdes.cpp file
FIX: Synchronization fails for SQL Server CE merge replication
FIX: The Sqldumper.exe File Does Not Generate a Userdump File When It Runs Against a Windows Service
FIX: When using the Microsoft Decision Support Objects (DSO) library to perform a processReaggregate operation on a cube the SQL Server 2000 Analysis Services service may shutdown and then restart
FIX: You cannot exclude members of the computer Administrators group from the OLAP Administrators group
FIX: You may receive a 644 error message when you run concurrent transactions on a heap
How to add an article to an existing snapshot or to a transactional publication by using system stored procedures
How to manually activate a report server instance by using Rsactivate
How to upgrade an Internet Information Services server after applying SQL Server 2000 Service Pack 3 or SQL Server 2000 Service Pack SP3a
How to use XML in connected and disconnected ADO.NET applications in Visual C++ .NET 2003
HOW TO: Synchronize Mobile Databases by Using SQL Server CE Relay
INF: SQL Server 2000 64-Bit Readme.htm Additions
INF: SQL Server 2000 Evaluation Edition Release A
MDX Query Results May Not Be Correct If Dimension Security Is Defined on a Dimension
MS03-031:Security Patch for SQL Server 2000 Service Pack 3
PRB: Error Message: "Setup was unable to validate the logged user..." Occurs When You Install SQL Server 2000 Service Pack 3 (SP3)
SQL Server Cluster Supportability with CMS 2001
Transactional replication for text or for image data may not work with DB-Library applications that use the dbtxptr function in SQL Server 7.0 Service Pack 2 or in SQL Server 2000
When you run a program or a Web browser script that uses the Command object in the ADO programming interface to run a SQL Server stored procedure, you may receive an "EXECUTE permission denied on object..." error message in SQL Server 2000
You receive query results that were not expected when you use both ANSI joins and non-ANSI joins

[В начало]

Англоязычные статьи

Reporting Services!
Mike Pearson
When word of SQL Server Reporting Services (SQLRS) first got out - it caused a huge amount of 'movement' in the reporting industry. I have seen reactions to it that range from borderline hysterical to "ho-hum version 1 stuff". So just how good is it really, and once all the marketing hype has been pushed aside, just how good is it really?
MDX Essentials: Basic Numeric Functions: The Count() Function
William Pearson
Microsoft Analysis Services ("MSAS"), as most of us know, leads the enterprise business intelligence arena with its rich set of mathematical and analytical tools. Most of these tools rely upon functions based in the MDX query language. MDX is integrated not only within MSAS, but also throughout the entire Microsoft Business Intelligence Platform in applications that include Data Mining and Reporting Services. This integration provides a distinct advantage for the platform over the offerings of other vendors, and, particularly in the case of numerical functions, allows for easy, consistent application of built-in logic.
MS SQL Server Distributed Partitioned Views
Don Schlichting
This article will explore the use of Distributed Partitioned Views for accessing multiple MS SQL Severs when configured as a Federated Database
"File Watcher" for SQL Server
Muthusamy Anantha Kumar
SQL Server Database administrators often come across situations where the scheduled DTS jobs failed because the Source File didn't arrive on time. It is a tedious process to cleanup if there are dependent jobs that have run before the first DTS job completed successfully. The intent of this article is to provide a solution for that kind of situation
Microsoft SQL Server 2000 command line utilities (Part 2)
Alexzander Nepomnjashiy
This article is a continuation of "Microsoft SQL Server 2000 command line utilities (Part 1)," which began our discussion of how to use the Microsoft SQL Server 2000 command line utilities, and what are they used for. . Most of these utilities are primarily used for diagnostic, configuration and data migration purposes, rather than for database server administration
Introduction to Database Replication
Baya Pavliashvili
Replication involves much more than setup, just as marriage involves much more than a honeymoon. And there aren't many sources of information for replication implementation and troubleshooting. In this first in a series on replication, Baya Pavliashvili fills in some of the gaps for you by detailing replication types and giving you a general overview
A DTS Data Store
Bruce Szabo
Data Transformation Services (DTS) is extremely useful in solving a number of problems.In some cases I have used it to solve data problems and in other cases I have used it to solve some tasks that have nothing to do with SQL. One difficulty I found with DTS was managing the different packages that exist on servers. For example, I may have packages A, B and C on Server 1 and packages D, E and F on Server 2, although the packages can be made generic and abstracted so they use common data sets too often I find subtle differences between packages on different servers. For this reason it is nice to have a backup of each package from each server. As you should know backing up packages is important and can be a relatively trivial task but to gather up a group of packages from a variety of servers and store them in one location can be more involved
Rolling Back a Transaction in the Transaction Log File
SQL Server Development Team
This tip is one of a series from SQL Server Magazine. For additional tips, visit the SQL Server Tips and Tricks Center
Showdown-bcp vs. DTS
John Green
Every SQL Server application manager needs to move a large amount of data into or out of a SQL Server database at least once, so SQL Server has several tools for bulk data-transfer operations. In this article, I compare two familiar SQL Server utility programs—bulk copy program (bcp) and Data Transformation Services (DTS)—in a common scenario: moving data to and from an ASCII text file. I compare the performance and capabilities of these utilities to the T-SQL BULK INSERT statement, which, according to Microsoft, is the fastest bulk copy method (see "Data Import/Export Architecture" in SQL Server 2000 Books Online — BOL — for more information)
Opinion: Real Intelligence, or Paralysis by Analysis?
Joe McKendrick
11/5/03 — Business intelligence and analytics used to be a closed process in which a small room of analysts loaded sales figures into proprietary software or onto spreadsheets to generate reports for corporate decision makers. Now, three trends are sweeping away this process, but threaten to make the IT director’s job untenable
An Evaluation of Stored Procedures for the .NET Developer
Christa Carpentiere
Get an introduction to Microsoft SQL Server stored procedures for developers who work primarily in the .NET programming languages. Discover the pros and cons of using stored procedures; get an overview of the tools that Microsoft Visual Studio .NET 2003 provides to help you work with stored procedures; and learn a handful of best practices to help you get started. (7 printed pages)
Microsoft SQL Server 2000: Replication troubleshooting
Alexzander Nepomnjashiy
First and foremost when troubleshooting replication, make sure you are running the latest service pack available for SQL Server. Service packs often include replication bug fixes
Worst Practice - Not Qualifying Objects With The Owner
Christoffer Hedgate
When Andy recently added his latest piece in the Worst Practices series, about adding a column without thinking, it made me think about a worst practice I have been trying to combat for a long time. Maybe not as specific as most other worst practices, but one of the worst practices I often encounter is plain and simple laziness. One mistake that people often do just because they are lazy is to not qualify the names of objects with owner name. I will discuss this here, and in a later article I will discuss some other mistakes caused by laziness
Билл Гейтс об усовершенствованиях в области безопасности
Выступая на конференции RSA Conference на прошлой неделе, председатель правления Билл Гейтс (Bill Gates) описал усовершенствования в области безопасности, реализуемые корпорацией Майкрософт, и в очередной раз подчеркнул приоритетное значение безопасности для нашей компании. Корпорация Майкрософт поможет вам обеспечить безопасность вычислительных систем на предприятиях ваших заказчиков. Прочитайте запись презентации Билла Гейтса и воспользуйтесь ресурсами для партнеров, предлагаемыми в рамках программы Outreach
Using SQL Server's CHARINDEX and PATINDEX
Gregory A. Larsen
If you have written many applications then you probably have run across situations where you need to identify if a specific character or set of characters appears in a string. In this article I will discuss using the CHARINDEX and PATINDEX functions to search text columns and character strings. I will show you how each of these functions operate, and explain the differences between them. Also provided are some examples on how you might consider using these functions to solve a number of different character search situations
Inside the SQLXML Virtual Directory Structure
Drew Minkin
Introduces the SQLXML tier, a combination of XML-based communication to Microsoft SQL Server through an Internet Information Services (IIS) virtual directory. (14 printed pages)
Creating User-Defined Data Types in Yukon
Rod Paddock
One key feature of CLR support is the ability to create User Defined Data Types (UDT). This article demonstrates how to create your own UDT using Yukon and the Whidbey-release of Visual Studio .NET
Yukon Delayed Again and Named
Brian Knight
As you may've already suspected, Microsoft confirmed today that the next release of SQL Server, code named "Yukon", has been delayed yet again into 2005. The previous announcement had Yukon being released the later part of 2004. With Beta 2 still be needing to be released before then, the math didn't quite add up though for a 2004 GA release. Also announced today was that Yukon will officially be called SQL Server 2005 upon its release. The next release of Visual Studio, code named Whidbey, will also have the 2005 branding with Visual Studio 2005
Review of ApexSQL Report v4.3
Mike Pearson
ApexSQL Report is designed for flexibility and fast turnaround of reporting in a dynamic Rapid Application Development environment. The current version (v4.3) is ASP based, with v5 coming out soon in ASP.NET
Home-grown Data Mining in SQL Server
Drew Georgopulos
You need certain data values to complete a database-programming job, but you don't know where they're located. What can you do? Follow this 10-Minute Solution to navigate through an unfamiliar database, and locate and extract the table and column names that house your values. As a database programmer, when you don't know where the tables and columns you need to do your job are, you're dead in the water. You can browse through Enterprise Manager looking for tables whose columns are likely candidates, but columns often are tersely named in a less-than-intuitive manner, making the search for columns (either join columns for foreign keys or code columns for descriptive data) difficult and error prone
Scripted Configuration for SQL Server 2000
Rob Hawthorne
SQL Server's limited installation and configuration options force database developers to take on a lot of server reconfiguration work. Use scripts to quickly configure not just one but multiple servers, and simplify your SQL Server configuration process
AWE Adventures
Joseph Sack
Recently, 4GB of physical RAM was added to a SQL Server 2000 Enterprise edition instance I support. This brought the total physical RAM available on the machine up to 8GB. By using Windows 2000 Address Windowing Extensions (AWE), with SQL Server 2000 Enterprise or Developer Edition, on Windows 2000 Advanced Server or Windows 2000 Data Center, SQL Server can take advantage of physical memory exceeding 4GB of physical RAM
What You Don't Know
Andy Warren
Sometimes it's what you don't know that hurts. Sometimes it hurts a lot! Recently one of our developers was just about to pound his head on the desk in frustration, trying to figure out why some TSQL was not working correctly. It looked something like this
Enhanced SQL Server Security Auditing
Muthusamy Anantha Kumar
Security audit in SQL server captures only successful and failed logins. It does not capture the application that uses the login. Login used by an application may have full read and write access on all of the tables and procedures but the application restricts the users by providing a front-end which will allow them to see only a few columns, tables etc. However, certain users out of curiosity may log on to the database using SQL Query tools such as Enterprise manager and Query analyzer, using production login information. The following process will capture such un-authorized users who log on to the SQL server

[В начало]

ФОРУМ SQL.RU

Самые популярные темы недели

Ваше мнение об упражнениях SELECT на http://sql.ipps.ru
Кто на чем пишет клиентов под SQL Server?
Новость No.2 - FTP SQL.RU
Нынче по небу правильно солнце идет
Железо для SQL Server
Таблицы без identity
Если LEFT JOIN - то тормоза...
Advanced Transact-Sql For Sql Server 2000 [Itzik Ben-Gan]
Отлов select
Переход с SQL 7.0 на SQL 2000. Проблемы вып. хран. процедур
Запрос на UPDATE
После обновления статистики запрос стал тормозить
Производительность SQL сервера
Странная потеря данных. Как избежать вновь?
Меньшая производительность MS SQL по сравнению с PostgresSQL
sql и 1С
Вопрос про триггер INSERT UPDATE
Как в MS SQL Server 2000 просмотреть журнал транзакций базы .ldf
Помогите возникла проблема
CREATE TABLE в хранимой процедуре

[В начало]

Вопросы остались без ответа

Хоть и не в ТЕМУ - но ХХХЕЕЛЛЛППП!!!
Данные из Excel
Отладка болльших скриптов со вложенной логикой...Делюсь опытом
как создать такой запрос
Увеличение размера журнала транзакций
Lumigent LogExplorer for SQL Server 4.0.2
Виснет Enterprise Manager
MSSQL2000&ExtendedSP&Table UDF
Как Merge Agent вызывает команду BULK(BCP)?

[В начало]

ПОЛЕЗНОСТИ

Рассылка:  Вопросы и ответы по Microsoft SQL Server

Автор рассылки: Сергей Кошкин

Выпуск No. 30 от 2004-03-12
Вопрос : Что такое "системные базы данных" и почему я должен о них заботиться?
Ответ

[В начало]