МошаПоскольку Вы маскируете даже имя своего сервера, то наверное Вы не захотите выкладывать все это на форуме :)
МошаЯ предложил вынести это в оффлайн, потому что я хочу Вас попросить послать мне:
1. abf бэкап куба
Странная логика. Если я не намерен показывать кубы никому на форуме, то с какой стати Вы решили, что для Вас будет сделано исключение? Кубы действительно содержат коммерческую информацию, а имя сервера я замазал, потому что в нем содержится название организации. Все эти сведения не имеют ровным счетом никакого отношения к обсуждаемой проблеме. Вы можете легко воспроизвести ее сами на любых данных.
-идете на MSDN и скачиваете SQL Server 2005 RTM (билд 1399)
-ставите
-открываете SQL Mgmt Studio, New Database Engine Query и пишете:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
use tempdb
drop table ProductCategories, Products, Departments, Employees, Time, Sales
create table ProductCategories(ID int identity, Name nvarchar( 50 ))
insert ProductCategories (Name) values ('Category 1')
insert ProductCategories (Name) values ('Category 2')
insert ProductCategories (Name) values ('Category 3')
create table Products (ID int identity, Name nvarchar( 50 ), CategoryID int)
insert Products (Name, CategoryID) values ('Product 1', 1 )
insert Products (Name, CategoryID) values ('Product 2', 1 )
insert Products (Name, CategoryID) values ('Product 3', 1 )
insert Products (Name, CategoryID) values ('Product 4', 2 )
insert Products (Name, CategoryID) values ('Product 5', 2 )
insert Products (Name, CategoryID) values ('Product 6', 3 )
create table Departments(ID int identity, Name nvarchar( 50 ))
insert Departments (Name) values ('Department 1')
insert Departments (Name) values ('Department 2')
create table Employees (ID int identity, Name nvarchar( 50 ), DepartmentID int)
insert Employees (Name, DepartmentID) values ('Employee 1', 1 )
insert Employees (Name, DepartmentID) values ('Employee 2', 1 )
insert Employees (Name, DepartmentID) values ('Employee 3', 2 )
insert Employees (Name, DepartmentID) values ('Employee 4', 2 )
insert Employees (Name, DepartmentID) values ('Employee 5', 2 )
create table Time (ID int identity, Name smalldatetime)
declare @d smalldatetime
set @d = '2005-01-01'
set nocount on
while @d <= '2005-12-31' begin insert Time (Name) values (@d); set @d = @d + 1 end
set nocount off
create table Sales (TimeID int, ProductID int, EmployeeID int, Fact smallmoney)
insert Sales select t.ID, p.ID, e.ID, rand(t.ID * 10000 + p.ID * 10 + e.ID) * 1000 from Time t, Products p, Employees e
-выполняете
-открываете New Analysis Services XMLA Query и пишете:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.
254.
255.
256.
257.
258.
259.
260.
261.
262.
263.
264.
265.
266.
267.
268.
269.
270.
271.
272.
273.
274.
275.
276.
277.
278.
279.
280.
281.
282.
283.
284.
285.
286.
287.
288.
289.
290.
291.
292.
293.
294.
295.
296.
297.
298.
299.
300.
301.
302.
303.
304.
305.
306.
307.
308.
309.
310.
311.
312.
313.
314.
315.
316.
317.
318.
319.
320.
321.
322.
323.
324.
325.
326.
327.
328.
329.
330.
331.
332.
333.
334.
335.
336.
337.
338.
339.
340.
341.
342.
343.
344.
345.
346.
347.
348.
349.
350.
351.
352.
353.
354.
355.
356.
357.
358.
359.
360.
361.
362.
363.
364.
365.
366.
367.
368.
369.
370.
371.
372.
373.
374.
375.
376.
377.
378.
379.
380.
381.
382.
383.
384.
385.
386.
387.
388.
389.
390.
391.
392.
393.
394.
395.
396.
397.
398.
399.
400.
401.
402.
403.
404.
405.
406.
407.
408.
409.
410.
411.
412.
413.
414.
415.
416.
417.
418.
419.
420.
421.
422.
423.
424.
425.
426.
427.
428.
429.
430.
431.
432.
433.
434.
435.
436.
437.
438.
439.
440.
441.
442.
443.
444.
445.
446.
447.
448.
449.
450.
451.
452.
453.
454.
455.
456.
457.
458.
459.
460.
461.
462.
463.
464.
465.
466.
467.
468.
469.
470.
471.
472.
473.
474.
475.
476.
477.
478.
479.
480.
481.
482.
483.
484.
485.
486.
487.
488.
489.
490.
491.
492.
493.
494.
495.
496.
497.
498.
499.
500.
501.
502.
503.
504.
505.
506.
507.
508.
509.
510.
511.
512.
513.
514.
515.
516.
517.
518.
519.
520.
521.
522.
523.
524.
525.
526.
527.
528.
529.
530.
531.
532.
533.
534.
535.
536.
537.
538.
539.
540.
541.
542.
543.
544.
545.
546.
547.
548.
549.
550.
551.
552.
553.
554.
555.
556.
557.
558.
559.
560.
561.
562.
563.
564.
565.
566.
567.
568.
569.
570.
571.
572.
573.
574.
575.
576.
577.
578.
579.
580.
581.
582.
583.
584.
585.
586.
587.
588.
589.
590.
591.
592.
593.
594.
595.
596.
597.
598.
599.
600.
601.
602.
603.
604.
605.
606.
607.
608.
609.
610.
611.
612.
613.
614.
615.
616.
617.
618.
619.
620.
621.
622.
623.
624.
625.
626.
627.
628.
629.
630.
631.
632.
633.
634.
635.
636.
637.
638.
639.
640.
641.
642.
643.
644.
645.
646.
647.
648.
649.
650.
651.
652.
653.
654.
655.
656.
657.
658.
659.
660.
661.
662.
663.
664.
665.
666.
667.
668.
669.
670.
671.
672.
673.
674.
675.
676.
677.
678.
679.
680.
681.
682.
683.
684.
685.
686.
687.
688.
689.
690.
691.
692.
693.
694.
695.
696.
697.
698.
699.
700.
701.
702.
703.
704.
705.
706.
707.
708.
709.
710.
711.
712.
713.
714.
715.
716.
717.
718.
719.
720.
721.
722.
723.
724.
725.
726.
727.
728.
729.
730.
731.
732.
733.
734.
735.
736.
737.
738.
739.
740.
741.
742.
743.
744.
745.
746.
747.
748.
749.
750.
751.
752.
753.
754.
755.
756.
757.
758.
759.
760.
761.
762.
763.
764.
765.
766.
767.
768.
769.
770.
771.
772.
773.
774.
775.
776.
777.
<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ObjectDefinition>
<Database xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<ID>DbForOfflineCubeTest</ID>
<Name>DbForOfflineCubeTest</Name>
<Language> 1033 </Language>
<Collation>Cyrillic_General_CI_AS</Collation>
<DataSourceImpersonationInfo>
<ImpersonationMode>ImpersonateServiceAccount</ImpersonationMode>
</DataSourceImpersonationInfo>
<Dimensions>
<Dimension>
<ID>Products</ID>
<Name>Products</Name>
<Source xsi:type="DataSourceViewBinding">
<DataSourceViewID>Tempdb</DataSourceViewID>
</Source>
<UnknownMember>Visible</UnknownMember>
<Language> 1033 </Language>
<Collation>Cyrillic_General_CI_AS</Collation>
<UnknownMemberName>Unknown</UnknownMemberName>
<Attributes>
<Attribute>
<ID>Products</ID>
<Name>Product</Name>
<Usage>Key</Usage>
<EstimatedCount> 6 </EstimatedCount>
<KeyColumns>
<KeyColumn>
<NullProcessing>UnknownMember</NullProcessing>
<DataType>Integer</DataType>
<Source xsi:type="ColumnBinding">
<TableID>dbo_Products</TableID>
<ColumnID>ID</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<NameColumn>
<DataType>WChar</DataType>
<DataSize> 50 </DataSize>
<Source xsi:type="ColumnBinding">
<TableID>dbo_Products</TableID>
<ColumnID>Name</ColumnID>
</Source>
</NameColumn>
<AttributeRelationships>
<AttributeRelationship>
<AttributeID>Product Categories</AttributeID>
<RelationshipType>Rigid</RelationshipType>
<Name>Category</Name>
</AttributeRelationship>
</AttributeRelationships>
<OrderBy>Key</OrderBy>
<AttributeHierarchyVisible>false</AttributeHierarchyVisible>
<InstanceSelection>DropDown</InstanceSelection>
</Attribute>
<Attribute>
<ID>Product Categories</ID>
<Name>Category</Name>
<EstimatedCount> 3 </EstimatedCount>
<KeyColumns>
<KeyColumn>
<NullProcessing>UnknownMember</NullProcessing>
<DataType>Integer</DataType>
<Source xsi:type="ColumnBinding">
<TableID>dbo_ProductCategories</TableID>
<ColumnID>ID</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<NameColumn>
<DataType>WChar</DataType>
<DataSize> 50 </DataSize>
<Source xsi:type="ColumnBinding">
<TableID>dbo_ProductCategories</TableID>
<ColumnID>Name</ColumnID>
</Source>
</NameColumn>
<OrderBy>Key</OrderBy>
<AttributeHierarchyVisible>false</AttributeHierarchyVisible>
<InstanceSelection>DropDown</InstanceSelection>
</Attribute>
</Attributes>
<Hierarchies>
<Hierarchy>
<ID>Product Categories - Products</ID>
<Name>Product Categories - Products</Name>
<Levels>
<Level>
<ID>Product Categories</ID>
<Name>Product Categories</Name>
<SourceAttributeID>Product Categories</SourceAttributeID>
</Level>
<Level>
<ID>Products</ID>
<Name>Products</Name>
<SourceAttributeID>Products</SourceAttributeID>
</Level>
</Levels>
</Hierarchy>
</Hierarchies>
<ProactiveCaching>
<SilenceInterval>-PT1S</SilenceInterval>
<Latency>-PT1S</Latency>
<SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
<ForceRebuildInterval>-PT1S</ForceRebuildInterval>
<Source xsi:type="ProactiveCachingInheritedBinding" />
</ProactiveCaching>
</Dimension>
<Dimension>
<ID>Employees</ID>
<Name>Employees</Name>
<Source xsi:type="DataSourceViewBinding">
<DataSourceViewID>Tempdb</DataSourceViewID>
</Source>
<UnknownMember>Visible</UnknownMember>
<Language> 1033 </Language>
<Collation>Cyrillic_General_CI_AS</Collation>
<UnknownMemberName>Unknown</UnknownMemberName>
<Attributes>
<Attribute>
<ID>Employees</ID>
<Name>Employee</Name>
<Usage>Key</Usage>
<EstimatedCount> 5 </EstimatedCount>
<KeyColumns>
<KeyColumn>
<NullProcessing>UnknownMember</NullProcessing>
<DataType>Integer</DataType>
<Source xsi:type="ColumnBinding">
<TableID>dbo_Employees</TableID>
<ColumnID>ID</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<NameColumn>
<DataType>WChar</DataType>
<DataSize> 50 </DataSize>
<Source xsi:type="ColumnBinding">
<TableID>dbo_Employees</TableID>
<ColumnID>Name</ColumnID>
</Source>
</NameColumn>
<AttributeRelationships>
<AttributeRelationship>
<AttributeID>Departments</AttributeID>
<Name>Department</Name>
</AttributeRelationship>
</AttributeRelationships>
<OrderBy>Key</OrderBy>
<AttributeHierarchyVisible>false</AttributeHierarchyVisible>
<InstanceSelection>DropDown</InstanceSelection>
</Attribute>
<Attribute>
<ID>Departments</ID>
<Name>Department</Name>
<EstimatedCount> 2 </EstimatedCount>
<KeyColumns>
<KeyColumn>
<NullProcessing>UnknownMember</NullProcessing>
<DataType>Integer</DataType>
<Source xsi:type="ColumnBinding">
<TableID>dbo_Departments</TableID>
<ColumnID>ID</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<NameColumn>
<DataType>WChar</DataType>
<DataSize> 50 </DataSize>
<Source xsi:type="ColumnBinding">
<TableID>dbo_Departments</TableID>
<ColumnID>Name</ColumnID>
</Source>
</NameColumn>
<OrderBy>Key</OrderBy>
<AttributeHierarchyVisible>false</AttributeHierarchyVisible>
<InstanceSelection>DropDown</InstanceSelection>
</Attribute>
</Attributes>
<Hierarchies>
<Hierarchy>
<ID>Departments - Employees</ID>
<Name>Departments - Employees</Name>
<Levels>
<Level>
<ID>Departments</ID>
<Name>Departments</Name>
<SourceAttributeID>Departments</SourceAttributeID>
</Level>
<Level>
<ID>Employees</ID>
<Name>Employees</Name>
<SourceAttributeID>Employees</SourceAttributeID>
</Level>
</Levels>
</Hierarchy>
</Hierarchies>
<ProactiveCaching>
<SilenceInterval>-PT1S</SilenceInterval>
<Latency>-PT1S</Latency>
<SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
<ForceRebuildInterval>-PT1S</ForceRebuildInterval>
<Source xsi:type="ProactiveCachingInheritedBinding" />
</ProactiveCaching>
</Dimension>
<Dimension>
<ID>Time</ID>
<Name>Time</Name>
<Source xsi:type="DataSourceViewBinding">
<DataSourceViewID>Tempdb</DataSourceViewID>
</Source>
<Type>Time</Type>
<Language> 1033 </Language>
<Collation>Cyrillic_General_CI_AS</Collation>
<UnknownMemberName>Unknown</UnknownMemberName>
<Attributes>
<Attribute>
<ID>Year</ID>
<Name>Year</Name>
<Type>Years</Type>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>- 1 </DataSize>
<Source xsi:type="ColumnBinding">
<TableID>dbo_Time</TableID>
<ColumnID>Year</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<NameColumn>
<DataType>WChar</DataType>
<DataSize>- 1 </DataSize>
<Source xsi:type="ColumnBinding">
<TableID>dbo_Time</TableID>
<ColumnID>Year</ColumnID>
</Source>
</NameColumn>
<OrderBy>Key</OrderBy>
</Attribute>
<Attribute>
<ID>Quarter</ID>
<Name>Quarter</Name>
<Type>Quarters</Type>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>- 1 </DataSize>
<Source xsi:type="ColumnBinding">
<TableID>dbo_Time</TableID>
<ColumnID>Quarter</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<NameColumn>
<DataType>WChar</DataType>
<DataSize> 7 </DataSize>
<Source xsi:type="ColumnBinding">
<TableID>dbo_Time</TableID>
<ColumnID>QuarterName</ColumnID>
</Source>
</NameColumn>
<AttributeRelationships>
<AttributeRelationship>
<AttributeID>Year</AttributeID>
<Name>Year</Name>
</AttributeRelationship>
</AttributeRelationships>
<OrderBy>Key</OrderBy>
<AttributeHierarchyVisible>false</AttributeHierarchyVisible>
</Attribute>
<Attribute>
<ID>Month</ID>
<Name>Month</Name>
<Type>Months</Type>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>- 1 </DataSize>
<Source xsi:type="ColumnBinding">
<TableID>dbo_Time</TableID>
<ColumnID>Month</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<NameColumn>
<DataType>WChar</DataType>
<DataSize> 30 </DataSize>
<Source xsi:type="ColumnBinding">
<TableID>dbo_Time</TableID>
<ColumnID>MonthName</ColumnID>
</Source>
</NameColumn>
<AttributeRelationships>
<AttributeRelationship>
<AttributeID>Quarter</AttributeID>
<Name>Quarter</Name>
</AttributeRelationship>
</AttributeRelationships>
<OrderBy>Key</OrderBy>
</Attribute>
<Attribute>
<ID>Date</ID>
<Name>Date</Name>
<Type>Days</Type>
<Usage>Key</Usage>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<Source xsi:type="ColumnBinding">
<TableID>dbo_Time</TableID>
<ColumnID>ID</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<NameColumn>
<DataType>WChar</DataType>
<DataSize> 10 </DataSize>
<Source xsi:type="ColumnBinding">
<TableID>dbo_Time</TableID>
<ColumnID>Date</ColumnID>
</Source>
</NameColumn>
<AttributeRelationships>
<AttributeRelationship>
<AttributeID>Month</AttributeID>
<RelationshipType>Rigid</RelationshipType>
<Name>Month</Name>
</AttributeRelationship>
</AttributeRelationships>
<OrderBy>Key</OrderBy>
<AttributeHierarchyVisible>false</AttributeHierarchyVisible>
</Attribute>
</Attributes>
<Hierarchies>
<Hierarchy>
<ID>Year - Quarter - Month - Date</ID>
<Name>Year - Quarter - Month - Date</Name>
<Levels>
<Level>
<ID>Year</ID>
<Name>Year</Name>
<SourceAttributeID>Year</SourceAttributeID>
</Level>
<Level>
<ID>Quarter</ID>
<Name>Quarter</Name>
<SourceAttributeID>Quarter</SourceAttributeID>
</Level>
<Level>
<ID>Month</ID>
<Name>Month</Name>
<SourceAttributeID>Month</SourceAttributeID>
</Level>
<Level>
<ID>Date</ID>
<Name>Date</Name>
<SourceAttributeID>Date</SourceAttributeID>
</Level>
</Levels>
</Hierarchy>
</Hierarchies>
<ProactiveCaching>
<SilenceInterval>-PT1S</SilenceInterval>
<Latency>-PT1S</Latency>
<SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
<ForceRebuildInterval>-PT1S</ForceRebuildInterval>
<Source xsi:type="ProactiveCachingInheritedBinding" />
</ProactiveCaching>
</Dimension>
</Dimensions>
<Cubes>
<Cube>
<ID>Tempdb</ID>
<Name>Sales</Name>
<Language> 1033 </Language>
<Collation>Cyrillic_General_CI_AS</Collation>
<Dimensions>
<Dimension>
<ID>Time</ID>
<Name>Time</Name>
<DimensionID>Time</DimensionID>
<Attributes>
<Attribute>
<AttributeID>Year</AttributeID>
</Attribute>
<Attribute>
<AttributeID>Quarter</AttributeID>
</Attribute>
<Attribute>
<AttributeID>Month</AttributeID>
</Attribute>
<Attribute>
<AttributeID>Date</AttributeID>
</Attribute>
</Attributes>
<Hierarchies>
<Hierarchy>
<HierarchyID>Year - Quarter - Month - Date</HierarchyID>
</Hierarchy>
</Hierarchies>
</Dimension>
<Dimension>
<ID>Products</ID>
<Name>Products</Name>
<DimensionID>Products</DimensionID>
<Attributes>
<Attribute>
<AttributeID>Products</AttributeID>
</Attribute>
<Attribute>
<AttributeID>Product Categories</AttributeID>
</Attribute>
</Attributes>
<Hierarchies>
<Hierarchy>
<HierarchyID>Product Categories - Products</HierarchyID>
</Hierarchy>
</Hierarchies>
</Dimension>
<Dimension>
<ID>Employees</ID>
<Name>Employees</Name>
<DimensionID>Employees</DimensionID>
<Attributes>
<Attribute>
<AttributeID>Employees</AttributeID>
</Attribute>
<Attribute>
<AttributeID>Departments</AttributeID>
</Attribute>
</Attributes>
<Hierarchies>
<Hierarchy>
<HierarchyID>Departments - Employees</HierarchyID>
</Hierarchy>
</Hierarchies>
</Dimension>
</Dimensions>
<MeasureGroups>
<MeasureGroup>
<ID>Sales</ID>
<Name>Sales</Name>
<Measures>
<Measure>
<ID>Fact</ID>
<Name>Fact</Name>
<DataType>Double</DataType>
<Source>
<DataType>Double</DataType>
<Source xsi:type="ColumnBinding">
<TableID>dbo_Sales</TableID>
<ColumnID>Fact</ColumnID>
</Source>
</Source>
</Measure>
</Measures>
<StorageMode>Molap</StorageMode>
<ProcessingMode>Regular</ProcessingMode>
<Dimensions>
<Dimension xsi:type="RegularMeasureGroupDimension">
<CubeDimensionID>Products</CubeDimensionID>
<Attributes>
<Attribute>
<AttributeID>Products</AttributeID>
<KeyColumns>
<KeyColumn>
<NullProcessing>UnknownMember</NullProcessing>
<DataType>Integer</DataType>
<Source xsi:type="ColumnBinding">
<TableID>dbo_Sales</TableID>
<ColumnID>ProductID</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<Type>Granularity</Type>
</Attribute>
<Attribute>
<AttributeID>Product Categories</AttributeID>
<KeyColumns>
<KeyColumn>
<NullProcessing>UnknownMember</NullProcessing>
<DataType>Integer</DataType>
<Source xsi:type="InheritedBinding" />
</KeyColumn>
</KeyColumns>
</Attribute>
</Attributes>
</Dimension>
<Dimension xsi:type="RegularMeasureGroupDimension">
<CubeDimensionID>Employees</CubeDimensionID>
<Attributes>
<Attribute>
<AttributeID>Employees</AttributeID>
<KeyColumns>
<KeyColumn>
<NullProcessing>UnknownMember</NullProcessing>
<DataType>Integer</DataType>
<Source xsi:type="ColumnBinding">
<TableID>dbo_Sales</TableID>
<ColumnID>EmployeeID</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<Type>Granularity</Type>
</Attribute>
<Attribute>
<AttributeID>Departments</AttributeID>
<KeyColumns>
<KeyColumn>
<NullProcessing>UnknownMember</NullProcessing>
<DataType>Integer</DataType>
<Source xsi:type="InheritedBinding" />
</KeyColumn>
</KeyColumns>
</Attribute>
</Attributes>
</Dimension>
<Dimension xsi:type="RegularMeasureGroupDimension">
<CubeDimensionID>Time</CubeDimensionID>
<Attributes>
<Attribute>
<AttributeID>Year</AttributeID>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>- 1 </DataSize>
<Source xsi:type="InheritedBinding" />
</KeyColumn>
</KeyColumns>
</Attribute>
<Attribute>
<AttributeID>Quarter</AttributeID>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>- 1 </DataSize>
<Source xsi:type="InheritedBinding" />
</KeyColumn>
</KeyColumns>
</Attribute>
<Attribute>
<AttributeID>Month</AttributeID>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<DataSize>- 1 </DataSize>
<Source xsi:type="InheritedBinding" />
</KeyColumn>
</KeyColumns>
</Attribute>
<Attribute>
<AttributeID>Date</AttributeID>
<KeyColumns>
<KeyColumn>
<DataType>Integer</DataType>
<Source xsi:type="ColumnBinding">
<TableID>dbo_Sales</TableID>
<ColumnID>TimeID</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<Type>Granularity</Type>
</Attribute>
</Attributes>
</Dimension>
</Dimensions>
<Partitions>
<Partition>
<ID>Sales</ID>
<Name>Sales</Name>
<Source xsi:type="DsvTableBinding">
<DataSourceViewID>Tempdb</DataSourceViewID>
<TableID>dbo_Sales</TableID>
</Source>
<StorageMode>Molap</StorageMode>
<ProcessingMode>Regular</ProcessingMode>
<ProactiveCaching>
<SilenceInterval>-PT1S</SilenceInterval>
<Latency>-PT1S</Latency>
<SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
<ForceRebuildInterval>-PT1S</ForceRebuildInterval>
<Source xsi:type="ProactiveCachingInheritedBinding" />
</ProactiveCaching>
</Partition>
</Partitions>
<ProactiveCaching>
<SilenceInterval>-PT1S</SilenceInterval>
<Latency>-PT1S</Latency>
<SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
<ForceRebuildInterval>-PT1S</ForceRebuildInterval>
<Source xsi:type="ProactiveCachingInheritedBinding" />
</ProactiveCaching>
</MeasureGroup>
</MeasureGroups>
<Source>
<DataSourceViewID>Tempdb</DataSourceViewID>
</Source>
<ProactiveCaching>
<SilenceInterval>-PT1S</SilenceInterval>
<Latency>-PT1S</Latency>
<SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
<ForceRebuildInterval>-PT1S</ForceRebuildInterval>
<Source xsi:type="ProactiveCachingInheritedBinding" />
</ProactiveCaching>
</Cube>
</Cubes>
<DataSources>
<DataSource xsi:type="RelationalDataSource">
<ID>Tempdb</ID>
<Name>Tempdb</Name>
<ConnectionString>Provider=SQLNCLI. 1 ;Data Source=(local);Integrated Security=SSPI;Initial Catalog=tempdb;Current Language=Russian</ConnectionString>
<ImpersonationInfo>
<ImpersonationMode>ImpersonateServiceAccount</ImpersonationMode>
</ImpersonationInfo>
<Timeout>PT0S</Timeout>
</DataSource>
</DataSources>
<DataSourceViews>
<DataSourceView>
<ID>Tempdb</ID>
<Name>Tempdb</Name>
<Annotations>
<Annotation>
<Name>http://schemas.microsoft.com/DataWarehouse/Designer/ 1 . 0 :SchemaRestriction</Name>
</Annotation>
<Annotation>
<Name>http://schemas.microsoft.com/DataWarehouse/Designer/ 1 . 0 :RetrieveRelationships</Name>
<Value>true</Value>
</Annotation>
</Annotations>
<DataSourceID>Tempdb</DataSourceID>
<Schema>
<xs:schema id="Tempdb" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:msprop="urn:schemas-microsoft-com:xml-msprop">
<xs:element name="Tempdb" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="dbo_Departments" msprop:FriendlyName="Departments" msprop:DbSchemaName="dbo" msprop:DbTableName="Departments" msprop:TableType="Table">
<xs:complexType>
<xs:sequence>
<xs:element name="ID" msdata:ReadOnly="true" msdata:AutoIncrement="true" msprop:FriendlyName="ID" msprop:DbColumnName="ID" type="xs:int" />
<xs:element name="Name" msprop:FriendlyName="Name" msprop:DbColumnName="Name" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="50" />
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="dbo_Employees" msprop:FriendlyName="Employees" msprop:DbSchemaName="dbo" msprop:DbTableName="Employees" msprop:TableType="Table">
<xs:complexType>
<xs:sequence>
<xs:element name="ID" msdata:ReadOnly="true" msdata:AutoIncrement="true" msprop:FriendlyName="ID" msprop:DbColumnName="ID" type="xs:int" />
<xs:element name="Name" msprop:FriendlyName="Name" msprop:DbColumnName="Name" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="50" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="DepartmentID" msprop:FriendlyName="DepartmentID" msprop:DbColumnName="DepartmentID" type="xs:int" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="dbo_ProductCategories" msprop:FriendlyName="ProductCategories" msprop:DbSchemaName="dbo" msprop:DbTableName="ProductCategories" msprop:TableType="Table">
<xs:complexType>
<xs:sequence>
<xs:element name="ID" msdata:ReadOnly="true" msdata:AutoIncrement="true" msprop:FriendlyName="ID" msprop:DbColumnName="ID" type="xs:int" />
<xs:element name="Name" msprop:FriendlyName="Name" msprop:DbColumnName="Name" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="50" />
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="dbo_Products" msprop:FriendlyName="Products" msprop:DbSchemaName="dbo" msprop:DbTableName="Products" msprop:TableType="Table">
<xs:complexType>
<xs:sequence>
<xs:element name="ID" msdata:ReadOnly="true" msdata:AutoIncrement="true" msprop:FriendlyName="ID" msprop:DbColumnName="ID" type="xs:int" />
<xs:element name="Name" msprop:FriendlyName="Name" msprop:DbColumnName="Name" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="50" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="CategoryID" msprop:FriendlyName="CategoryID" msprop:DbColumnName="CategoryID" type="xs:int" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="dbo_Sales" msprop:FriendlyName="Sales" msprop:DbSchemaName="dbo" msprop:DbTableName="Sales" msprop:TableType="Table">
<xs:complexType>
<xs:sequence>
<xs:element name="TimeID" msprop:FriendlyName="TimeID" msprop:DbColumnName="TimeID" type="xs:int" minOccurs="0" />
<xs:element name="ProductID" msprop:FriendlyName="ProductID" msprop:DbColumnName="ProductID" type="xs:int" minOccurs="0" />
<xs:element name="EmployeeID" msprop:FriendlyName="EmployeeID" msprop:DbColumnName="EmployeeID" type="xs:int" minOccurs="0" />
<xs:element name="Fact" msprop:FriendlyName="Fact" msprop:DbColumnName="Fact" type="xs:decimal" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="dbo_Time" msprop:FriendlyName="Time" msprop:DbSchemaName="dbo" msprop:DbTableName="Time" msprop:TableType="Table">
<xs:complexType>
<xs:sequence>
<xs:element name="ID" msdata:ReadOnly="true" msdata:AutoIncrement="true" msprop:FriendlyName="ID" msprop:DbColumnName="ID" type="xs:int" />
<xs:element name="Name" msprop:FriendlyName="Name" msprop:DbColumnName="Name" type="xs:dateTime" minOccurs="0" />
<xs:element name="Year" msdata:ReadOnly="true" msprop:DbColumnName="Year" msprop:ComputedColumnExpression="year(Name)" msprop:Description="" msprop:IsLogical="True" type="xs:int" minOccurs="0" />
<xs:element name="Quarter" msdata:ReadOnly="true" msprop:DbColumnName="Quarter" msprop:ComputedColumnExpression="datepart(q, Name)" msprop:Description="" msprop:IsLogical="True" type="xs:int" minOccurs="0" />
<xs:element name="Month" msdata:ReadOnly="true" msprop:DbColumnName="Month" msprop:ComputedColumnExpression="month(Name)" msprop:Description="" msprop:IsLogical="True" type="xs:int" minOccurs="0" />
<xs:element name="Date" msdata:ReadOnly="true" msprop:DbColumnName="Date" msprop:ComputedColumnExpression="convert(char(10), Name, 103)" msprop:Description="" msprop:IsLogical="True" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="10" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="QuarterName" msdata:ReadOnly="true" msprop:DbColumnName="QuarterName" msprop:ComputedColumnExpression="cast(DatePart(q, Name) as char(1)) + '-й кв.'" msprop:Description="" msprop:IsLogical="True" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="7" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="MonthName" msdata:ReadOnly="true" msprop:DbColumnName="MonthName" msprop:ComputedColumnExpression="DateName(m, Name)" msprop:Description="" msprop:IsLogical="True" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="30" />
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:unique name="Constraint1" msprop:IsLogical="True" msdata:PrimaryKey="true">
<xs:selector xpath=".//dbo_Departments" />
<xs:field xpath="ID" />
</xs:unique>
<xs:unique name="dbo_Employees_Constraint1" msdata:ConstraintName="Constraint1" msprop:IsLogical="True" msdata:PrimaryKey="true">
<xs:selector xpath=".//dbo_Employees" />
<xs:field xpath="ID" />
</xs:unique>
<xs:unique name="dbo_ProductCategories_Constraint1" msdata:ConstraintName="Constraint1" msprop:IsLogical="True" msdata:PrimaryKey="true">
<xs:selector xpath=".//dbo_ProductCategories" />
<xs:field xpath="ID" />
</xs:unique>
<xs:unique name="dbo_Products_Constraint1" msdata:ConstraintName="Constraint1" msprop:IsLogical="True" msdata:PrimaryKey="true">
<xs:selector xpath=".//dbo_Products" />
<xs:field xpath="ID" />
</xs:unique>
<xs:unique name="dbo_Time_Constraint1" msdata:ConstraintName="Constraint1" msprop:IsLogical="True" msdata:PrimaryKey="true">
<xs:selector xpath=".//dbo_Time" />
<xs:field xpath="ID" />
</xs:unique>
</xs:element>
<xs:annotation>
<xs:appinfo>
<msdata:Relationship name="Employees-Departments" msdata:parent="dbo_Departments" msdata:child="dbo_Employees" msdata:parentkey="ID" msdata:childkey="DepartmentID" msprop:Description="" msprop:IsLogical="True" />
<msdata:Relationship name="Products-ProductCategories" msdata:parent="dbo_ProductCategories" msdata:child="dbo_Products" msdata:parentkey="ID" msdata:childkey="CategoryID" msprop:Description="" msprop:IsLogical="True" />
<msdata:Relationship name="Sales-Time" msdata:parent="dbo_Time" msdata:child="dbo_Sales" msdata:parentkey="ID" msdata:childkey="TimeID" msprop:Description="" msprop:IsLogical="True" />
<msdata:Relationship name="Sales-Products" msdata:parent="dbo_Products" msdata:child="dbo_Sales" msdata:parentkey="ID" msdata:childkey="ProductID" msprop:Description="" msprop:IsLogical="True" />
<msdata:Relationship name="Sales-Employees" msdata:parent="dbo_Employees" msdata:child="dbo_Sales" msdata:parentkey="ID" msdata:childkey="EmployeeID" msprop:Description="" msprop:IsLogical="True" />
</xs:appinfo>
</xs:annotation>
</xs:schema>
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1" />
</Schema>
</DataSourceView>
</DataSourceViews>
</Database>
</ObjectDefinition>
</Create>
-выполняете
-процессите базу
-открываете Excel 2003 SP1, устанавливаете соединение с кубом:
1.
2.
3.
4.
QueryType=OLEDB
Version= 1
CommandType=Cube
Connection=Provider=MSOLAP. 3 ;Cache Authentication=False;User ID="";Initial Catalog=DbForOfflineCubeTest;Data Source=<skipped>;Impersonation Level=Impersonate;Location=<skipped>;Mode=ReadWrite;Protection Level=Pkt Privacy;Auto Synch Period= 20000 ;Default Isolation Mode= 0 ;Default MDX Visual Mode= 0 ;MDX Compatibility= 0 ;MDX Unique Name Style= 0 ;Non Empty Threshold= 0 ;SQLQueryMode=Calculated;Safety Options= 1 ;Secured Cell Value= 0 ;SQL Compatibility= 0 ;Compression Level= 0 ;Real Time Olap=False;Packet Size= 4096
CommandText=Sales
-открываете серверный куб в Pivot Table, строите по нему оффлайновый:
